Formula or Macro to calculate No of Stamps

[AliCat]

I have arranged a table in excel which has the numbers 1 to 20 in column "B" starting in "B3". In "C3" to "H3" I've entered the value of some postage stamps say 65p, 45p, 25p, 5p, 2p & 1p. I've completed the table by means of formulas to tell me the value of multiple stamps by reference to the quantities shown in column "B". This allows me to look at the table and calculate the number of stamps I need to put on various packages.

I would like to take this a step further to get some automation in my calculation. I would like say, to enter the value of the postage in "B1" and have some formula or function or possibly a macro calculate and the number of the different values of stamps and enter these quantities in cells "C1" to "H1" respectively.

Can any one help.

Thanks, Alison.

 

[Phil Squod]

Enter in cells C1:H1 the numbers 65,45,25,5,2,1

Enter your postage value in B2 (enter in pounds, e.g. 0.76 representing 76p)

Enter in in C2 the formula =INT($B2*100/$C$1)

Enter in in D2 the formula =INT((($B2*100)-SUMPRODUCT(($C2:C2)*($C$1:C$1)))/D$1)

Drag the D2 formula across to H2

The formulas in C2:H2 can be dragged down

 

[white6174]

try this
hope it helps

steve w

lastcell.xls
	A	B	C	D	E	F	G	H	I
1		5.83	8	1	0	3	1	1
2	hidethisrow		0.63	0.18	0.18	0.03	0.01	0
3			0.65	0.45	0.25	0.05	0.02	0.01
4
5	thisisjustadoublecheck		5.2	0.45	0	0.15	0.02	0.01	5.83
6
7
8
9
10
Sheet2