Does anyone have a formula that can be used in excel to break out currency. I would like to be able to put the amount in the first column and then have excel break out the amount into $20, $10, $5, $1, quarters, dimes, nickels, pennies...in other words, letting me know how many of each denomination I must get from the bank to amount to the total of what is in each column of figures. I have tried several different formulas, but none work unless I change it for each amount entered:( I am going out of town, would appreciate an answer to my email address [email protected]
need to break out currency [SOLVED]



it looks so ugly I cant believe this is right.
It does need fine tuning for numbers which do not have penny values.
but here it is in all its suckieness.
26.51
=A2/20
=(MOD(A4,1)*20)/10
=MOD(A5,1)*10/5
=MOD(A6,1)*5/1
=MOD(A7,1)*1/0.25
=MOD(A8,1)*0.25/0.1
=MOD(A9,1)*0.1/0.05
=MOD(A10,1)*0.05/0.01 
Here's a couple more methods.
From Howard Kittle:
With the amount in B1 
20's =INT(B1/20)
10's =INT(MOD(B1,20)/10)
5's =INT(MOD(B1,10)/5)
1's =INT(MOD(B1,5))
Quarters =INT((ROUND(B1INT(B1),2))/0.25)
Dimes =INT((MOD(ROUND(B1INT(B1),2),0.25))/0.1)
Nickels =INT(ROUND(MOD(MOD(ROUND(B1INT(B1),2),0.25),0.1),2)/0.05)
Pennies =ROUND(MOD(ROUND(B1INT(B1),2),0.05)/0.01,0)Or this one, from Shane Devenshire:
A B C
200 1 388.55
100 1 188.55
50 1 88.55
20 1 38.55
10 1 18.55
5 1 8.55
2 1 3.55
1 1 1.55
0.5 1 0.55
0.2 0 0.05
0.1 0 0.05
0.05 1 0.05
0.02 0 0
0.01 0 0You enter your possible denominations in A1:A14, in B1 you enter the formula
=INT(C1/A1) and copy it down to B14, in C1 you enter the starting value, in
C2 you enter the formula =ROUND(MOD(C1,A1),2) and copy this down to C14.
Column B is the desired result.Cheers,
John

man it irks me when people do not chime back in. To say if it helped or not.

used the method from Howard Kittle and it seems to be working fine!Used this for determining how many of each currency amount needed for distributing bowling league prize money.
Thanks again

Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!