Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Eric
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Eric
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Finding maximum of various ranges of data thekovinc Excel Discussion (Misc queries) 2 January 23rd 06 09:41 PM
Finding Maximum Roger H. Excel Worksheet Functions 1 January 11th 06 02:29 AM
find which column has the maximum value Frank Drost Excel Discussion (Misc queries) 2 December 15th 05 02:09 AM
Tricky 'Find Maximum' problem seeks formula tx12345 Excel Worksheet Functions 6 December 5th 05 11:26 PM
Finding the maximum of a subset of values on a different sheet rmellison Excel Discussion (Misc queries) 4 December 1st 05 04:56 PM


All times are GMT +1. The time now is 08:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"