Forum Discussion
Formula in Excel
I know the basics in creating some formulas I'm trying to create a spread sheet for mileage that if someone has up to 1500km it is charged at a specific rate , and in another column I want anything ...
If the charge per km for the first 1500 km is 20 and the charge per km for the excess over 1500 km is 25, such would be similar to a tax table wherein the formula, assuming the distance is in A6, would be:
=LOOKUP(A6,
{0;1500},
{0,30000}+(A6-{0;1500})*{20,25})
Instead of the array constants, cell references should be used, such that {0;1500} should be entered in, and referred to as, B$2:B$3, {0;30000} should be entered in, and referred to as, C$2:C$3, and {20;25} should be entered in, and referred to as, D$2:D$3.
Thus, the same formula using cell references instead of array constants would be:
=LOOKUP(A6,
B$2:B$3,
C$2:C$3+(A6-B$2:B$3)*D$2:D$3)
=LOOKUP(A6,
{0;1500},
{0,30000}+(A6-{0;1500})*{20,25})
Instead of the array constants, cell references should be used, such that {0;1500} should be entered in, and referred to as, B$2:B$3, {0;30000} should be entered in, and referred to as, C$2:C$3, and {20;25} should be entered in, and referred to as, D$2:D$3.
Thus, the same formula using cell references instead of array constants would be:
=LOOKUP(A6,
B$2:B$3,
C$2:C$3+(A6-B$2:B$3)*D$2:D$3)