Hi,

Using excel if functions can you set it up to give the following all in one formula?>=7 then give value of 0

<7 and >=6 then give value of 1

<6 and >=5 then give value of 2

<5 then give value of 3Thanks

Iain

message edited by IRMC

✔ Best Answer

There is a difference between "correct" and writing a formula that will return the values you want. While your formula will return the values you want, it is much too complicated for the task at hand, therefore I would not call it "correct".

First, you don't need the AND's. A simple Nested IF will return the same results.

Second, your formula will never return

""because the condition required for that will never occur. You have 4 conditions, >=7, >=6, >=5, and <5. That pretty much covers every number in the known universe. Since you have specified a return value for each of those conditions (0,1,2,3) there is no condition under which the formula will return "".As far as I can tell, this simple Nested IF will return the values that you want.

=IF(N3>=7,0,IF(N3>=6,1,IF(N3>=5,2,3)))

You will note that the formula never actually tests for N3<5. It doesn't have to. If all the other tests are FALSE, then the value in N3

mustbe <5. In other words, if the value in N3 is not >=7 and not >=6 and not >=5, then it can't be anything else than <5.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

You can cascade IFs, as in IF(condition1, trueaction1, IF(condition2, trueaction2, falseaction2))

It should be fairly easy for you to extend that to get the desired result.

What you are looking for is known as a Nested IF. If you DAGS

Excel Nested IF, you'll find lots of examples and tutorials.Building a Nested IF on your own is a better way to learn about them then if we simply supply a solution. If you have any trouble, come on back.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hi DerbyDad03, Ive tried a few things and can't get it to work. it only seems to go half way along the formula and stop working. e.g. =IF(N3>=7,0,IF(N3<7,1,IF(N3<5,3,2)))

No idea how to fix it and I'm running out of time!

Can you help?

Obviously if N3 < 5 then it is also true that N3 < 7, so that part of the formula is never going to be reached. Think about the order in which you apply the tests. Why the time constraint? This begins to sound like a homework question; I think you have been given more than enough help to solve it.

I think this is correct? =IF(N3>=7,0,IF(AND(N3<7,N3>=6),1,IF(AND(N3<6,N3>=5),2,IF(N3<5,3,""))))

There is a difference between "correct" and writing a formula that will return the values you want. While your formula will return the values you want, it is much too complicated for the task at hand, therefore I would not call it "correct".

First, you don't need the AND's. A simple Nested IF will return the same results.

Second, your formula will never return

""because the condition required for that will never occur. You have 4 conditions, >=7, >=6, >=5, and <5. That pretty much covers every number in the known universe. Since you have specified a return value for each of those conditions (0,1,2,3) there is no condition under which the formula will return "".As far as I can tell, this simple Nested IF will return the values that you want.

=IF(N3>=7,0,IF(N3>=6,1,IF(N3>=5,2,3)))

You will note that the formula never actually tests for N3<5. It doesn't have to. If all the other tests are FALSE, then the value in N3

mustbe <5. In other words, if the value in N3 is not >=7 and not >=6 and not >=5, then it can't be anything else than <5.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hi Derby, Thanks, im not very good with formulas but gave it a try, thanks for the correction and I'll have a little read and hopefully can learn something from it.

Thanks,

Ask Your Question

Weekly Poll