Forum Discussion
adding information to nested if formula not working
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 |
12 Replies
- hi i tried using the ifs formula but it didn't return the correct fields such as G1PALLET
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.
- 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
- 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.
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)
