why can't i sum or count a "value based on if statement"

[_Bigred]

(Excel 2000)



I have a column that uses a If Statement of:



=IF(H8<5,"0",IF(H8<10,"50",IF(H8<15,"100",IF(H8<20,"150",IF(H8<25,"200",IF(H
8<40,"250"))))))





It works great, but how can I do sum, count, average etc.. on this? When I
try I get an error.



When trying to average it returns #DIV/O!



Any help would be greatly appreciated,
_Bigred

 

[Ron Rosenfeld]

(Excel 2000)



I have a column that uses a If Statement of:



=IF(H8<5,"0",IF(H8<10,"50",IF(H8<15,"100",IF(H8<20,"150",IF(H8<25,"200",IF(H
8<40,"250"))))))



It works great, but how can I do sum, count, average etc.. on this? When I
try I get an error.

Since the formula only returns a single value, your statement needs some
amplification. Perhaps you mean to use the returned value in another formula
that includes AVERAGE, etc?

When trying to average it returns #DIV/O!

That's because you are returning text strings, and not numbers. Different
operators may coerce a text string to a number and so may give results
different from what you expect, if you expect the result to always be treated
as a number.

Since all of your return values are enclosed in quotation marks, they are
returned as text strings.

If you need to have these values returned as text, you will have to convert
them explicitly to values in your subsequent formulas. You can usually do this
by either using the VALUE worksheet function, or by prepending a double unary
(--).

On the other hand, if you don't need them as text, it might be easier to just
remove the double quotes.



--ron

 

[Gord Dibben]

Bigred

By using the quotation marks you are creating text values.

Copy an empty cell and select your range of formulas then Paste
Special>Add>OK>Esc. This will change the data to numerics

Or edit the formulas to return numerics to start with.

Edit>Replace

what: "
with: nothing

Gord Dibben XL2002

 

[_Bigred]

Thanks Guys I removed the double quotes, and it worked great.

Thanks,
_Bigred