question driving me nuts
 

Okay...I never thought I would ever have to use excel again. Lo and behold, I
am now the owner of a business and have forgotten how to do some things. I
know how to create formaulas for adding and stuff. I forgot how to make my
calculatons so that my deposit would be broken down. For example say I have 5
of each dollar denomination (1,2,5,10,20,50,100) and coin (1,5,10,25,50,$1).
My deposit has to be 790 (5 of each denomination-150).

What I want my sheet to do is tell me how much of each denomination I should
pull out in terms of numbers instead of dollar amount. For exaple instead of
telling me to pull $790 I want it to tell me to pull 5-$100 bills 5-$50 bills
and 4-10 bills. Basically breaking down my cash with the largest possible
denomination until there can be no more. Any help would be deeply appreciated.

question driving me nuts
 

Hi,

You can do this with the "Solver" Add-in in Excel (for that, the add-in
should have been installed in Excel. For installing it, "Tools" -- "Add-Ins
....", check "Solver Add-In"; the installation may ask for the Excel or Office
CD)

In Column A enter the denominations as follows:
100 in A2, 50 in A3, 20 in A4,.....1 in A8, 1 in A9, 0.5 in A10, .....0.01
in A14.
In Column B enter the number of each denomination to be used, as follows:
Enter 1 (0 will also be ok) in B2, B3, ..... B14.
In C2 enter the target amount [e.g., 790 or any other amount you want]
In C3, enter the formula =SUMPRODUCT(A2:A14,B2:B14) [calculated total]
In C4, enter the formula =SUM(B2:B14) [total number of bills/coins used]

Invoke "Solver" by doing the following:

"Tools" -- "Solver", and in the 'Solver Parameters' window,

Set Target Cell: $C$4
Equal to: check "Min"
By Changing Cells: $B$2:$B$14
Add the following constraints,by clicking "Add" each time and entering the
following formulas one at a time and clicking "OK"
$B$2:$B$14=integer
$B$2:$B$14=0
$B$2:$B$14<=5
$C$3=$C$2

After all the four constraints have been added click "Solve".
Excel would perform iterations and arrive at the best possible combination
of bills and coins to achieve the target amount.

Regards,
B. R. Ramachandran




"Esaam" wrote:

Okay...I never thought I would ever have to use excel again. Lo and behold, I
am now the owner of a business and have forgotten how to do some things. I
know how to create formaulas for adding and stuff. I forgot how to make my
calculatons so that my deposit would be broken down. For example say I have 5
of each dollar denomination (1,2,5,10,20,50,100) and coin (1,5,10,25,50,$1).
My deposit has to be 790 (5 of each denomination-150).

What I want my sheet to do is tell me how much of each denomination I should
pull out in terms of numbers instead of dollar amount. For exaple instead of
telling me to pull $790 I want it to tell me to pull 5-$100 bills 5-$50 bills
and 4-10 bills. Basically breaking down my cash with the largest possible
denomination until there can be no more. Any help would be deeply appreciated.

question driving me nuts
 

"Esaam" wrote:
say I have 5 of each dollar denomination (1,2,5,10,20,50,100)
and coin (1,5,10,25,50,$1).
[....]
What I want my sheet to do is tell me how much of each
denomination I should pull out in terms of numbers instead
of dollar amount. For exaple instead of telling me to pull
$790 I want it to tell me to pull 5-$100 bills 5-$50 bills and
4-10 bills. Basically breaking down my cash with the largest
possible denomination until there can be no more.

But 4 $10 bills are not "the largest possible denomination";
2 $20 bills are. I assume you simply made a mistake in
your manual calculations. But if the example is coming
from your teacher, you might want to ask for clarification.

Suppose A1 contains your target dollar amount ($790).
Suppose A2:A8 contains your paper denominations in
decreasing order ($100 to $1) and A9:A14 contains your
coin denominations in decreasing order ($1 to $0.01).
Let B2:B14 contain the number of each denomination.
As a check, I suggest that B15 contain the total amount
represented by B2:B14.

Ostensibly, you might create the following formulas:

B2: =MAX(0, MIN(5, INT(A1/A2)))
B3: =MAX(0, MIN(5, INT(($A$1-
SUMPRODUCT($A$2:OFFSET(A3,-1,0),
$B$2:OFFSET(B3,-1,0)))/A3)))
B4:B14: copy B3
B15: =SUMPRODUCT(A2:A14,B2:B14)

That should earn you at least a C, perhaps a B. It
works for $790. However, if you enter $500.21 (e.g)
into A1, you might see why it is not worth an A. The
number of $0.01 coins is zero(!), and the sum in B15
is only $500.20 :-(.

You will see why if you format B14 with 14 decimal
places and strip the formula of all functions, leaving
only "$A$1-.../A3". Instead of 1.0...0, we find 0.99...909.
INT() correctly truncates that to zero.

This is not an uncommon problem with floating point
arithmetic. In general, I believe there is nothing wrong
with using INT() in these formulas. In fact, I believe
INT() is required.

I believe the correct solution is change the formulas
for coin denominations as follows:

B9: =MAX(0,MIN(5,INT((INT(100*$A$1)-
SUMPRODUCT(INT(100*$A$2:OFFSET(A9,-1,0)),
$B$2:OFFSET(B9,-1,0)))/INT(100*A9))))
B10:B14: copy B9

There is no harm in using the same formula in B2:B8.
I am also tempted to say that the check sum should be:

B15: =SUMPRODUCT(INT(100*A2:A14),B2:B14)/100

But your teacher might object because it limits the total
exact dollar amount to about $45 billion :-).

PS: For an A+, it might be more elegant if B2:B14
displayed a blank instead of zero. That is a simple
embellishment, but it makes things messier because
some part of the formula must be duplicated. Hint:
not the MAX/MIN part.

Explanation of Bx: SUMPRODUCT(...) computes the
sum of Ax*Bx for all pairs in row 2 through the row
above. Thus, $A$1-SUMPRODUCT(...) computes the
remaining amount. MIN(5,...) limits the result to 5
of each denomination. MAX(0,...) displays zero when
SUMPRODUCT(...) computes a negative number
because the denomination exceeds the remaining
amount.

HTH.

question driving me nuts
 

Errata ....

I wrote:
Suppose A1 contains your target dollar amount ($790).
Suppose A2:A8 contains your paper denominations in
decreasing order ($100 to $1) and A9:A14 contains your
coin denominations in decreasing order ($1 to $0.01).
Let B2:B14 contain the number of each denomination.
[....]
I believe the correct solution [for the floating point
"error"] is change the formulas for coin denominations
as follows:

B9: =MAX(0,MIN(5,INT((INT(100*$A$1)-
SUMPRODUCT(INT(100*$A$2:OFFSET(A9,-1,0)),
$B$2:OFFSET(B9,-1,0)))/INT(100*A9))))

Of course, this correction is not needed in B9 ($1 coin).
In fact, it is not needed until B12 (dime). But as I said,
there is no harm in using it elsewhere.