How to determine the combination of stamps?

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

Does anyone have any suggestions on how to determine the combination of stamps?
If the stamp for a letter requires $5.4, which input in cell B1, then I
would like to determine the minimum number of stamps I need for this letter,
and the number of stamps based on the dollar value under column A will show
under column B.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

$0.1 in Cell A2
$0.2 in Cell A3
$0.5 in Cell A4
$1.4 in Cell A5
$1.8 in Cell A6
$2.4 in Cell A7
$3.0 in Cell A8
 
Here is the answer

B8 is simply the value in B1 / B8. Use int to get whole value of division
=INT($B$1/$A$8)
B7 you have to subtract the amount from Row 8 which is A8 * B8
=($B$1-($B$8*$A$8))/$A$7
B6 is the same as B7 but you have to add Row 8 amount and Row 7 amount
=($B$1-(($B$8*$A$8)+($B$7*$A$7)))/$A$6
B5
=($B$1-(($B$8*$A$8)+($B$7*$A$7)+($B$6*$A$6)))/$A$5
B4
=($B$1-(($B$8*$A$8)+($B$7*$A$7)+($B$6*$A$6))+($B$5*$A$5))/$A$4
B3
=($B$1-(($B$8*$A$8)+($B$7*$A$7)+($B$6*$A$6))+($B$5*$A$5)+($B$4*$A$4))/$A$3
B
=($B$1-(($B$8*$A$8)+($B$7*$A$7)+($B$6*$A$6))+($B$5*$A$5)+($B$4*$A$4)+($B$3*$A$3))/$A$2
 
I left the INT() function out of some of the equations and for the B2 cell
you need roundup instead of INT

B8
=INT($B$1/$A$8)
B7
=INT(($B$1-($B$8*$A$8))/$A$7)
B6
=INT(($B$1-(($B$8*$A$8)+($B$7*$A$7)))/$A$6)
B5
=INT(($B$1-(($B$8*$A$8)+($B$7*$A$7)+($B$6*$A$6)))/$A$5)
B4
=INT(($B$1-(($B$8*$A$8)+($B$7*$A$7)+($B$6*$A$6)+($B$5*$A$5)))/$A$4)
B
=INT(($B$1-(($B$8*$A$8)+($B$7*$A$7)+($B$6*$A$6)+($B$5*$A$5)+($B$4*$A$4)))/$A$3)
B
=ROUNDUP(($B$1-(($B$8*$A$8)+($B$7*$A$7)+($B$6*$A$6)+($B$5*$A$5)+($B$4*$A$4)+($B$3*$A$3)))/$A$2,0)
 
The equation simplify if you use sumproduct

B8
=INT($B$1/$A8)
B7
=INT(($B$1-(SUMPRODUCT($B8:$B$8*$A8:$A$8)))/$A7)
B6
=INT(($B$1-(SUMPRODUCT($B7:$B$8*$A7:$A$8)))/$A6)
B5
=INT(($B$1-(SUMPRODUCT($B6:$B$8*$A6:$A$8)))/$A5)
B4
=INT(($B$1-(SUMPRODUCT($B5:$B$8*$A5:$A$8)))/$A4)
B3
=INT(($B$1-(SUMPRODUCT($B4:$B$8*$A4:$A$8)))/$A3)
B2
=ROUNDUP(($B$1-(SUMPRODUCT($B3:$B$8*$A3:$A$8)))/$A2,0)

Note B7-B3 can be copied without an changes. B2 you can copy the other
cells to this location but you have to change INT to Roundup and add to the
Round up the last parameter ,0
 
Back
Top