Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the combination of stamps?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the combination of stamps?
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 B2 =($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 "Eric" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the combination of stamps?
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) B3 =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) B2 =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) "Eric" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the combination of stamps?
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 "Joel" wrote: 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) B3 =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) B2 =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) "Eric" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time Stamps??????????? | Excel Discussion (Misc queries) | |||
Sorting data with time stamps | Excel Discussion (Misc queries) | |||
I need to find the yy/mm/dd AND hh:mm:ss between 2 time stamps | Excel Discussion (Misc queries) | |||
multiple time stamps on worksheets | Excel Discussion (Misc queries) | |||
DIFFERENCE BETWEEN TWO TIME STAMPS AS DECIMAL | Excel Discussion (Misc queries) |