Stamps

C

Cam

Hi,

I saw how to do this once but..... can I remember :( ... can anyone
help?

The problem is a simple/common one.

I have a list of stamp values, say 1,2,4,8,10,20,35

I have a postage rate for a 100g letter of say 134.

How do I get Excel to work out how many of which stamp to use?

Not even sure where to start.....

Thanks, Cam :)
 
W

wouter.magre

For A1:"Stock"
For B1 "Stampvalues"
For C1: "Needed"
For D1: "Use"
For E1: "Required"
For F1: "Control"
For B3 upto B9: 35, 20, 10, 8, 4, 2, 1
For A3 upto A9: ythe number of stamp currently in stock
For E2 the required postage ie: 135
For F2: =SUM(F3:F9)
For C3: =INT(E2/B3)
For D3: =MIN(A3:C3)
For E3: =E2-D3*B3
For F3: =B3*D3

Copy the range C3:F3 down unto row 9.

You cab mark column A and D for easy use,

Do not forget to change the figures in column A after using same stamps
or obtaining new ones!

You can use Conditional formating for Cel E2 and F2 to warn teh user in
the case that the required postage can not be reached withthe stamps in
stock.


HTH

Wouter Magre
 
D

Don Guillett

Send me a request to my email below and I will send you one I wrote some
time ago.
I will NOT respond to requests made on the ng as this wastes the tim of
others.
 
C

Cam

Hi Wouter & Don,

Firstly, thank you both for your great help - I'm learning lots :) .

Having got this bit working I am keen to be able to put in my letter
weight and have it 'look up' the cost associated.

I have created a list on *sheet2* A2-A17 are the weights in grams.....
so 60, 100, 150, 200 etc - in B2-B17 are the associated costs so 30,
46, 64, 79 etc. Now how would I get excel to get the right postage
cost for an actual letter weight..... so say my letter is 184g - the
answer would need to be 64p ie letter weight of between 150g and 199g.

Cheers, Cam :)
 
C

Cam

Hi again,

I have been happily using the stamp calculator the kind folks here
helped me make :) The spreadsheet goes down the list extracting
values of stamps until it reaches the desired value. I am wondering if
it is possible to optimise this so that as few stamps as possible are
used?

For example say I need a postage value of £1.00 and I have the
following stamps £0.60, £0.50 & £0.05. At the moment the answer would
use 9 stamps - 1 x £0.60 & 8 x £0.05 - the desired result would be 2
stamps ie 2 x £0.50....... any ideas?

Thanks, Cam
 
D

Don Guillett

I have such an animal that I can send ONLY if you request off line to my
private mail.
 
C

Cam

Hi Don,

Would it be possible for you to take us through it here in the forum so
that we can all learn?

Thanks Cam
 
D

Don Guillett

Takes a while to explain but it uses INT & MOD
http://www.usps.gov/
.39 1st CLASS 1st OUNCE
.24 ADDITIIONAL OUNCE

4 Ounces
$ 1.11 TOTAL
USING ALL STAMPS
0.39 2 0.78 1.11
0.24 1 0.24 0.33
0.01 9 0.09
USING 39c & 1c ONLY
0.39 2 0.78 1.11
0.01 33 0.33
USING 24c & 1c ONLY
0.24 4 0.96 1.11
0.01 15 0.15
USING 39c FIRST & 24c & 1c
0.39 1 0.39 1.11
0.24 3 0.72 0.72
0.01 0 0.00


0.39 1st CLASS 1st OUNCE
0.24 ADDITIIONAL OUNCE

4 Ounces
=C2+(C5-1)*C3 TOTAL
USING ALL STAMPS
=C2 =INT(F8/C8) =C8*D8 =C6
=C3 =INT(F9/C9) =C9*D9 =ROUND(MOD(F8,C8),2)
0.01 =INT(F10/C10) =ROUND(MOD(F9,C9),2)
="USING "&$C$2*100& "c & 1c ONLY"
=C2 =INT(F12/C12) =C12*D12 =C6
0.01 =INT(F13/C13) =ROUND(MOD(F12,C12),2)
="USING "&$C$3*100& "c & 1c ONLY"
=C3 =INT(F15/C15) =C15*D15 =$C$6
0.01 =INT(F16/C16) =ROUND(MOD(F15,C15),2)
="USING "&C2*100&"c FIRST & "&C3*100&"c & 1c"
=C2 1 =C18*D18 =F8
=C3 =INT(F19/C19) =C19*D19 =$C$6-C2
0.01 =INT(F20/C20) =ROUND(MOD(F19,C19),2)
 
C

Cam

Hi Don,

Thanks for that.... a bit lost..... any chance you could present a
simple example:

A B
C
stamps availaible Stamp value required number of each stamp
required
1 0.60 1.00
0
2 0.50
2
3 0.20
0
4 0.05
0

What would the formula behind C1, C2, C3 & C4 be?

Cheers, Cam
 
Top