Avoiding "IF-THEN" with multiple clauses for complex table

[guillebeck]

Hello excellians! I'm trying to solve my requirement withouth entering into a very large IF-THEN function. Here's the situation: I have a table to define the discount to be applied to a reseller depending on:
a) amount of acumulated purchases. These are the rows. i.e. 1-1500, 1500-5000, 5001-1000
b) tasks performed. These are the columns. i.e. "meetings completed", "implementation finished"

So for example:
if the reseller has already purchased 500 units AND he has completed task from column A, then his discount is 10%
if the reseller has already purchased 500 units AND he has completed task from column B instead of column A, his discount is 15%
if acumulated purchased units are 1700 and task from column B, discount is now 25%.

Something like that.
Any dynamic idea on how to solve this logic is appreciated!

Thanks,
GB

 

[6StringJazzer]

Put your criteria in a table and use a simple formula to look up the discount.

What does your data look like? (how many units are purchased, what tasks have been completed)

Your examples say "i.e.". To get an actual table and formula, please give complete data, not just examples. Also explain "task from column A" and "task from column B". What is in column A and B? How does this relate to the tasks you mentioned in b) above?

 

[guillebeck]

Hey Jeff! Thanks for answering.
Please see attached screenshot. I hope it explains better what I was trying to say previously.

The idea is that, depending on the accumulated purchases and if you've completed tasks (meetings, demos), you get a different discount.
If you're between 1-1500 items purchased and you've completed the meetings, you get 15%. If you've done the demos, 7,5%.

Makes sense?

Thanks
Guillermo

 

[6StringJazzer]

OK, that looks like the setup that tells you what discount to apply. That's an excellent start. Can you show the data that gives the number of purchases and tasks completed, and where you want the result to go?

 

[guillebeck]

Here's a screenshot of what should happen.
In the section below you see that I need to input the number of accumulated purchases so far.
Then I input how many units the customer is purchasing now, the total price and the price divided per product type.
Next I need to say if the customer has completed the needed tasks.
Finally, I manually typed the discount that should be applied per product type depending on (a) accumulated purchases and (b) if tasks were completed or not.
Then, the resulting discount price per product type.

The grayed cells are what I need to automate.

Thanks again,
GB