need to break out currency [SOLVED]

  • 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, 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 e-mail address [email protected]

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



  • 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(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)

    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 0

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



  • :thumbup:
    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!