IF(AND's and IF(OR's

Damon Lloyd

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))))))))))))

Paul Newcome

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))))

Paul Newcome

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)))))

Paul Newcome

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))))

Damon Lloyd

Thanks Paul, Perfect!

Question: if another customer gets extended the reduced pricing, would i be able to add their name in-line 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)))))

Paul Newcome

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)))))