convert amount to denominations

  • Thread starter Thread starter Suzanne Mead
  • Start date Start date
S

Suzanne Mead

I need to be able to enter an amount of money such as $468.25 and
have it be broken down into denominations in separate columns of
100's, 50's, 20's, 5's, 1's, .25's. The purpose is to figure cash
needed to cash paychecks.
 
Suzanne,

for the cents, use
=MOD(A1,1)

for the units,
=INT(MOD(A1,10))

for the tens,
=INT(MOD(A1,10)/1)

for the hundreds
=INT(MOD(A1,1000)/100)

for the thousands
=INT(MOD(A1,10000)/1000)

etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
With your amount in B1

100's =INT(B1/100)
50's =INT(MOD(B1,100)/50)
20's =INT(MOD(B1,50)/20)
10's =INT(MOD(B1,20)/10)
5's =INT(MOD(B1,10)/5)
1's =INT(MOD(B1,5))
Quarters =INT((ROUND(B1-INT(B1),2))/0.25)
Dimes =INT((MOD(ROUND(B1-INT(B1),2),0.25))/0.1)
Nickels =INT(ROUND(MOD(MOD(ROUND(B1-INT(B1),2),0.25),0.1),2)/0.05)
Pennies =ROUND(MOD(ROUND(B1-INT(B1),2),0.05)/0.01,0)

Found in an earlier post via Google and amended to include 50s and 100s
 
Ken,

I like my solution better than yours. Pity it answers another question eh
<vbg>?

I guess I couldn't imagine why anyone would want the 50'2 and 20's rather
than the tens etc., so I didn't! Time for bed I suppose.

Bob
 
Bob Phillips said:
Suzanne,

for the cents, use
=MOD(A1,1)

for the units,
=INT(MOD(A1,10))

for the tens,
=INT(MOD(A1,10)/1)

for the hundreds
=INT(MOD(A1,1000)/100)

for the thousands
=INT(MOD(A1,10000)/1000)

etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
10/01 I thank you for the help. Everything works except the 10's. It
comes up with zero each time. I tried =int(mod(a1,10)/1) as you
suggested and I tried =int(mod(b1,20)/10 as Ken Wright suggested. Of
course I substituted the correct cell, rather than A1 or B1. Mine is
A2. What now?
 
Following the logic,

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

HTH
Paul
----------------------------------------
 
REPLY -Paul - I tried this & I get the number of tens including those
inclued in the fifties column. i.e $468.00 gives me 1 fifty & 6 tens.
 
I have tried the int & mod functions as directed. They work for
100's, 50', 20'. When I get to 10's this no longer works properly.
The number of 10's is either 1 too many or 1 too few. Suzanne
 
Maybe

for 50s

=INT(MOD(A1,100)/50)

for 10s

=FLOOR(MOD(A1,50)/10,1)

for 5s

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

for 1s

FLOOR(MOD(A1,5),1)

and for cents

=MOD(A1,1)*100
 
Ken Wright said:
Take a look at Peo's solution and give that a go
Click to expand...


10/07/03 Peo's solution worked great. I only needed to change the
formula for the 10's to get it working. Thanks to all who tried.
Suzanne
 
Here is what I used. This Assumes the following:
C4 = Amount being converted
D4 = 100's
E4= 50's
F4 = 20's
G4 = 10's
H4 = 5's
I4 = 1's
J4 = Quarters or .25
K4 = Dimes or .1
L4 = Nickels or .05
M4 = Pennies or .01
_____________________________
100's =INT(C4/100)
50's =INT((C4-(D4*100))/50)
20's =INT((C4-((D4*100)+(E4*50)))/20)
10's =INT((C4-((D4*100)+(E4*50)+(F4*20)))/10)
5's =INT((C4-((D4*100)+(E4*50)+(F4*20)+(G4*10)))/5)
1's =INT((C4-((D4*100)+(E4*50)+(F4*20)+(G4*10)+(H4*5)))/1)
.25's =INT((C4-((D4*100)+(E4*50)+(F4*20)+(G4*10)+(H4*5)+(I4*1)))/.25)
.10's =INT((C4-((D4*100)+(E4*50)+(F4*20)+(G4*10)+(H4*5)+(I4*1)+ (J4*0.25)))/0.1)
.05's =INT((C4-((D4*100)+(E4*50)+(F4*20)+(G4*10)+(H4*5)+(I4*1)+(J4*0.25)+(K4*0.1)))/0.05)
.01's =((C4-((D4*100)+(E4*50)+(F4*20)+(G4*10)+(H4*5)+(I4*1)+(J4*0.25)+(K4*0.1)+(L4*0.05)))/0.01)

I had a little trouble with the pennies but as soon as I removed the INT it
calculates correctly..Try this
 
Back
Top