calculating counts of dollar denominations

  • Thread starter Thread starter Turi
  • Start date Start date
T

Turi

I have $100 checks, $50 checks, $25 checks, and $10 checks.

I want to calculate the least amount of checks to give
each person. For example Jane needs $75. I could easily
give her 3 $25 checks, but it would be better to give her
1 $50 check and 1 $25 check since each check costs $ in
addition to it's face value. What calculation would I use?
 
Hi Turi!

With the amount in A1. Try the following

B1
=INT(A1/100)
returns number of 100s needed
C1
=INT(MOD(A1,100)/50)
returns number of 50s
D1
=INT(MOD(A1,50)/20)
returns number of 20s
E1
=INT(MOD(A1,50)/10-2*D1)

With all your paycheck requirements in column A (or as appropriate),
the setting up in this way allows vertical summing of the note
requirements in the separate column so that you know what to get from
the bank. You can use the same principles to get down to smaller
denominations if you want.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Friday 18th July: Mexico (Day of Mourning
death of Benito Juarez), Spain (Labor Day), Uruguay (Constitution Day)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
With the amount in A1. Try the following

B1
=INT(A1/100)
returns number of 100s needed
C1
=INT(MOD(A1,100)/50)
returns number of 50s
D1
=INT(MOD(A1,50)/20)
returns number of 20s
E1
=INT(MOD(A1,50)/10-2*D1)
Click to expand...
...

If only life were simple enough to allow us to change the specs!

If the D1 denomination were 20, then the E1 formula could be

=INT(MOD(A1,20)/10)

But it's not. The D1 formula needs to use 25 rather than 20, and that leads to
fun-filled complications, e.g., A1 = $30. The numbers of $50s and $25s are 0 or
1 only, and your approach needs some way of dealing with amounts that can't be
represented by the available check denominations, e.g., $13.
 
... ...
..

If only life were simple enough to allow us to change the specs!

If the D1 denomination were 20, then the E1 formula could be

=INT(MOD(A1,20)/10)
Click to expand...

WRONG! But it could be

=INT(MOD(MOD(A1,50),20)/10)

which is still a savings of one '-' op.
 
Harlan Grove wrote:
...=INT(MOD(MOD(A1,50),20)/10)...
Click to expand...
i used this method to count currency breaking the denominations down to $20s, $10s, $5s, $1s, $0.25s, $0.10s, $0.05s, and $0.01s.

it doesn't like the nickels or pennies. it miss counts them. here's what a snippet of my formulae looks like: =INT(MOD(MOD(MOD(A1,0.25),0.1),0.5)/0.01)

i've tried several variations with more or less mod commands. it miss counts them differently, but still never gives the right answer.
 
Back
Top