# Excel ranges

I have 5 bands of commissions for sales based on customer consumptions.
I receive historic analysis of cutomer consumptions based on customer deciles
I want to be able to calculate the commissions I will pay for my forecast next quarters sales based on the historic consumptions.
How can I get around the incompatible range sets
I have attached a sample of the data involved. (the ranges are not constant and the number of commission bands can change)
David
Formulae-for-incompatible-ranges.xlsm
###### Who is Participating?

Commented:
To award credit, you need to accept the solution and give it a letter grade.

To see the code, press ALT+F11 to open the Visual Basic editor and then open Sheet1. Now that you've confirmed the behavior, I've added some comments to the macro.

The macro only updates the projections when you change the forecast (cell J1).
Formulae-for-incompatible-ranges.xlsm
0

IT super heroCommented:
what are the "incompatible range sets"? what's the "formula" you're trying to acomplish?
0

Author Commented:
In cells I4:I8 what would be the split of the cell J4 customers, based on the decile ranges in B4:C13 giving (in this example) deciles of 500 customers
Hope that explains
0

IT super heroCommented:
is this it?

=J1/(PERCENTILE(B4:C13;0,1))
0

Author Commented:
Formula produced error, could be due to semi-colon after C13 but error persisted even when changed semi-colon to comma.
Not sure of percentile usage?
I forecast 5,000 new customers who will have varying levels of product usage from 0 - 150,000 units each.
Our Reporting unit produces customer usage figures based on deciles i.e 10% of customers have a usage range of 0 - 3,000, 10% have a usage range of 3,001 - 5,000, etc
Our sales commission bands are not the same as the ranges as the decile ranges, taking our first commission band of 0 - 5,997 - how many of my 5,000 customers will fall into this band, based on the decile info
Hope that helps
0

IT super heroCommented:
=\$J\$1/(PERCENTILE(B4:C4;0,1))
=\$J\$1/(PERCENTILE(B5:C5;0,1))
...
=\$J\$1/(PERCENTILE(B8:C8;0,1))
0

Author Commented:
The error warning is still occurring as shown in attachment
PERCENTILE-ERROR-WARNING.docx
0

IT super heroCommented:
There a "VLOOKUP" referenced on the box to the left of the formula imput. why?
0

Author Commented:
The drop down list of recently used functions appears as soon as the = sign is typed in.
There is no usage of VLOOKUP or other functions
0

IT super heroCommented:
my file has no errors (only a "Formula omits adjecent cells" warning). see atached file
Formulae-for-incompatible-ranges.xlsm
0

Author Commented:
Your formula is slightly different to mine - have rectified in my copy
However the total number of customers in I4:I8 must be equal to the 5,000 in J1
eg
0                   5,997           £80         1,000
5998           15,309         £110         2,000
15310         24,403         £140         1,000
24404         59,604         £210            500
59605        150,000        £250            500

Any suggestions?
0

IT super heroCommented:
show me how to come up with the value 1000.
0

Author Commented:
I wish I could - that is the challenge - how to arrive at the figure based on the data in the spreadsheet (1,000 was just to demo what is needed)
A number of years ago I saw how this could be done but regretably I never kept a record of the process or the formulae
0

IT super heroCommented:
you should check the "marketing statistics analysis" forum before posting on the MS Excel one ;)
0

Commented:
Is the projection to assume that customers are distributed evenly across each decile. For example, that in the third decile usage would be 5001, 5005, 5009, etc.?

And, if that's true, would the £80 commission apply to 1,249 customers? (That's 500 for the first decile, 500 for the second decile, and 249 for the third decile.)

If so, the logic is a bit complicated for a formula, but I've written a macro that should give the results you're looking for.
Formulae-for-incompatible-ranges.xlsm
0

Author Commented:
Good Evening
You are correct we do have to assume an even decile distribution (even though it is not) because that is the best intelligence we receive from out Reporting Division and we are unlikely to see anything better in the medium to long term.

I am quite new to EE, how do we progress this now as I feel you have delivered my solution, although I can not see the macro that is driving the logic
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.