Forum Discussion
kindly help me with creating a formula
Note: if we take 26 days as an example, that means :
8 x 60 = 480
15 X 120 = 1800
3 x 200 = 600
so total amount : 2880
2 Replies
An alternative:
The formula in B2 is
=SUMPRODUCT((A2>$E$2:$E$4)*(A2-$E$2:$E$4)*($F$2:$F$4-$F$1:$F$3))
Here are two different formulas, and it's entirely possible that other Excel experts will add more. These both make use of a table arrayed as shown below:
One of the formulas is based on the VLOOKUP formula, and it should work regardless of which version of Excel you have.
=VLOOKUP(B3,ChargeTbl,3,1)+(((B3+1)-VLOOKUP(B3,ChargeTbl,1,1))*(VLOOKUP(B3,ChargeTbl,2,1)))
This formula uses the new LET function and does require the most recent versions of Excel.
=LET(rw,MATCH(B3,D3:D6),INDEX(F3:F6,rw)+(INDEX(E3:E6,rw)*((B3+1)-INDEX(D3:D6,rw))))
