# Subtracting Values from a Ranked List

M

#### 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

Ad

M

#### Mal

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

M

#### 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

M

#### 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

Ad

M

#### Mal

Thanks Ron.

Mal

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

## Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.