Forum Discussion

Sparky16's avatar
Sparky16
Copper Contributor
Mar 10, 2019

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

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    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)
  • For the data structured like this

    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

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Perhaps, you need a formula like this:
    =IF(A1>1500,
    A1*25,
    A1*20)

Resources