IF, AND, What is wrong with this formula?

[DaveMoore]

The cells in the formula below contain formula themselves; could this
be why it is not working?
"False" is being returned whether or not the values of each of these
cells are zero.
I have not used =IF(SUM(AR187:BC187)=0,"True","False") because there
may be a compensating negative and positive in the range.

'=IF(AND(AR187,AS187,AT187,AU187,AV187,AW187,AX187,AY187,AZ187,BA187,BB187,BC187)=0,"True","False")

many thanks to anyone who can help.
Dave Moore

 

[Mike H]

Maybe

=IF(COUNTIF(AR187:BC187,"=0")=12,"True","False")

Mike

 

[Billy Liddel]

Dave
Each reference has to evaluate to 0

eg. =IF(AND(B7=0,C7=0,D7=0),"True","False")

Hope this helps
Peter

 

[Roger Govier]

Hi Dave
Probably due to small rounding errors
try
=IF(ROUND(SUM(AR187:BC187),0)=0,"True","False")

 

[Ron Rosenfeld]

The cells in the formula below contain formula themselves; could this
be why it is not working?
"False" is being returned whether or not the values of each of these
cells are zero.
I have not used =IF(SUM(AR187:BC187)=0,"True","False") because there
may be a compensating negative and positive in the range.

'=IF(AND(AR187,AS187,AT187,AU187,AV187,AW187,AX187,AY187,AZ187,BA187,BB187,BC187)=0,"True","False")

many thanks to anyone who can help.
Dave Moore

The problem is that you are not understanding the syntax for the AND worksheet
function.

If you were to use the AND function to test to see if all the cells have a
value of zero, you need to write:

=IF(AND(AR187=0,AS187=0,AT187=0,...),"True","False")

Another way of writing this, again using the AND function, would be

=AND(AR187:BC187=0)

Entered as an ARRAY formula, it will return TRUE if all are zero (or blank),
and false if any are not. (It returns the TRUE or FALSE as Boolean values, and
not as text values as will your IF statement. If you require TEXT values, then
just wrap it in an IF statement: =IF(AND(AR187:BC187=0),"True","False") --
also ARRAY-ENTERED).

To enter an ARRAY formula, hold down <ctrl><shift> while hitting <enter>.

If you also want to return FALSE if any of the entries are blank, then:

=AND(AR187:BC187=0,ISNUMBER(AR187:BC187)) also entered as an array formula.
--ron