Subtracting Values from a Ranked List

[Mal]

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

 

[Mal]

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

 

[Ron Rosenfeld]

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.

 

[Mal]

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]

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

 

[Mal]

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]

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.

 

[Mal]

Thanks Ron.

Mal

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