Solved Hi, If you have 4 things you want to look at what do you do

November 19, 2016 at 21:37:40
Specs: Windows 7
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 3

Thanks

Iain

message edited by IRMC


See More: Hi, If you have 4 things you want to look at what do you do

Report •

✔ Best Answer
November 22, 2016 at 06:05:16
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 must be <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.



#1
November 20, 2016 at 00:39:20
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.


Report •

#2
November 20, 2016 at 06:07:56
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.


Report •

#3
November 20, 2016 at 23:13:58
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?


Report •

Related Solutions

#4
November 21, 2016 at 01:43:40
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.


Report •

#5
November 22, 2016 at 00:11:21
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,""))))


Report •

#6
November 22, 2016 at 06:05:16
✔ 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 must be <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.


Report •

#7
November 22, 2016 at 06:13:07
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,


Report •

Ask Question