Lookup/Ranges

[Drreamsurferr]

Hi I have an excel question. I need formula to calculate a fee based on performance. For example I have 4 unique keys with the following performance:

KEY Performance
20 10,000
21 20,000
22 30,000
23 40,000

I need to work out how I can formula drive a fee calculation based on performance which is subject to different ranges:

KEY Min Fee Fee 1 Fee 2 Fee 3 Fee 4
20 100 0.1 0.2 0.3 0.4
21 100 0.1 0.2 0.3 0.4
22 100 0.1 0.2 0.3 0.4
23 100 0.1 0.2 0.3 0.4

For example key 20 has a min fee chargeable of 100, however a fee is chargable based on performance as follows:

Fee Range 1 0-9999
Fee Range 2 10000-19999
Fee Range 3 20000-29999
Fee Range 4 30000-99999999


So key 20 gets charged 0.1% of amounts between 0&9,999, 0.2% of amounts between 10,000&19,999, 0.3% on amounts between 20,000& 29,999 and 0.4% on the rest.

How can this be combined into a lookup/range/low-high formula to extract the correct values????

[Drreamsurferr]

Just reworking it - looks like it will!!

[Drreamsurferr]

Hi,

Thanks this doesn't slove it though. As there is a sliding scale i.e

Key 21 gets charged 0.1% of amounts between 0 & 9,999, 0.2% of amounts between 10,000&19,999, 0.3% on amounts between 20,000& 29,999 and 0.4% on the rest etc. So gets a fee of (0.1%*9,999)+(0.1%*10,000). Any Ideas?

Appreciated!!!!

[DonkeyOte]

You can use SUMPRODUCT to handle tiered calculations but your question is not very clear ... could you please furnish us with some examples of the calculation in action ?

I guess I don't understand the significance of the first PERFORMANCE table - how does this impact your calcs ?

[Drreamsurferr]

Example Calc (this is manual, I would like to formula drive):

Source DATA

KEY PERFORMANCE
XYZ 150,215,665


KEY Fee 1 Fee 2 Fee 3
XYZ 1% 0.40% 0.25%

Fee Range 1 0-50,000,000
Fee Range 2 50000000-150,000,000
Fee Range 3 150,000,000-999,999,999


EXAMPLE CALC:

PERFORMANCE FEE RATE
50,000,000 0.50% 250,000
100,000,000 0.40% 400,000
215,665 0.25% 539
150,215,665 650,539

[DonkeyOte]

OK to demo concept of SUMPRODUCT, if we assume value is in B2

Please Login or Register  to view this content.

Note I used your initial rate of 1% of the first band, you revised to 0.5% in your calculation which would give 650,539, ie

Please Login or Register  to view this content.

As you can see in the last array of values we are storing the step change between the bands (ie first band is 0.5%, next band is 0.1% lower, third band is 0.15% lower than 2nd band etc...)

[DonkeyOte]

To clarify further... this is a demo... the inline Arrays {value1,value2,value3} can be replaced with range references (eg D1:D3) but that is perhaps the next step - we'd need to get a better feel for where your data is stored... a sample file is generally helpful (obviously dummy values otherwise confidential)

[Drreamsurferr]

Looks like it's working, I'd like to replace with ranges - home time in the UK here, so hopefully pick up tomorrow - so far so good!!! :-)

[Drreamsurferr]

Hi,

Hoping you can help again. In order to work this I attach a s/sheet showing the data I have.

Tab 1 - Rates chargeable at each range (ie charge rate 1 for 1st 1 range) for each KEY

Tab 2 - Range for rates for each key (ie for Key BC00114-B charge first 100,000,000 @ rate 1, next 100,000,000 @ rate 2, and anything over rate1&2 @ Rate 3

Tab 3 - Raw Data

Tab 4 - Performance KEY, i.e driver for rates.

I have enclosed a sample of the manual calc but I would like to one formul - hopefully using the SUMPRODUCT function you described - can you let me know if this is possible?????

Thanks :-)

[DonkeyOte]

Nope, not using that layout I'm afraid...

In the attached I've created a table based off your original using formulae, see DATA!F1:J52 ...

With the revised layout you can calculate relatively easily (see RATE!D7), eg:

Please Login or Register  to view this content.

NOTE: I've removed the other data to keep file small.

[Drreamsurferr]

This works very well!!

You have solved a real brain teaser here. Much appreciated

Linsey

[Drreamsurferr]

Hi there,

I have just tried to rework the calc following my posts. Yours works for the sample provided & I would like to use it, but have I incorrectly pasted the calc in the enclosed??? Feedback appreciated.

[DonkeyOte]

Only just looked at this - from what I can tell re: the INDEX you need to add a space to the MATCH preceding the "00" string, ie:

Please Login or Register  to view this content.

[DonkeyOte]

Further to prior post...

The SUMPRODUCT won't work if you have error values in your ranges so I would suggest per your file you do the following:

Please Login or Register  to view this content.

You should then find CALCS sheet works as expected.