Formula for setting a cell dependant on values in other cells
Posted by Paul Lowe on March 07, 2001 8:38 AM
I need to set the contents of a cell, eg Z1, to 'Y' or 'N', dependant upon the value ('Y' or 'N') of 16 other cells in the worksheet. Is there an easy way to do this?
Eg: if all 16 cells are set to 'Y', cell Z1 should be set to 'Y'; if one or more cells are set to 'N', Z1 should also be set to 'N'.
Thanks.
Posted by Mark W. on March 07, 2001 8:50 AM
{=IF(AND(A1:A16="Y")),"Y","N")}
Posted by Paul Lowe on March 07, 2001 9:05 AM
Thanks. Unfortunately, my cells are in different places. For example, I have 'Y's and 'N's in cells like A1, B5, F42, etc. Can I use the same or similar formula?
Cheers.
Posted by Mark W. on March 07, 2001 11:58 AM
=IF(AND(A1="Y",B5="Y",F42="Y"),"Y","N")
or
=IF(A1&B5&F42="YYY","Y","N")
or
=IF(A1&B5&F42=REPT("Y",3),"Y","N")
Posted by Mark W. on March 07, 2001 1:22 PM
...By the way...
You really ought to get out of the habit of using
{"Y","N"} as boolean flags and use {1,0} instead.
If you need to display Y/N just use a number
format such as [=1]"Y";[=0]"N" . You can then
use much simpler formulations such as...
=AND(A1,B5,F42)+0
...instead of...
=IF(AND(A1="Y",B5="Y",F42="Y"),"Y","N")
Posted by Paul Lowe on March 09, 2001 1:03 AM
Re: ...By the way...
That works a treat - many thanks for your help!
