IF formula - evaluation shows it should work but...

[Guest]

My formula is:

=IF(B2=1,"",(v2+z2+ad2+af2))

(B2 is either 0 or 1)

However, this gives a blank result for every cell, even when B#=0. When I
evaluate the formula through formula auditing the evaluation shows the
formula should work. Am stumped!

I have tried to re work the formula in several ways, if turned around
(B#=0,(#+#+#), "") it inserts the summed value in every cell and none are
blank even if (for example) B43=0

Hope you can help

Juliet

 

[Barb Reinhardt]

Is B2 formatted as TEXT? It shouldn't be.

 

[Guest]

Thanks Barb,
the worksheet is in general numerical format, can I assume then that the
formula entered is numerical too and not text?
juliet

 

[Pete]

You need to check that B2 is not formatted as Text - select B2 and
Format | Cells | Number (tab) - what format type is displayed? If it
Text then click General and OK. Double-click B2 for the change to take
place.

You might also check that B2 contains the number 1 and not the "text"
value '1.

Hope this helps.

Pete

 

[Roger Govier]

Hi Julie

If the formula evaluation tool returns the correct result, then it
sounds as though calculation may be set to manual.
Try pressing F9 and see what happens.
If required Tools>Options>Calculation>Automatic

 

[Roger H.]

Hello Juliet...
Could you please state what occupies those cells : (v2+z2+ad2+af2) ? I see
nothing wrong with the syntax of your IF formula, so I'm suspecting the
problem lies in those four cells. Thank you... Roger H.

 

[Sandy Mann]

Another possibility to add to the previous answers:

Is B1 a calculated result? it may be displaying 1 but be some value
slightly different - even off by 0.00000000001 would cause the test B2=1 to
fail.

Try just

=IF(B2,"",(v2+z2+ad2+af2))

--
HTH

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk