How to recognise negative numbers in a formula?

**grrr34** · 10-08-2013, 08:00 PM

I wish to calculate the difference between two test scores and then assign a letter value based on the difference in the scores. The formula I have tried to use is:

=IF(OR($P4="", $Q4="", $R4=""),"",IF(R4>=4,"W",IF(R4>=0,"X",IF(R4<0,"Y",IF(R4<=-5,"Z",)))))

P is 1st test result. Q is 2nd test result. R is the difference in test scores (the result is sometimes negative).

'W', 'X' and 'Y' all display when appropriate, but I can't get the formula to display 'Z' for any test result. It should do so when the result is negative 5 or 'less'. Can excel recognise negative numbers in a formula of this type? If not, what functions can I use to display the letters appropriately?

Thanks for your help.

**sktneer** · 10-08-2013, 08:37 PM

Try this.

=IF(OR($P4="", $Q4="", $R4=""),"",IF(R4>=4,"W",IF(R4>=0,"X",IF(R4>-5,"Y","Z"))))

**benishiryo** · 10-08-2013, 08:49 PM

welcome to the forum grrr34. that's because the IF formula test for each condition from left to right & stops when the condition is TRUE. so anything less than 0 is already ends here before you test for "Z":
IF(R4<0,"Y"

you should keep your conditions consistently going up or down. since you're testing for >=, keep it that way.
=IF(OR($P4="", $Q4="", $R4=""),"",IF(R4>=4,"W",IF(R4>=0,"X",IF(R4>-5,"Y",IF(R4<=-5,"Z",)))))

and since there will be no other results possible when it's not "", >=4, >=0, >-5, it can only be <=-5. there is no need to test for that:
=IF(OR($P4="", $Q4="", $R4=""),"",IF(R4>=4,"W",IF(R4>=0,"X",IF(R4>-5,"Y","Z"))))

you can also reduce it further by counting if P4:R4 is 3 counts (meaning 3 numbers):
=IF(COUNT(P4:R4)<>3,"",IF(R4>=4,"W",IF(R4>=0,"X",IF(R4>-5,"Y","Z"))))

**grrr34** · 10-09-2013, 07:03 PM

Brill! Thank you for the extra info and the kind welcome.

I have another problem this evening:

I would like to paste multiple cells with their formulas from one workbook to another. The formulas refer to a different sheet within the workbook, and when I paste special - formula, the actual sheet name from the first workbook is pasted also. I don't want this to happen as the I want it to refer to a sheet with the same name in the second workbook (and not back to the first workbook). I hope this is clear. I can provide extra info if not.

Is there another paste option which will paste the formulas, but not the reference to the sheet in the first workbook?

**FDibbins** · 10-09-2013, 11:18 PM

To add to Bens comment of...

you can also reduce it further by counting if P4:R4 is 3 counts (meaning 3 numbers):
=IF(COUNT(P4:R4)<>3,"",IF(R4>=4,"W",IF(R4>=0,"X",IF(R4>-5,"Y","Z"))))

If R is the difference between P and Q, , I would think you only need to test R, so you can reduce it further to...
=IF(R4="","",IF(R4>=4,"W",IF(R4>=0,"X",IF(R4>-5,"Y","Z"))))

Also, if your post #4 is a different question, please start another thread with that question

Thanks