If Statement and Multiple Criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using a formula to bring back some text about some data. I am using the
following formula:

=IF(N12>M12,"is larger for girls","is larger for boys"), so that if the data
in cell N12 is greater than the data in cell M12 then it says "is larger for
girls" and vice versa.

This works fine, apart from if negative numbers are in the cells then it
brings back the value closest to zero - rather than the largest negative
number.

Does anyone know how to get round this.

In addition - I would ideally like to alter the formula so that it says that
if the two values are within a certain range of each other (say 5%) that it
says that the values are similar, and then if it is over a 5% difference then
the statments that I had in previously are returned.

Can anyone recommend a way to do this as well.

Thanks very much for your help.
 
I am using a formula to bring back some text about some data. I am using the
following formula:

=IF(N12>M12,"is larger for girls","is larger for boys"), so that if the data
in cell N12 is greater than the data in cell M12 then it says "is larger for
girls" and vice versa.

This works fine, apart from if negative numbers are in the cells then it
brings back the value closest to zero - rather than the largest negative
number.
Click to expand...

Your formula does not "bring back" ANY numeric value; it only returns a phrase.

If you want to test for the value of the greatest magnitude, regardless of
sign, then use the ABS function:

e.g. =if(abs(n12)>abs(m12),...)
In addition - I would ideally like to alter the formula so that it says that
if the two values are within a certain range of each other (say 5%) that it
says that the values are similar, and then if it is over a 5% difference then
the statments that I had in previously are returned.

Can anyone recommend a way to do this as well.
Click to expand...

In general, something like:

=if(ABS((M12-N12)/M12)<=5%,...)

However, depending on what you want to do, you may have to decide which value
is the "base".

For example:

M12: 95
N12: 100

100 is 95 + 5.26%
95 is 100 - 5%

Finally, all of the above assumes that both values are of the same sign (either
positive or negative). If they are not, the comparisons may not be meaningful.
--ron
 
Maybe you could change the formulas that return the values from the other
location:

=max(0,yourformulahere)

You'll never see a negative number.

And you can change your formula:

=if((n12/m12)>1.05,"....

But you'd probably want to check to see if m12 = 0--what happens then?

=if(m12=0,"whateveryouwant",if((n12/m12)>1.05,"....
 
Back
Top