Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the maximum lost?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the maximum lost?
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the maximum lost?
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the maximum lost?
You're welcome !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Eric" wrote in message ... Thank you very much Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding maximum of various ranges of data | Excel Discussion (Misc queries) | |||
Finding Maximum | Excel Worksheet Functions | |||
find which column has the maximum value | Excel Discussion (Misc queries) | |||
Tricky 'Find Maximum' problem seeks formula | Excel Worksheet Functions | |||
Finding the maximum of a subset of values on a different sheet | Excel Discussion (Misc queries) |