Tricky If Formula for Auto Postage Selection?

[razzandy]

What I am trying to do with little success is have a CELL Select the Correct Postage service for several conditions.

If A1 = “DPD” Then Display “DPD” But if A1 = “PPI LL” and C1<751 then display A1 but if C1>750 Display “PPI Packet” but if C1>1250 and B1 = “2nd Class” then display “Tracked Standard” but if B1 = “1st Class” Then Display “PPI Packet” but if B1 = “DPD” then display “DPD”

I have seen examples of separate criteria tables on other sheets which formulas use to calculate the data but I am not sure how to do that.

Basically I have 2 Carriers 1 with with several size and weight restrictions:
The customer can choose from “FREE 2nd Class Postage” or “1st Class Postage” or “DPD”. The problem comes with weights and packet sizes. At the moment we manually go through the orders and sort which are “PPI LL” or “PPI Packets” or DPD. I would like Excel to choose for me.

Just for reference PPI LL is our term for Royal Mail Large Letter and PPI Packets is for Royal Mail Packets.

We have the products set with a default postage method but the customer could choose different and/or the weight/size of parcel could provoke a change!

Hope this makes sense and somebody as far better than me on Excel

Thanks in advance

 

[razzandy]

Re: Tricky If Formula with Lots of Criteria Maybe Arrays?

Any help would be great just to get me started.

Cheers

 

[barry houdini]

If A1 = “DPD” Then Display “DPD” But if A1 = “PPI LL” and C1<751 then display A1 but if C1>750 Display “PPI Packet” but if C1>1250 and B1 = “2nd Class” then display “Tracked Standard” but if B1 = “1st Class” Then Display “PPI Packet” but if B1 = “DPD” then display “DPD”

A table is good if you just have a one to one relationship - one value = another value, but what you have here is a little more complex so you might need to construct a nested IF formula, this would follow the logic above...I think

=IF(A1="DPD",A1,IF(A1="PPI LL",IF(C1<751,A1,IF(C1>1250,IF(B1="2nd Class","Tracked Standard",IF(B1="1st Class","PPI Packet",IF(B1="DPD",B1,"x"))),"PPI Packet")),"x"))

In some circumstances it will return "x", (e.g. if A1 is blank) - you can change that to a blank "", or some other text like "error"......

 

[razzandy]

Hi Barry

Thanks for your solution I will give it a go

I’m not well up with nested formula all I really know is to hit Ctrl Shift and Enter! Which bits would I nest?

 

[barry houdini]

That formula right there is "nested". Nesting just means putting one function inside another, so if I used a simple IF function like

=IF(A1>50,"Yes","No")

....but now I decide I want another IF function to take the place of "No" like this

=IF(A1>50,"Yes",IF(A1< 15,"No","Maybe"))

then that nesting.....