If Statements and Multiple Criteria

[Bored79]

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.

 

[Gerald Higgins]

Problem No 1 - are you saying you simply want to ignore the negative sign, so that it would treat -10 as being greater than 8 ?

If so, use ABS to capture the absolute value.

=IF(ABS(N12)>ABS(M12),"is larger for girls","is larger for boys")

Problem 2 - slightly complicated - 5% of what ? N12, or M12 ? If you're testing for similarity, it might not matter too much.
Let's assume you want them to be within 5% of M12.

=IF(ABS(N12)>(ABS(M12)*1.05),"is larger for girls",IF(ABS(N12)<(ABS(M12)*0.95),"is larger for boys","are similar"))