Subtracting Values from a Ranked List
 

I have a column of costs that I have ranked from highest to lowest. (1 being
lowest)
The column is totaled $8
I have a Spend of $4
I want to reduce the total from $8 to $4 by subtracting the lowest cost
items until the column total = the Spend.
e.g.
Spend $4
Cost Rank Result
$1 3 $0
$3 1 $3
$2 2 $1
$1 5 $0
$1 4 $0
--- -----
$8 $4
--- -----

Ant help appreciated.


Mal

Subtracting Values from a Ranked List
 

Anyone?
I'm really struggling with this.
Thanks,
Mal

"Mal" wrote in message
d.com...
I have a column of costs that I have ranked from highest to lowest. (1
being lowest)
The column is totaled $8
I have a Spend of $4
I want to reduce the total from $8 to $4 by subtracting the lowest cost
items until the column total = the Spend.
e.g.
Spend $4
Cost Rank Result
$1 3 $0
$3 1 $3
$2 2 $1
$1 5 $0
$1 4 $0
--- -----
$8 $4
--- -----

Ant help appreciated.


Mal

Subtracting Values from a Ranked List
 

On Wed, 11 Jan 2012 05:56:16 +1000, "Mal" wrote:

I have a column of costs that I have ranked from highest to lowest. (1 being
lowest)
The column is totaled $8
I have a Spend of $4
I want to reduce the total from $8 to $4 by subtracting the lowest cost
items until the column total = the Spend.
e.g.
Spend $4
Cost Rank Result
$1 3 $0
$3 1 $3
$2 2 $1
$1 5 $0
$1 4 $0
--- -----
$8 $4
--- -----

Ant help appreciated.


Mal


I don't understand what you are trying to do based on your description and example.

You write you want to "subtract the lowest Cost items ...". It looks like you've subtracted the three $1 items, and 1/2 of the $2 item. (i.e. NOT the whole $2 item).
Is there any significance to the Rank? You don't mention it in your description of what you want to do.

Subtracting Values from a Ranked List
 

Sorry Ron.
What I want to do is reduce the "Cost" $8 to the "Spend" $4 by reducing the
lowest cost items on the list. That is why I have ranked the list.
So in this example the difference between the Cost and the spend is $4 so we
reduce the three $1 items to $0 and take $1 from the $2 item reducing it to
$1.
We then end up with the $3 item unchanged, the $2 item reduced to $1 and
each of the $1 items reduced to $0 with the Spend $4.
One point. If the ranking is equal as the 3 x $1 items in the example are,
and only $2 needed to be deducted, it is does not matter which $1 items are
deleted to end up at the Spend amount.
Hope this clarifies.
Thanks,
Mal
"Ron Rosenfeld" wrote in message
...
On Wed, 11 Jan 2012 05:56:16 +1000, "Mal" wrote:

I have a column of costs that I have ranked from highest to lowest. (1
being
lowest)
The column is totaled $8
I have a Spend of $4
I want to reduce the total from $8 to $4 by subtracting the lowest cost
items until the column total = the Spend.
e.g.
Spend $4
Cost Rank Result
$1 3 $0
$3 1 $3
$2 2 $1
$1 5 $0
$1 4 $0
--- -----
$8 $4
--- -----

Ant help appreciated.


Mal


I don't understand what you are trying to do based on your description and
example.

You write you want to "subtract the lowest Cost items ...". It looks like
you've subtracted the three $1 items, and 1/2 of the $2 item. (i.e. NOT
the whole $2 item).
Is there any significance to the Rank? You don't mention it in your
description of what you want to do.

Subtracting Values from a Ranked List
 

On Fri, 13 Jan 2012 04:42:26 +1000, "Mal" wrote:

Sorry Ron.
What I want to do is reduce the "Cost" $8 to the "Spend" $4 by reducing the
lowest cost items on the list. That is why I have ranked the list.
So in this example the difference between the Cost and the spend is $4 so we
reduce the three $1 items to $0 and take $1 from the $2 item reducing it to
$1.
We then end up with the $3 item unchanged, the $2 item reduced to $1 and
each of the $1 items reduced to $0 with the Spend $4.
One point. If the ranking is equal as the 3 x $1 items in the example are,
and only $2 needed to be deducted, it is does not matter which $1 items are
deleted to end up at the Spend amount.
Hope this clarifies.
Thanks,
Mal

One way to do this would be to sort your list in Descending order by Cost of the Item. Then, assuming your highest cost item is in A3, and "Cost" refers to your range A3:A7, for example, and Spend refers to $4 or to $B$1:

B3:
=IF((Spend-SUM(OFFSET(Cost,0,0,ROWS($1:1)))+A3)0,
MIN(A3,(Spend-SUM(OFFSET(Cost,0,0,ROWS($1:1)))+A3)),0)

and fill down as far as needed.

If you need the list sorted in the ORIGINAL order, I would use a VBA macro to get the results

Subtracting Values from a Ranked List
 

OK . Well I went down the VBA route and the code I ended up with is shown
below.
It seems to work. So I am happy.
I don't really know the VBA code so if there is a better VBA alternative, I
am happy to use it.
Thanks for the help.
Mal


Sub Test()

Set rng1 = Range("c5:c9"): 'Cost range
Set rng2 = Range("d5:d9"): 'Rank Cost
Set rng3 = Range("e5:e9"): 'Revised Cost
Set rng4 = Range("b2"): 'Spend
Set rng5 = Range("c11"): ' Total "Cost"
MaxVal = Application.Max(rng2)
aa = rng4.Value: ' Spend
bb = rng5.Value: ' Total "Cost"
cc = bb - aa
rng3.ClearContents
dd = MaxVal

For i = 1 To dd
ee = rng2(i).Value
If ee < MaxVal Then GoTo Counter

For j = i To i

ff = rng1(j).Value: gg = rng2(j).Value: hh = rng3(j).Value

hh = ff

Do While hh 0 And cc 0
hh = hh - 1: cc = cc - 1
Loop
MaxVal = MaxVal - 1

rng3(j).Value = hh
i = 0
Next j
Counter:
Next i

End Sub
"Ron Rosenfeld" wrote in message
...
On Fri, 13 Jan 2012 04:42:26 +1000, "Mal" wrote:

Sorry Ron.
What I want to do is reduce the "Cost" $8 to the "Spend" $4 by reducing
the
lowest cost items on the list. That is why I have ranked the list.
So in this example the difference between the Cost and the spend is $4 so
we
reduce the three $1 items to $0 and take $1 from the $2 item reducing it
to
$1.
We then end up with the $3 item unchanged, the $2 item reduced to $1 and
each of the $1 items reduced to $0 with the Spend $4.
One point. If the ranking is equal as the 3 x $1 items in the example are,
and only $2 needed to be deducted, it is does not matter which $1 items
are
deleted to end up at the Spend amount.
Hope this clarifies.
Thanks,
Mal

One way to do this would be to sort your list in Descending order by Cost
of the Item. Then, assuming your highest cost item is in A3, and "Cost"
refers to your range A3:A7, for example, and Spend refers to $4 or to
$B$1:

B3:
=IF((Spend-SUM(OFFSET(Cost,0,0,ROWS($1:1)))+A3)0,
MIN(A3,(Spend-SUM(OFFSET(Cost,0,0,ROWS($1:1)))+A3)),0)

and fill down as far as needed.

If you need the list sorted in the ORIGINAL order, I would use a VBA macro
to get the results

Subtracting Values from a Ranked List
 

On Sun, 15 Jan 2012 11:38:48 +1000, "Mal" wrote:

It seems to work. So I am happy.
I don't really know the VBA code so if there is a better VBA alternative, I
am happy to use it.

Perfect is the enemy of good enough. If it is working and suits your needs, you're done.

Subtracting Values from a Ranked List
 

Thanks Ron.

Mal

"Ron Rosenfeld" wrote in message
...
On Sun, 15 Jan 2012 11:38:48 +1000, "Mal" wrote:

It seems to work. So I am happy.
I don't really know the VBA code so if there is a better VBA alternative,
I
am happy to use it.

Perfect is the enemy of good enough. If it is working and suits your
needs, you're done.