IF(AND's and IF(OR's
I have the below formula working to assign a price pursuant to particular job's complexity.
=IF(COMPLEXITY517 = "LOW", 1000, IF(COMPLEXITY517 = "MID", 1500, IF(COMPLEXITY517 = "HIGH", 2000, IF(COMPLEXITY517 = "HQP", 2500))))
However, I have 2 tiers of customer pricing that i'd like to track. The above covers +90% of the entries. However, for example, if the customer is Widgets Inc, the prices would be 820, 1250, 1750, 2250.
How would i go about keeping the above formula while adding Widget's pricing structure?
I've tried several variations of the below formula to invariable failure
=IF(CUSTOMER517 = “WIDGET”, IF(COMPLEXITY517 = "LOW", 850, IF(CUSTOMER517 = “WIDGET”, IF(COMPLEXITY517 = "MID", 1250,IF(CUSTOMER517 = “WIDGET”, IF(COMPLEXITY517 = "HIGH", 1750, IF(CUSTOMER517 = “WIDGET”, IF(COMPLEXITY517 = "HQP", 2250, IF(COMPLEXITY517 = "LOW", 1000, IF(COMPLEXITY517 = "MID", 1500, IF(COMPLEXITY517 = "HIGH", 2000, IF(COMPLEXITY517 = "HQP", 2500))))))))))))
Best Answers

Try something along these lines...
=IF(Customer@row = "Widget", IF(Complexity@row = "Low", 850, IF(Complexity@row = "Mid", 1250, IF(Complexity@row = "High", 1750, 2250))), IF(Complexity@row = "Low", 1000, IF(Complexity@row = "Mid", 1500, IF(Complexity@row = "High", 2000, 2500))))

You would need to use an OR statement to include additional customers for the discounted pricing.
=IF(OR(CUSTOMER@row = "WIDGET", CUSTOMER@row = "Another Customer"), IF(COMPLEXITY@row = "Low", 850, IF(COMPLEXITY@row = "Mid", 1250, IF(COMPLEXITY@row = "High", 1750, IF(COMPLEXITY@row = "hqp", 2250)))), IF(COMPLEXITY@row = "Low", 1000, IF(COMPLEXITY@row = "Mid", 1500, IF(COMPLEXITY@row = "High", 2000, IF(COMPLEXITY@row = "hqp", 2500)))))
Answers

Try something along these lines...
=IF(Customer@row = "Widget", IF(Complexity@row = "Low", 850, IF(Complexity@row = "Mid", 1250, IF(Complexity@row = "High", 1750, 2250))), IF(Complexity@row = "Low", 1000, IF(Complexity@row = "Mid", 1500, IF(Complexity@row = "High", 2000, 2500))))

Thanks Paul, Perfect!
Question: if another customer gets extended the reduced pricing, would i be able to add their name inline after Widget?
For example: =IF(CUSTOMER@row = "WIDGET", IF(CUSTOMER@row="COMPANY", IF(COMPLEXITY@row...
Or would the rest of the formula's syntax have to be adapted?
As it stands, the final functional formula reads as below:
=IF(CUSTOMER@row = "WIDGET", IF(COMPLEXITY@row = "Low", 850, IF(COMPLEXITY@row = "Mid", 1250, IF(COMPLEXITY@row = "High", 1750, IF(COMPLEXITY@row = "hqp", 2250)))), IF(COMPLEXITY@row = "Low", 1000, IF(COMPLEXITY@row = "Mid", 1500, IF(COMPLEXITY@row = "High", 2000, IF(COMPLEXITY@row = "hqp", 2500)))))

You would need to use an OR statement to include additional customers for the discounted pricing.
=IF(OR(CUSTOMER@row = "WIDGET", CUSTOMER@row = "Another Customer"), IF(COMPLEXITY@row = "Low", 850, IF(COMPLEXITY@row = "Mid", 1250, IF(COMPLEXITY@row = "High", 1750, IF(COMPLEXITY@row = "hqp", 2250)))), IF(COMPLEXITY@row = "Low", 1000, IF(COMPLEXITY@row = "Mid", 1500, IF(COMPLEXITY@row = "High", 2000, IF(COMPLEXITY@row = "hqp", 2500)))))
Help Article Resources
Categories
Check out the Formula Handbook template!