Forum Discussion
adding information to nested if formula not working
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 helpIts 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"
)
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