adding information to nested if formula not working

[rach1345]

Hi,

Can anyone help me please?  I have the following formula IF(B2=3001,"N/A",IF(C2="IN","GEXPBOOK",IF(E2="GDG","GDG",IF(D2=1,"G1LINE",IF(AND(A2>0.01,A2<=53000),"G1CARTON",IF(AND(A2>53000,A2<1000000),"G1>CARTON",IF(AND(A2=1000000),"G1PALLET",IF(AND(A2>=1000000),"G>1PALLET","0"))))))))

I would like to add to some of this formula.  The G1>carton formula to also include if it is less than 90kg (f column), the G1PALLET to include if its equal to 400kg (f column)  and G>1PALLET to also include if it is more than 401kg.

these are the rules 

53000 volume = G1CARTON

more than 53000 in volume but less than 1 MILLION IN VOLUME but also to include if is less than 90kg G1>CARTON

if the volume is equal to 1000000 but also to include if its equal to 400kg =G1PALLET

if the volume is more than a million but also to include if its more than 400kg =G>1PALLET

[#2]

@rach1345 

Rather than nesting IF functions so deep, which can get very confusing, you'd do well to learn the IFS function, which can be much simpler. You just need to make sure that the conditions are listed in an order such that the formula can stop once it encounters a condition that is met (i.e., it will not evaluate all the conditions and then accept the most relevant; it stops as soon as it meets a condition)

[#3]

hi i tried using the ifs formula but it didn't return the correct fields such as G1PALLET

[#4]

That's not because the IFS function doesn't work. You need to define the conditions correctly (i.e., clearly and in keeping with the actual intentions) and in the right sequence. It's very easy to inadvertently be less clear /precise/accurate than intended.

Let me try again to see if I can adapt your nested IF.

[#5]

@rach1345 

I'm trying to sift through your instructions but find them confusing. Could you clarify. What your formula contains is not the same as your written "Formula rules" in several instances. 

For example, the formula reads

 IF(AND(A5>0.01,A5<=53000),"G1CARTON",IF(AND(A5>53000,A5<1000000),"G1>CARTON"

but the rules  53000 volume = G1CARTON 

That's just an example. Not a comprehensive list.

I think a good part of the problem here is that kind of inconsistency, and you can't expect outsiders to sort through those. So if you could carefully spell out the conditions for each type of outcome, that would be helpful.

And, frankly, I think if you were to do that, you'd find the IFS function would work.

[#6]

Hi mathetes,
I'm sorry the formula is very confusing which is why I am struggling to return the correct value. I will leave the easy bits out gexport,gdg etc and just concentrate on the difficult ones.
1. 53000 volume = G1CARTON
2. more than 53000 volume but less than 1000000 in volume and also less than 90kg weight G>1CARTON (if its more than 90kg it needs to go in to G1PALLET)
3. If more than1000000 volume and if its equal to 400kg weight = G1PALLET
4. If the volume is more than a million and more than 400kg G1>PALLET
Its very complex i'm not even sure if excel can do this formula
thanks and appreciate the help

[#7]

@rach1345 

Its very complex i'm not even sure if excel can do this formula

Excel can definitely accommodate the complexity. The complexity just needs to be spelled out clearly. I'm also confident that IFS would be clearer than a raft of nested IF statements.

Just to take those that you've now stated:

IFS(

volume=<53000,"G1CARTON",

AND(volume>53000,volume<1000000,kg<=90),"G>1CARTON",

AND(volume>53000,volume<1000000,kg>90),"G1PALLET",

AND(volume>1000000,kg<400),"G1PALLET",

AND(volume>1000000,kg=>400),"G1>PALLET"

)

For volume and kg above, substitute the appropriate cell references

I suspect, though you'd have to test this, that it could be simplified some

IFS(

volume=<53000,"G1CARTON",

AND(volume>53000,volume<1000000,kg<=90),"G>1CARTON",

AND(volume<1000000,kg>90),"G1PALLET",

AND(volume>1000000,kg<400),"G1PALLET",

AND(volume>1000000,kg=>400),"G1>PALLET"

)

[#8]

@mathetes 

Thanks very much this returned the required reference's. Before the formula looks at these calculations is there anyway it could first eliminate the following fields IF(B2=3001,"N/A",IF(C2="IN","GEXPBOOK",IF(E2="GDG","GDG",IF(D2=1,"G1LINE") and then do the weight and volume calculations as you have done above.  I think its the order that i'm asking excel to work out the calculation is wrong.

Im sorry I know i'm hard work I haven't used Excel for a long time. Thanks

[#9 · Accepted solution]

@rach1345 

I've put those additional conditions in; I'll leave it to you to verify that it's working correctly.

[#10]

@rach1345  alternatively you could use a lookup table:

for ease I defined the table range as WVtable

[#11]

Thanks mathetes it worked really appreciate all your help

[#12]

@mtarler 

Great solution, Matt (so long as all the columns in the data table  cooperate).

I often suggest tables and VLOOKUPs as alternatives to multiple levels of IF. I've never seen MATCH(INDEX nested within VLOOKUP, but I like it and am filing that away for comparable situations.

Decades ago (literally) I nested an HLOOKUP within a VLOOKUP ... using at the time Lotus 1-2-3, if that's an indication of the time; I think it was in the early 1980s.

[#13]

yes, for some reason the VLOOKUP - MATCH seemed to make more sense. The final INDEX inside was just to index the named range instead of having another range or name.