LinkBack URL
About LinkBacks
How Calculate range Min Number & Max Number (Optical)
Dear All,
I want ask .. can Excel calculate number range -10.00 - +9.00 = pricelist, if -15.00 - +8.00 = Outpricelist (add cost).
My example file in attachment.
Thanks - John
========================================================================= SHEET1 (my pricelist) ========================================================================= A | B | C | D | E | F | G | H | I 1 | PRODUCT | SPHMIN | SPHMAX | CYLMIN | CYLMAX | ADD | ADDMAX | COST |ADDCOST (ADD COST if out range in cell B2 or C2 or D2) ----------------------------------------------------------------------------------------------------------- 2 | PRODUCT A | -10.00 | 10.00 | -10.00 | 10.00 | 0.00 | 10.00 | $10 | $1 3 | PRODUCT B | -15.00 | 15.00 | -15.00 | 15.00 | 0.00 | 15.00 | $50 | $2 ----------------------------------------------------------------------------------------------------------- ========================================================================= SHEET2 (my invoice) ========================================================================= A | B | C | D | E | F 1 | PRODUCT | SPH | CYL | ADD | QTY | COST -------------------------------------------------------------------- 2 | PRODUCT A | -10.00 | 10.00 | 10.00 | 1 | $10 3 | PRODUCT A | -11.00 | 10.00 | 10.00 | 1 | $11 ===> Out Range addcost $1 4 | PRODUCT B | -15.00 | 15.00 | 15.00 | 1 | $50 5 | PRODUCT B | -15.50 | 15.50 | 10.00 | 2 | $52 ===> Out Range addcost $2 --------------------------------------------------------------------
OR
=================================================================================== SHEET 3 (Pricelist 2 revisi) =================================================================================== PRODUCT A | PRODUCT B SPH CYL ADD | SPH CYL ADD -10.00 -10.00 0.00 | -15.00 -15.00 0.00 -9.75 -9.75 0.25 | -14.75 -14.75 0.25 -9.50 -9.50 0.50 | -14.50 -14.50 0.50 -9.25 -9.25 0.75 | -14.25 -14.25 0.75 -9.00 -9.00 1.00 | -14.00 -14.00 1.00 -8.75 -8.75 1.25 | -13.75 -13.75 1.25 -8.50 -8.50 1.50 | -13.50 -13.50 1.50 -8.25 -8.25 1.75 | -13.25 -13.25 1.75 -8.00 -8.00 2.00 | -13.00 -13.00 2.00 -7.75 -7.75 2.25 | -12.75 -12.75 2.25 -7.50 -7.50 2.50 | -12.50 -12.50 2.50 -7.25 -7.25 2.75 | -12.25 -12.25 2.75 -7.00 -7.00 3.00 | -12.00 -12.00 3.00 -6.75 -6.75 3.25 | -11.75 -11.75 3.25 -6.50 -6.50 3.50 | -11.50 -11.50 3.50 -6.25 -6.25 3.75 | -11.25 -11.25 3.75 -6.00 -6.00 4.00 | -11.00 -11.00 4.00 -5.75 -5.75 4.25 | -10.75 -10.75 4.25 -5.50 -5.50 4.50 | -10.50 -10.50 4.50 -5.25 -5.25 4.75 | -10.25 -10.25 4.75 -5.00 -5.00 5.00 | -10.00 -10.00 5.00 -4.75 -4.75 5.25 | -9.75 -9.75 5.25 -4.50 -4.50 5.50 | -9.50 -9.50 5.50 -4.25 -4.25 5.75 | -9.25 -9.25 5.75 -4.00 -4.00 6.00 | -9.00 -9.00 6.00 -3.75 -3.75 6.25 | -8.75 -8.75 6.25 -3.50 -3.50 6.50 | -8.50 -8.50 6.50 -3.25 -3.25 6.75 | -8.25 -8.25 6.75 -3.00 -3.00 7.00 | -8.00 -8.00 7.00 -2.75 -2.75 7.25 | -7.75 -7.75 7.25 -2.50 -2.50 7.50 | -7.50 -7.50 7.50 -2.25 -2.25 7.75 | -7.25 -7.25 7.75 -2.00 -2.00 8.00 | -7.00 -7.00 8.00 -1.75 -1.75 8.25 | -6.75 -6.75 8.25 -1.50 -1.50 8.50 | -6.50 -6.50 8.50 -1.25 -1.25 8.75 | -6.25 -6.25 8.75 -1.00 -1.00 9.00 | -6.00 -6.00 9.00 -0.75 -0.75 9.25 | -5.75 -5.75 9.25 -0.50 -0.50 9.50 | -5.50 -5.50 9.50 -0.25 -0.25 9.75 | -5.25 -5.25 9.75 0.00 0.00 10.00 | -5.00 -5.00 10.00 +0.25 +0.25 | -4.75 -4.75 10.25 +0.50 +0.50 | -4.50 -4.50 10.50 +0.75 +0.75 | -4.25 -4.25 10.75 +1.00 +1.00 | -4.00 -4.00 11.00 +1.25 +1.25 | -3.75 -3.75 11.25 +1.50 +1.50 | -3.50 -3.50 11.50 +1.75 +1.75 | -3.25 -3.25 11.75 +2.00 +2.00 | -3.00 -3.00 12.00 +2.25 +2.25 | -2.75 -2.75 12.25 +2.50 +2.50 | -2.50 -2.50 12.50 +2.75 +2.75 | -2.25 -2.25 12.75 +3.00 +3.00 | -2.00 -2.00 13.00 +3.25 +3.25 | -1.75 -1.75 13.25 +3.50 +3.50 | -1.50 -1.50 13.50 +3.75 +3.75 | -1.25 -1.25 13.75 +4.00 +4.00 | -1.00 -1.00 14.00 +4.25 +4.25 | -0.75 -0.75 14.25 +4.50 +4.50 | -0.50 -0.50 14.50 +4.75 +4.75 | -0.25 -0.25 14.75 +5.00 +5.00 | 0.00 0.00 15.00 +5.25 +5.25 | +0.25 +0.25 +5.50 +5.50 | +0.50 +0.50 +5.75 +5.75 | +0.75 +0.75 +6.00 +6.00 | +1.00 +1.00 +6.25 +6.25 | +1.25 +1.25 +6.50 +6.50 | +1.50 +1.50 +6.75 +6.75 | +1.75 +1.75 +7.00 +7.00 | +2.00 +2.00 +7.25 +7.25 | +2.25 +2.25 +7.50 +7.50 | +2.50 +2.50 +7.75 +7.75 | +2.75 +2.75 +8.00 +8.00 | +3.00 +3.00 +8.25 +8.25 | +3.25 +3.25 +8.50 +8.50 | +3.50 +3.50 +8.75 +8.75 | +3.75 +3.75 +9.00 +9.00 | +4.00 +4.00 +9.25 +9.25 | +4.25 +4.25 +9.50 +9.50 | +4.50 +4.50 +9.75 +9.75 | +4.75 +4.75 +10.00 +10.00 | +5.00 +5.00 | +5.25 +5.25 | +5.50 +5.50 | +5.75 +5.75 | +6.00 +6.00 | +6.25 +6.25 | +6.50 +6.50 | +6.75 +6.75 | +7.00 +7.00 | +7.25 +7.25 | +7.50 +7.50 | +7.75 +7.75 | +8.00 +8.00 | +8.25 +8.25 | +8.50 +8.50 | +8.75 +8.75 | +9.00 +9.00 | +9.25 +9.25 | +9.50 +9.50 | +9.75 +9.75 | +10.00 +10.00 | +10.25 +10.25 | +10.50 +10.50 | +10.75 +10.75 | +11.00 +11.00 | +11.25 +11.25 | +11.50 +11.50 | +11.75 +11.75 | +12.00 +12.00 | +12.25 +12.25 | +12.50 +12.50 | +12.75 +12.75 | +13.00 +13.00 | +13.25 +13.25 | +13.50 +13.50 | +13.75 +13.75 | +14.00 +14.00 | +14.25 +14.25 | +14.50 +14.50 | +14.75 +14.75 | +15.00 +15.00 ===================================================================================== ========================================================================= SHEET2 (my invoice) ========================================================================= A | B | C | D | E | F 1 | PRODUCT | SPH | CYL | ADD | QTY | COST -------------------------------------------------------------------- 2 | PRODUCT A | -10.00 | 10.00 | 10.00 | 1 | $10 3 | PRODUCT A | -11.00 | 10.00 | 10.00 | 1 | $11 ===> Out Range addcost $1 4 | PRODUCT B | -15.00 | 15.00 | 15.00 | 1 | $50 5 | PRODUCT B | -15.50 | 15.50 | 10.00 | 2 | $52 ===> Out Range addcost $2 --------------------------------------------------------------------
Register To Reply
Bookmarks