If Function Surpress False (v97)

Topic

New Reply

Using the If function with no instructions for the false value, returns the word false if the true condition is not met. Generally, I would put – Value if False “” – to produce a blank, but this cell is a precedent for a Std. Dev. calculation and when it returns a blank, it causes the dependant cell to return an error. If you use 0 for the Value if false the Std Deviation calculation is inaccurate. The user would prefer NOT to have the word “false” to show … is there another way to surpress it? Thanks for the help.

Chance

Reply | Quote

Replies

Chance, As the attached worksheet shows, STDEV is not affected by “”. There must be some other problem. Can you give us more details? –Sam

Reply | Quote

Using “” for the False condition should do what you want, as long as you don’t put a space between those quotes.

Reply | Quote

All text is ignored by the STDEV function, including True and False boolean values.

Reply | Quote

I am not familiar with the STDEV function, but the original poster said he was getting an error from whatever function/formula he was using.

Reply | Quote

You could apply a custom number format, like:

0.00;-0.00;0.00;""

This would make negative, positive and zero values show up with two decimals, and text to not appear at all (which is the case with TRUE and FALSE).

Reply | Quote

Legare, that could be it: STDEV & STDEVP will give correct results because they skip text and logicals, but STDEVA and STDEVPA would give different (hesitate to say wrong) results because they include text and logicals. –Sam

Reply | Quote

Thank you Jan, Jim, Lagare and Sam … I was mistaken on the formulas as it isn’t my worksheet but rather a clients. I needed to get ahold of the worksheet …..Here are the two formulas that are involved:

=IF(COUNT(B153:I302),STDEV(B153:I302)) if this formula contains the “” it produces an error in the formula shown below

=IF(COUNT(D1:D3),SQRT((D1^2+D2^2+D3^2)/COUNT(D1:D3)),””)

However, returning the false in the first formula produces an accurate calculation in the second. The user doesn’t want the “false” to appear in the first cell, if possible.

I truely value the input from you all and I apoligize for being unclear in my first post.

Reply | Quote

Maybe he should use a conditional format on the first formula, making the font colour white when the cell’s value equals FALSE?

Reply | Quote

Jan … thanks … I should have thought of that … yes, that works for his purposes. Thank you so much for rattling this sometimes fuzzy brain. I guess I was trying to make it more complicated than necessary

Reply | Quote