Forum Discussion

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
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.

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.