convert amount to denominations

[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.

 

[Bob Phillips]

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)

 

[Ken Wright]

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

 

[Bob Phillips]

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

 

[Ken Wright]

LOL - Quite agree - I'd take the 10s every time.

 

[Suzanne Mead]

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)

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?

 

[Paul Sardella]

Following the logic,

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

HTH
Paul
----------------------------------------

 

[Suzanne Mead]

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.

 

[Suzanne Mead]

I have tried both formulas & 1 end up with 1 too many or 1 too few

tens. The 100 , fifty & twenty columns work fine.

 

[Suzanne Mead]

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

 

[Peo Sjoblom]

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]

Take a look at Peo's solution and give that a go

 

[Suzanne Mead]

Take a look at Peo's solution and give that a go

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