#DIV/0! ERROR is taunting me....

[Michele]

Here is my formula "=(STDEV(B71:F71))^2" and here is what is displayed in cell I71 "#DIV/0! ERROR"

would like to choke this spreadsheet right now !!! I tried a conditional formating formula but when I really do end up with 0 for a sum it won't display it so I am at my wits end. Any ideas ?

Thanks, M

 

[VoG]

If you want to suppress the error try

=IF(ISERROR(STDEV(B71:F71)^2),"",STDEV(B71:F71)^2)

 

[Jonmo1]

Are you sure the values in B71:F71 are actually numbers?
Not "Numbers Stored As Text" ?

Use
=ISNUMBER(B71) filled right to test


Also, I think STDEV requires at least 2 numbers in it's arguments...
So perhaps test for at least 2

=IF(COUNT(B71:F71)>=2,STDEV(B71:F71)^2,"")


Hope that helps.

 

[Michele]

THAT WORKS Peter !!! but I have many other formulas I need to do this to and they are different, like averages, stand division or this complex formulae here "=2*SQRT((AD64/2)^2+(AD61/SQRT(3))^2)+AD62/SQRT(12)"

I am trying to figure out how to apply what you showed me to this last formula and I am just too stoopid !!! lol

any who, yup !

 

[Michele]

Good point !!! I only tested that formula on 2 cells, maybe I need to see about this. thank you

 

[Michele]

i was using 5 cells and it seems to change values for each cell entered, is that what you meant ?

 

[Aladin Akyurek]

What do you get with:

=COUNT(B71:F71)

=COUNTA(B71:F71)

 

[Scott Huish]

=2*SQRT((AD64/2)^2+(AD61/SQRT(3))^2)+AD62/SQRT(12)

That shouldn't ever result in a #DIV/0! error as you never divide by 0.

On a simple scale though, the basic formula is:

=IF(B1,A1/B1,"")

 

[Michele]

Yippeeee , thanks guys !!! That did work ! I had the cell properties set for the wrong format and a lingering color change code.

thanks