Solved

Excel ranges

Posted on 2012-09-03
269 Views
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
0
Question by:DAVID131

LVL 18

Expert Comment

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

Author Comment

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

LVL 18

Expert Comment

is this it?

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

Author Comment

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

LVL 18

Expert Comment

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

Author Comment

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

LVL 18

Expert Comment

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

Author Comment

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

LVL 18

Expert Comment

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

Author Comment

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

LVL 18

Expert Comment

show me how to come up with the value 1000.
0

Author Comment

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

LVL 18

Expert Comment

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

LVL 10

Expert Comment

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 Comment

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

LVL 10

Accepted Solution

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

Featured Post

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.