Denominations with least waste.

[Winehouse]

Hi.

I'm using Numbers on Mac.

I'd like to work out on Numbers, how many different stamp denominations I'll need for a large cost, with the least amount of wastage.

For instance, if a package costs £9.99 to send, and I have 0.85p & 0.66p stamps, the best way is 4x 0.85p & 10x 0.66p. This leaves waste of 1p.

Thank you for your help.

Regards

Winehouse

 

[Eric W]

Interesting question. There are a few options. You could use the Solver to figure that out. There is also an esoteric branch of mathematics called Diophantine Equations that might work. (If I have some time, I might play around with it and see if there's an easy solution for that.) But the fastest way I could figure this out would just to try all possibilities and choose the best. Here's one example:

Book1 (version 1).xlsb
	A	B	C	D	E	F
1	Cost		Stamp1 price	Stamp2 price
2	9.99		0.85	0.66
3
4		# of Stamp1	# of stamp2	Wastage		Remainder after stamp1
5		0	16	0.57		9.99
6		1	14	0.10		9.14
7		2	13	0.29		8.29
8		3	12	0.48		7.44
9		4	10	0.01		6.59
10		5	9	0.20		5.74
11		6	8	0.39		4.89
12		7	7	0.58		4.04
13		8	5	0.11		3.19
14		9	4	0.30		2.34
15		10	3	0.49		1.49
16		11	1	0.02		0.64
17		12	-1	-0.45		-0.21
Sheet24

Cell Formulas
Range	Formula
B5:B17	B5	=SEQUENCE(ROUNDUP(A2/C2,0)+1,,0)
C5:C17	C5	=ROUNDUP(F5#/D2,0)
D5:D17	D5	=C5#*D2-F5#
F5:F17	F5	=A2-C2*B5#
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:D17	Expression	=$D5=AGGREGATE(15,6,$D$5#/($D$5#>=0),1)	text	NO

You did not state what version of Excel you have, so I just assumed the latest one. If you don't have SEQUENCE, I can rewrite these. I might try to rewrite these so that you don't have to see every line, or need the column F helper column. But there might actually be some value in seeing every row. For example, as you said 4 of stamp1 and 10 of stamp2 results in a wastage of .01. But say you don't have 10 of stamp2 available. Then you would see that you can use 11 of stamp1 and only 1 of stamp2, and still only have a wastage of .02.

 

[Joe4]

You did not state what version of Excel you have, so I just assumed the latest one

It does not sound they are using Excel at all. The first line of their question says:

I'm using Numbers on Mac.

 

[Eric W]

It does not sound they are using Excel at all. The first line of their question says:

Hmm. I missed that. Well, I hope that the formulas can be converted easily to whatever Numbers uses. Incidentally, the C5 formula should be

=CEILING(F5#/D2,1)

to avoid the -1 value.

 

[Winehouse]

Thanks, only just seen this. Will have a better look tomorrow.