If Statements and Multiple Criteria

Bored79

New Member
Joined
Jul 9, 2007
Messages
7
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.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,258
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.

Code:
=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.

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

Forum statistics

Threads
1,181,053
Messages
5,927,848
Members
436,572
Latest member
khalid hussain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top