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
DAVID131Asked:
Who is Participating?
 
tdlewisCommented:
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
 
x-menIT super heroCommented:
what are the "incompatible range sets"? what's the "formula" you're trying to acomplish?
0
 
DAVID131Author 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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
x-menIT super heroCommented:
is this it?

=J1/(PERCENTILE(B4:C13;0,1))
0
 
DAVID131Author 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
 
x-menIT super heroCommented:
=$J$1/(PERCENTILE(B4:C4;0,1))
=$J$1/(PERCENTILE(B5:C5;0,1))
...
=$J$1/(PERCENTILE(B8:C8;0,1))
0
 
DAVID131Author Commented:
The error warning is still occurring as shown in attachment
PERCENTILE-ERROR-WARNING.docx
0
 
x-menIT super heroCommented:
There a "VLOOKUP" referenced on the box to the left of the formula imput. why?
0
 
DAVID131Author 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
 
x-menIT super heroCommented:
my file has no errors (only a "Formula omits adjecent cells" warning). see atached file
Formulae-for-incompatible-ranges.xlsm
0
 
DAVID131Author 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
 
x-menIT super heroCommented:
show me how to come up with the value 1000.
0
 
DAVID131Author 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
 
x-menIT super heroCommented:
you should check the "marketing statistics analysis" forum before posting on the MS Excel one ;)
0
 
tdlewisCommented:
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
 
DAVID131Author 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.
Your methodology for the split is spot on and your solution's answers look sensible.

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.

All Courses

From novice to tech pro — start learning today.