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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

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

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!