Forum Discussion

Asghar_Abbas's avatar
Asghar_Abbas
Copper Contributor
Apr 04, 2020
Solved

Conditional formulas

If the value of D2 like 1023 miles, I need help in formula like until 833 miles should be multiply by *0.45 then the balance 190 miles should’ve multiply by *0.25
Please advise
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Apr 05, 2020

    Asghar_Abbas 

    Try this:

    =MIN(833,D2)*0.45+MAX(0,D2-833)*0.25

     

    .... but to make a bit more dynamic, I would recommend to replace the "hard coded" numbers 833, 0.45 and 0.25 to absolute cell references. Then your formula could look like this:

    =MIN($A$1,D2)*$A$2+MAX(0,D2-$A$1)*$A$3

    Then you can set these parameters in on location and use them throughout the sheet without having to adjust (copy/paste) all affected formulae when the parameters change. Your choice, though.

1 Reply

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Apr 05, 2020

    Asghar_Abbas 

    Try this:

    =MIN(833,D2)*0.45+MAX(0,D2-833)*0.25

     

    .... but to make a bit more dynamic, I would recommend to replace the "hard coded" numbers 833, 0.45 and 0.25 to absolute cell references. Then your formula could look like this:

    =MIN($A$1,D2)*$A$2+MAX(0,D2-$A$1)*$A$3

    Then you can set these parameters in on location and use them throughout the sheet without having to adjust (copy/paste) all affected formulae when the parameters change. Your choice, though.

Resources