Thank you very much
Eric

"Max" wrote:

Here's one crack at it ..

Assuming the posted table is in cols A to D, data from row2 down

Points______Units______Lost per unit______Max Lost
10000______1_________0_______________0
11000______2_________0_______________0
13000______3_________0_______________0
etc

In C2:D2 will be simply zero inputs

Put in C3:
=IF(A3="","",IF(A3<A2,(A2-A3)/INDEX($B$2:B3,MATCH(A2,$A$2:A3,0)),0))

Put in D3:
=IF(A3="","",IF(A3<A2,INDEX($C$2:C3,MATCH(A2,$A$2: A3,0))+C3,MAX($D$2:D2)))

Select C3:D3, copy down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Eric" wrote in message
...
Can anyone help me on following calucation for Max. Lost?
The procedure for calculation is clear, but I don't how to fit the
function
into excel.
Could anyone give me any suggestion please?

Colume A for Points
Colume B for Units
Colume C for Lost per unit
Colume D for Max Lost

Delta = 1000;
Points______Units______Lost per unit______Max Lost
10000______1_________0_______________0
11000______2_________0_______________0
13000______3_________0_______________0
[13000-11500] / 3 units = 500 points per unit
11500______2_________500_____________500
[11500-10500] / 2 units = 500 points per unit
10500______1_________500_____________1000 [500 + 500 = 1000]
12500______2_________0_______________1000
16500______4_________0_______________1000
24500______5_________0_______________1000
[24500-19500] / 5 units = 1000 points per unit
19500______4_________1000____________1000
[19500-17500] / 4 units = 500 points per unit
17500______4_________500_____________1500

A lost is reduced from 13000 to 11500 based on 3 units, then the lost per
unit will be [13000-11500] / 3 units = 500 points per unit, at this level
of
11500, the highest point from above is 13000, then the max. lost will be
changed from previous 0 to 500.

A lost is reduced from 11500 to 15500 based on 2 units, then the lost per
unit will be [11500-10500] / 2 units = 500 points per unit, at this level
of
10500, the highest point from above is 13000, then the max. lost will be
sum
up the lost per unit between 13000 and 10500, which is 500 + 500 = 1000
lost
per unit, since it is larger than previous 500, then it will be changed to
1000.

A lost is reduced from 24500 to 19500 based on 5 units, then the lost per
unit will be [24500-19500] / 5 units = 1000 points per unit, at this level
of
19500, the highest point from above is 24500, then the max. lost will be
sum
up the lost per unit between 24500 and 19500, which is 0 + 1000 = 1000
lost
per unit, since it is equal to previous 1000, then it will not be changed.

A lost is reduced from 19500 to 17500 based on 4 units, then the lost per
unit will be [19500-17500] / 4 units = 500 points per unit, at this level
of
17500, the highest point from above is 24500, then the max. lost will be
sum
up the lost per unit between 24500 and 17500, which is 1000 + 500 = 1500
lost
per unit, since it is larger than previous 1000, then it will be changed
to
1500.

Thank you in advance
Eric