Excel Calculator..

[shawn]

How would I go about this and is it doable?

I want to make a calculator that will determine the number of stamps needed
to get postage needed.

Column A will contain the header "Stamp Value". Below it will be every stamp
denomination currently available, ie: .42, .17, .27, .01, .03, etc.I'll
input these and change them as necessary.

Column B will have check boxes that you can check off what stamps you have..
for example in our office I have $1.00 stamps, .42 cents, .27 cents, and .17
cents only.

One of the cells on the sheet you will input how much postage you need..
for example I needed $2.02 to mail out some catalogs to a customer today.

Column D will show you how many of each stamp you need to put on the
envelope, the best combination to reach $2.02. If it cannot reach $2.02, it
will try to combine stamps to give you the next highest amount closest to
$2.02.. so if it can use 4 stamps and reach $2.05 then it will show you
that.

 

[shawn]

If it's not easy to explain how to do, I'd pay to have it made, obviously
not a lot money since it shouldn't be too hard of a job and it would be for
my office, although coming out of my pocket.

 

[Shane Devenshire]

Hi,

Although you might be able to do this with regular spreadsheet function,
most likely you would want to use Solver. I'm at work so I can't spend the
time. But you shouldn't have to pay for it since its a relatively straight
forward problem. Keep in mind there are many stamp combinations to reach
2.02 for example 202 1cent stamps or a $2 stamp and 2 1 cent stamps. (I think
they still make $2 stamp?)

 

[Roger Govier]

Hi Shawn

this should get you started.

In A1 enter Stamp Value, in B1 enter 0 then in C1 onward enter the value of
the different stamps that are available
In A2 Enter Postage Required
In A3 enter the Value e.g. 2.02, and in B3 enter 0
In C3 enter
=IF(ROUND($A3-SUMPRODUCT($B3:B3,$B$1:B$1),2)>=C$1,
INT(ROUND($A3-SUMPRODUCT($B3:B3,$B$1:B$1),2)/C$1),0)

Copy across through D3:??3 i.e. to the last column with a value in row 1
Copy the whole row B3:??3 down as far as required

Enter all of your postage values down column A, and you can sum the various
columns to see how many stamps of each denomination are required.

 

[shawn]

Right. Many combinations. This program should automatically figure out the
best combination. It will say hey I can use 2 $1.00 stamps or 10 .20 cents
stamps. So after it calculates the available options it would then need to
look and see which uses less stamps to come as close $2.02 as possible.

I wouldn't even mind if this is an HTML file or an EXE. I just figured I'd
post here since I'm already here and Excel is fine with me.

 

[Roger Govier]

I posted a solution to you 10 hours ago

 

[Dana DeLouis]

currently available, ie: .42, .17, .27, .01, .03
As a fun side note from combination theory...
If you had selected your 5 variables...
..42, .17, .27, .01, .03

There are 2617 ways of arriving at 2.02 from these 5.

Just trivia..:>)
Dana DeLouis

 

[Dana DeLouis]

Right. Many combinations. This program should automatically figure
out the best combination.

I have $1.00 stamps, .42 cents, .27 cents, and .17
for example I needed $2.02

I find the following interesting. Since your 4 numbers are relatively
prime...

? FrobeniusNumber(100, 42, 27, 17)

175

Meaning that you can not make 1.75 from the above stamps.
However, there are solutions for all totals greater than 1.75

http://en.wikipedia.org/wiki/Frobenius_number

Dana DeLouis

<snip>