Split a dollar figure into the minimum pieces of currency for refund

I have a number createded through summing that is a dollar figure. Such as $123.12.
I need to have excel tell me the minimal combination of bills and change needed to refund a customer.

In the example number:
$123.12
a one hundred dollar bill
a twenty dollar bill
a dime
and two pennies

Any assistance would be much appreciated.
Thanks!

It's also possible to use Solver.

Alf

dwest100 actually posted this question for me before i joined the forums, i've tried variations on most of these to suit my existing spreadsheet. all if them seem to work well until it comes to nickles and pennies. there are a couple suggestions in here that might fix my problem but i'd like to show you all what i currently use. to clarify, i don't use any denomination over $20. aside from that i want to use the largest possible denominations (for instance .41 would be 1 quarter, 1 dime, 1 nickle, 1 penny) so, where $H8 equals the total to be divvied-up...

=INT($H8/20) (for 20s)
=INT(MOD($H8,20)/10) (10s)
=INT(MOD($H8,10)/5) (5s)
=INT(MOD($H8,5)/1) (1s)
=INT(MOD($H8,1)/0.25) (qtrs)
=INT(MOD($H8,0.25)/0.1) (dimes)
=INT(MOD(MOD($H8,0.25),0.1)/0.05) (nickles)
=INT(MOD(MOD(MOD($H8,0.25),0.1),0.05)/0.01) (pennies)

at the bottom of the spreadsheet these numbers are tallied with others in their perspective columns so that i know exactly how much of each denomination to get to pay each individual. as stated above these formulas do no work for nickles and pennies, neither do any others i have tried. each method i've tried returns different kinds of errors, such as miscounts or even negative integers, and some will work for one situation, but not another (for instance i might be able to adjust the formulas to work perfectly when there are no nickles involved, but if there are nickels it messes things up, it counts as 5 pennies and no nickles or 1 nickle AND 5 pennies to cover the same $0.05). it's very strange, and discouraging.

it just got weirder. i left all of the formulas the same as above except for pennies which now looks like this..

=MOD(MOD(MOD($H8,0.25),0.1),0.05)/0.01

basically i just removed the interger function because i'm dividing by 1. this works for anything now, except if the total in H8 (wich is, in itself a sum) is over $40.00. it then returns five pennies and no nickles where a nickle would be used. WHAT IN SAM HELL DOES THIS MEAN?!?!

Please see if my solution match your requirement or not? However to read it in word you require use VBA code but you can copy every thing from module 1 and use formula in worksheet "Spellnumber" (please see my attached file)..

821337-split-a-dollar.xlsx

please see my sample above. it's very weird.

sorry not clear, but i really don't understand why do you need this module fore?

we have to reimberse some employees in cash for certain purchases and it must be exact change. there are several people that need to be paid at the same time and we need to know what denominations to get from the bank for each person.

excuse me, what is nickle? is it currency unit? I am trying to observe your problem.

A nickel is 5 cents in the USA. A dime is 10 cents, quarter is 25 cents

Oh! but in your table i see that in H8 there are 2 for 20, 1 for 10, 1 for 0.25, 1 for .10, 1 for .05, and 1 for .01 so if come to your above problem there is nickle which is equal 1? do i have mistake in observation or not?

also, a penny is 1 cent. a cent is 1/100th of a dollar.

Originally Posted by excontent

Oh! but in your table i see that in H8 there are 2 for 20, 1 for 10, 1 for 0.25, 1 for .10, 1 for .05, and 1 for .01 so if come to your above problem there is nickle which is equal 1? do i have mistake in observation or not?

you observe correctly. but if you look, when there should be a nickle, but no pennies, and the total is over $40, it shows 5 pennies and no nickle. if their are nickles AND pennies, it counts it all correctly. the amount of money is the same and it's not that big of a deal, i guess. the employee still gets their money. but it's a strange issue that shouldn't be occuring. it especially shouldn't be occuring only under very particular circumstances, if it occurs at all, it should do it all the time.

after a little more exprimentation the problem actually starts at $31.90. soooo weeeiird!

Ok i found the problem please try this formula in h8: =INT(ROUNDUP(MOD(MOD($A8,0.25),0.1),4)/0.05) and in I8: =MOD(ROUNDUP(MOD(MOD($A8,0.25),0.1),4),0.05)/0.01 The problem was because very small different behind decimal (ex. 0.009999999999999999) so we have to fix it up with rounding. It also work with 31.90, i think.

that didn't work for me, but i see where you're going with it and i'll tinker around with the roundup function for a while. i'll let you know if i get it to work.

Ok please check my attached file: Copy of denominator sample-2.xlsxCopy of denominator sample-2.xlsx

HI there,

I'm trying to adapt the formula's from mahju’s “Dollar into Change” example for Australian denominations.
$100 ,$50 , $20, $10 , $5, $2, $1, 50c, 20c, 10c and 5c

So far I have got through 100's to $1 and it works, but as soon as i try to adapt the formula for 50 cents and below it's going awry:


(where C14 is the cell I would use to get the denominations)


100’s(Cell C3) =INT($C$14/100)
50’s (cell C4) =INT(($C$14-C3*100)/50)
20’s (cell C5) =INT(($C$14-C3*100-C4*50)/20)
10’s (cell C6) =INT((INT($C$14-C3*100-C4*50-C5*20))/10)
5’s (cell C7) =INT((INT($C$14-C$3*100-C$4*50-C$5*20-C$6*10))/5)
2’s cell (C8) =INT((INT($C$14-C$3*100-C$4*50-C$5*20-C$6*10-C$7*5))/2)
1’s (cell C9) =INT((INT($C$14-C$3*100-C$4*50-C$5*20-C$6*10-C$7*5-C$8*2))/1) - Works down to here.
0.5’s - nothing I have tried has worked yet?
0.2’s
0.1’s

Any help on this would be great.