Link to home
Create AccountLog in
Avatar of DAVID131
DAVID131

asked on

Performing Excel calculations based on data in 2 independent, but overlapping, data sets

I have two sets of data that use differing ranges;
one for calculating the quintile ranges of Product Values by actual Sales
the other for bonus payments for sales based on a range of Product Values.
I need to be able to calculate bonuses when measured against the Sales quintiles.
Because the two ranges are different for all the products I need to help to find a solution
Thanks
D
Mismatched-bands-for-bonuses.xlsm
Avatar of Ken Butters
Ken Butters
Flag of United States of America image

If I am understanding correctly:

You have actual sales for
product value from 1 to 5,734.
product value from 5735 to 10,000
product value from 10,001 to 16,500

you are giving a bonus of 80 per sale for anything in range of 0 to 12,000
you are giving a bonus of 150 per sale for anything in range of 12,001 to 20,000.

From the information supplied in the table... The overlap in this instance is that you get a higher bonus for the actual sales of product value under 12,000 (80) vs . product value over 12,000 (150).

Since it is not possible to tell how much of the actual sales in the range from 10,001 to 16,500 was above or below 12,000, so I don't believe you have enough information to calculate this manually... let alone enough information to caclulate with a formula or code.

Said another way...if you don't know (or cannot calculate) exactly how many actual sales you had below 12,000 then you cannot take that unknown number times 80.  Likewise if you cannot determine an exact number of sales between and including 12,001 and 20,000, then there is no way to calculate how much that number times 150 would be.

Does that make sense?   Or am I misunderstanding the issue?

In order to calculate this, you need actual sales broken down by the same ranges as your Bonus Bands.... or you could also calculate it if you had available the product value for each and every sale.
Avatar of DAVID131
DAVID131

ASKER

Thanks for this but it is not feasible to report sales against bonus bands without creating a report that currently does not exist. Creation of new reports is an IT beaureaucratic nightmare taking months.
ASKER CERTIFIED SOLUTION
Avatar of Ken Butters
Ken Butters
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I've requested that this question be deleted for the following reason:

Advised by moderator to delete as experts have not responded
Sometimes, as in this case... The correct a answer is.... It cannot be done with the information available.   It is not the solution desired.... But it is the inly accurate one
Thanks for your comment
After the moderator indicated that there were no further responses forthcoming I spent around 10 - 15 hours developing a solution myself that gives me ability to model differing incompatible ranges.
D
Then I guess I question the accuracy of your model... and here is why I think that...

You have an arbitrary number of sales that should get a bonus of $80. (because you have no idea how many are below and how many are above the threshold).

You also have an arbitrary number of sales that should get a bonus of $150.00 (again because you have no idea how many are below and how many are above the threshold).

So how can you possibly model data that will multiply the correct number of $80 bonuses with the correct number of $150.00 bonuses to arrive at a total bonus amount for the sales that you have had.

You could possibly arrive at some sort of guesstimate that assumes some sort of even distribution of all sales across all amounts... but that would at best be a guess, not an accurate calculation.
You are correct the solution is accurate to about plus/minus 5% but I am pragmatic enough to know that in the absence of anything bette, this is as close as I will probably get until IT get their act together.
Thanks
D