if fUNCTION - returning false? what is wrong with my criteria

Hi guys

excel shows false when I have the number 150 input. see the formule:

=IF(J11>150,"2",IF(J11=150,"1",IF(J11<=149,"0")))


For a cell where I have 150, excel doesn't show '1' it says; FALSE

Please advise whyt?

thank you!!

I've used your formula and for 150 I get a 1, but also, remove the quotes from around the numbers unless you are trying to return text.

Data Range

	J	K
9	1	=IF(J11>150,2,IF(J11=150,1,IF(J11<=149,0)))
10	1	=IF(J11>150,"2",IF(J11=150,"1",IF(J11<=149,"0")))
11	150

Notice J9 is right aligned (a number) and J10 is left aligned (text)

Hi,

I suspect that this is a rounding issue. Your formula does not have a contingency for J11 containing a value between 149 and 150. Therefore if the calculated value of J11 is 149.999999999 it will return FALSE.

Probably because the number is actually something between 149 and 150 that isn't catered for in your nested IFs - I'll bet that the number has decimals if you increase the decimal places.

It's probably rounding. 149.999999999999 will show as FALSE because it doesn't match any of your conditions. If you use this instead, you will probably get a 0:

Formula:

Please Login or Register  to view this content.

Edit: I really need to type faster - others keep beating me to it...!

Hi all

thank you for help.

It is J2 i have a problem with. I maybe think it is related to column I2 having a division formula

Please see attached!

You're right. I3, for example, is actually 149.577940348903, so is less than 150.
Two possible solutions:
in I2:

Formula:

Please Login or Register  to view this content.

or

in J2:

Formula:

Please Login or Register  to view this content.

Which you use depends on whether you might need the exact figures for 'Work per hour' for anything else.

You could use ROUNDUP or ROUNDDOWN instead of ROUND if you want to.

Again, as has been pointed out, it has to do with the decimal places.

How do you have a problem with J2? I2 = 251.23 which is greater than 150 so 2.

Originally Posted by Aardigspook

You're right. I3, for example, is actually 149.577940348903, so is less than 150.
Two possible solutions:
in I2:

Formula:

Please Login or Register  to view this content.

or

in J2:

Formula:

Please Login or Register  to view this content.

Which you use depends on whether you might need the exact figures for 'Work per hour' for anything else.

You could use ROUNDUP or ROUNDDOWN instead of ROUND if you want to.

thank you. the 2nd solution worked. rep added for everyone's help!

Thanks for the rep! Don't forget to add it to your notes so you remember next time it happens.

Originally Posted by AliGW

Thanks for the rep! Don't forget to add it to your notes so you remember next time it happens.

I will!

thanks Ali!

I am adding another question to the thread (sorry I am not great with IF functions).

Hi guys

Think I am overlapping my criteria in an IF function which is not giving me right results.

Please help:

=IF(P2>99,"2",IF(P2<=98,"1",IF(P2<=96,"0")))

I want to define if someone gets a score of 98 or below, their grade is 1.

If they get 96 or below they get 0. But I am not seeing 0 in my results?

pls advise

many thanks!

The IF function works in the order you write it. This will check for <=96 before <=98:

Formula:

Please Login or Register  to view this content.

You should also consider what you want for a score of 99, as exactly 99 isn't in your formula. Maybe make the first condition >=?

Formula:

Please Login or Register  to view this content.

And don't forget about potential rounding errors as previously in the thread .

ps thanks for the rep earlier.

thank you!

This is the same issue as before.

Are you sure that you UNDERSTAND the solutions given? If not, you must ask, or you'll keep making these mistakes.

@Ali - to be fair, the second problem was the logical order of the nested IF statements, so not the same as the first question.

@rayted - thanks for the rep, again, and for marking the thread as Solved.
You should maybe consider having a look at the Excel help files on functions which you want to use, like IF - they are generally quite good and will help you understand the logic behind each individual function. Sometimes just getting a solution given to you here will not help you understand why the solution works. As Ali has noted, you (like all of us) will only learn how to use them effectively if you understand them.
(end of lecture, sorry!)