# 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 3ThanksIainmessage edited by IRMC

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

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.

#1
November 20, 2016 at 00:39:20
 You can cascade IFs, as inIF(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.

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