Formula in Excel

Copper Contributor

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 above 1500 charge at a separate rate can someone please provide some ideas on how to create those formulas

3 Replies
Perhaps, you need a formula like this:
=IF(A1>1500,
A1*25,
A1*20)

For the data structured like this

image.png

if in B1 is your rate for the mileage below 1500, and in C1 - above it, you may use formulas

in B2:
=MIN($A2,1500)*$B$1

in C2:
=MAX(0,$A2-1500)*$C$1

in D2:
=B2+C2

and drag them down till end of your range

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)