is there an easy way to figure out which dollars and coins will be used to add up to a total owed

[jvoortman]

is there an easy way to figure out which dollars and coins will be used to add up to a total owed? ihave a little dart league where players earn small cash for each win, at the seasons end I pay out but it takes time to calculate bills and coins needed, so I can go to the bank and get correct change. i'm in Canada so we have $100's, 50's, 20's, 10's, 5's, Toonies, Loonies, quarters, dimes and nickels . for example a guy is getting $89.64, so I will need 1-$50, 1-$20, 1-$10, 1-$5, 2-toonies, 1-quarter, 1-dime, 1-nickel (rounding up if necessary since we no longer have pennies. I used to just buy rolls of change but that isn't great cuz I 'll have too much left over and so I am trying to see if you folks have a solution for me. The amounts just go straight down a column and it varies how many player from season to season. Any help would be greatly appreciated ! John

TONY	$300.75​
SAM	$203.50​
ALEX	$142.60​
ANDY	$115.11​
DAVE	$89.63​
JAY	$60.37​
JR	$35.48​
BILL	$34.82​
LES	$31.35​
CHRIS	$30.16​
JARVIS	$28.02​
IVAN	$32.76​
MAX	$26.86​
TERRY	$26.57​
DEAN	$27.52​
JOHN	$19.73​
SEAN	$19.56​
KEN	$12.27​
ERIK	$7.20​
KEINO	$6.30​

 

[jvoortman]

names run down column A while dollar amounts run down column B

 

[jvoortman]

maybe column C can show fifties needed, column D can show twenties needed, column E can show tens needed, column F can show fives needed, column G can show toonies need, column H can show loonies needed, column I can show quarters needed, column J can show dimes needed, and column K can show nickels needed, and then I can just sum up each column to get exact change needed

 

[jvoortman]

for non canadians the toonie is like a two dollar bill and a loonie is like a one dollar bill,

 

[Dave Patton]

Edit the formula for the rounding in Column M if necessary.

T202206a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1				Denomination					Allocate distribute amount by currency
2		Amount	0	100	50	20	10	5	2	1	0.25	0.1	0.05
3		300.75	0	3	0	0	0	0	0	0	3	0	0	300.75
4		203.50	0	2	0	0	0	0	1	1	2	0	0	203.50
5		142.60	0	1	0	2	0	0	1	0	2	1	0	142.60
6		115.11	0	1	0	0	1	1	0	0	0	1	1	115.15
7		89.63	0	0	1	1	1	1	2	0	2	1	1	89.65
8		60.37	0	0	1	0	1	0	0	0	1	1	1	60.40
9		0.57	0	0	0	0	0	0	0	0	2	0	2	0.60
3b

Cell Formulas
Range	Formula
D3:L9	D3	=INT(ROUND($B3-SUMPRODUCT($C3:C3,$C$2:C$2),2)/D$2)
M3:M9	M3	=ROUNDUP(($B3-SUMPRODUCT($C3:L3,$C$2:L$2))/M$2,0)
N3:N9	N3	=SUMPRODUCT($D$2:$M$2,D3:M3)

 

[Dave Patton]

T202206a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		Amount	$100	$50	$20	$10	$5	$2	$1	$0.25	$0.10	$0.05
3		1,450.20	14	1	0	0	0	0	0	0	2	0	1,450.20
4		250.20	2	1	0	0	0	0	0	0	2	0	250.20
5		1,180.25	11	1	1	1	0	0	0	1	0	0	1,180.25
6		650.75	6	1	0	0	0	0	0	3	0	0	650.75
7		985.60	9	1	1	1	1	0	0	2	1	0	985.60
8		450.20	4	1	0	0	0	0	0	0	2	0	450.20
9		53.33	0	1	0	0	0	1	1	1	0	2	53.35
10		5,020.53	46	7	2	2	1	1	1	7	7	2
3c

Cell Formulas
Range	Formula
C3:C9	C3	=INT($B3/C$2)
D9:K9,D3:L8	D3	=INT(ROUND($B3-SUMPRODUCT($C3:C3,$C$2:C$2),2)/D$2)
M3:M9	M3	=SUMPRODUCT($C$2:$L$2,C3:L3)
L9	L9	=CEILING(($B9-SUMPRODUCT($C9:K9,$C$2:K$2))/L$2,1)
B10:L10	B10	=SUM(B3:B9)

 

[Dave Patton]

T202206a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		Amount	$100	$50	$20	$10	$5	$2	$1	$0.25	$0.10	$0.05
3		89.64	0	1	1	1	1	2	0	2	1	1	89.65
4		250.20	2	1	0	0	0	0	0	0	2	0	250.20
5		1,180.25	11	1	1	1	0	0	0	1	0	0	1,180.25
6		650.75	6	1	0	0	0	0	0	3	0	0	650.75
7		985.60	9	1	1	1	1	0	0	2	1	0	985.60
8		450.20	4	1	0	0	0	0	0	0	2	0	450.20
9		53.33	0	1	0	0	0	1	1	1	0	2	53.35
10		3,659.97	32	7	3	3	2	3	1	9	6	3	3,660.00
3c

Cell Formulas
Range	Formula
C3:C9	C3	=INT($B3/C$2)
D3:K9	D3	=INT(ROUND($B3-SUMPRODUCT($C3:C3,$C$2:C$2),2)/D$2)
L3:L9	L3	=CEILING(($B3-SUMPRODUCT($C3:K3,$C$2:K$2))/L$2,1)
M3:M9	M3	=SUMPRODUCT($C$2:$L$2,C3:L3)
B10:M10	B10	=SUM(B3:B9)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Dave Patton]

This version requires a current version of Excel.
Use Round or Ceiling in Column L as required for your application.

T202206a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1								Allocate distribute amount by currency				Round
2													Total
3	Amount		100	50	20	10	5	2	1	0.25	0.1	0.05		difference
4	12,000.50		120							2			12,000.50
5	1,152.51		11	1				1		2			1,152.50	-0.01
6	6.00						1		1				6.00
7	777.00		7	1	1		1	1					777.00
8	625.00		6		1		1						625.00
9	4,827.00		48		1		1	1					4,827.00
10	1,306.00		13				1		1				1,306.00
11	1,307.00		13				1	1					1,307.00
12	5.00						1						5.00
13	2,000.00		20										2,000.00
14	53.33			1				1	1	1		2	53.35	0.02
15	250.20		2	1							1	2	250.20
16	24,309.54		240	4	3		7	5	3	5	1	4	24,309.55	0.01
3ccc

Cell Formulas
Range	Formula
C4:K15	C4	=INT(($A4-SUM($B4:B4*$B$3:B$3))/C$3)
L4:L15	L4	=ROUND(($A4-SUM($B4:K4*$B$3:K$3))/L$3,0)
M4:M15	M4	=SUM($B$3:$L$3*B4:L4)
N4:N15	N4	=M4-A4
A16,C16:N16	A16	=SUM(A4:A15)
B16	B16	=SUM(B3:B15)