What Formula Do I Use To Show A Calculation For A Range If Result Is BETWEEN Certain Values?

[Dhira]

Hi all,
I'll try to explain:

I have a value in cell A1 And another in B1. This formula works, but only for if the difference is more than the value of A1:
=IF(B1-A1>A1*100%,((B1*50%)),"")

What I want is a staggered range.

• If B1 is "between" 100% & 125% *(A1), how would I show that?
• Then How do I show:
  if (B1-A1) is "between" 100% & 125% *(A1), 'show' 50% *(A1) .... ELSE if (B1-A1) is "between" 125% & 150% *(A1), 'show' 55% *(A1) ... ELSE if (B1-A1) is "between" 150% & 175% *(A1), 'show' 60 *(A1)

Hope that explains what I need in one formula.
Thanks in advance.

 

[offthelip]

try this formula:

=A1*(0.5+0.05*ROUNDDOWN(4*(B1-2*A1)/A1,0))

 

[Dhira]

try this formula:

=A1*(0.5+0.05*ROUNDDOWN(4*(B1-2*A1)/A1,0))

Thank you! This is ALMOST perfect.
Just one more tweak:

How do I make it so that the limit stops at 90? Meaning if Column A is 100 and column B is over 400 the result is 90 which is fine.
But if Column B is over 425, I still want the result to remain at 90 (because that number will be a percentage. Can't have a percentage over 100%). So how to add a limit of 90?

 

[offthelip]

try this:

=A1*MIN((0.5+0.05*ROUNDDOWN(4*(B1-2*A1)/A1,0)),0.9)

 

[Dhira]

try this:

=A1*MIN((0.5+0.05*ROUNDDOWN(4*(B1-2*A1)/A1,0)),0.9)

This was working till I tried playing with the numbers in Column A. Then I realized it doesn't actually work for numbers other than 100...
This time I was able to use the mini sheet.
Your formula is in Column D & I have notes explaining what I want to show in the columns.

If "% Increase In Value" (Column C) is in the below range, show the following percentage. Otherwise show blank.
If greater than 50, show the profit % increment with 5% increases up till 90% (0.9) max:

100 - 125% = 50%
125 - 150% = 55%
150 - 175% = 60%
175 - 200% = 65%
200 - 225% = 70%
225 - 250% = 75%
250 - 275% = 80%
275 - 300% = 85%
300 - 325% = 90%
325 + = 90%

BUDGET.xlsx
	A	B	C	D	E	F
1	Initial Value	Latest Value	% Increase or Decrease In Value	% Profit Split
2	$ 100.00	$ 200.00	100%	50%
3	$ 100.00	$ 226.00	126%	55%
4	$ 100.00	$ 251.00	151%	60%
5	$ 100.00	$ 276.00	176%	65%
6	$ 100.00	$ 301.00	201%	70%
7	$ 100.00	$ 351.00	251%	80%
8	$ 100.00	$ 376.00	276%	85%
9	$ 100.00	$ 426.00	326%	90%	<-- This is great that it caps at 90… BUT the calculation is incorrect if the Column A Number is NOT "100". See Below:
10	$ 100.00	$ 451.00	351%	90%
11	$ 100.00	$ 476.00	376%	90%
12	$ 100.00	$ 501.00	401%	90%
13
14
15	Initial Value	Latest Value	% Increase or Decrease In Value	% Profit Split
16	$ 135.00	$ 201.00	49%	54%	<-- These should still cap at 90, but look what happens if column A is NOT 100.. the formula should ONLY go up or show in ranges of 25, but it doesn't. Formula calculation is off
17	$ 200.00	$ 251.00	26%	80%	•	If the increase/decrease (Column C) is greater than 50% the % Profit Split should show the proper range
18	$ 250.00	$ 301.00	20%	88%	•	If the increase/decrease (Column C) is greater than 90% the % Profit Split should show be capped at 90%
19	$ 275.00	$ 276.00	0%	96%
20	$ 500.00	$ 301.00	-40%	125%
21
22
Sheet1

Cell Formulas
Range	Formula
C16:C20,C2:C12	C2	=(B2-A2)*100%/A2
D16:D20,D2:D12	D2	=A2*MIN((0.5+0.05*ROUNDDOWN(4*(B2-2*A2)/A2,0)),0.9)%

 

[Dhira]

I think this requires the nested "IF" or the "IFS" function (which only works in Excel 2019 & above, but which I do have).
Just not quite sure how to write it here (for column D)...

 

[Dhira]

This is the formula I came up with which is close except for the fact that the result is ALWAYS "50%".

=IFS(D2<100%,"",D2>100%,"50%",D2>125%,"55%",D2>150%,"60%",D2>175%,"65%",D2>200%,"70%",D2>225%,"75%",D2>250%,"80%",D2>300%,"85%",D2>325%,"90%")

See mini sheet below:
Anyone have any suggested edits to calculate the ranges between the percentages in order for Column D to show the next level up?

BUDGET.xlsx
	A	B	C	D
1	Initial Value	Latest Value	% Increase or Decrease In Value	% Profit To Take My (Attempt II)
2	$ 100.00	$ 220.00	120%	50%
3	$ 100.00	$ 226.00	126%	50%
4	$ 100.00	$ 251.00	151%	50%
5	$ 100.00	$ 276.00	176%	50%
6	$ 100.00	$ 301.00	201%	50%
7	$ 100.00	$ 351.00	251%	50%
8	$ 100.00	$ 376.00	276%	50%
9	$ 100.00	$ 426.00	326%	50%
10	$ 100.00	$ 451.00	351%	50%
11	$ 100.00	$ 476.00	376%	50%
12	$ 100.00	$ 501.00	401%	50%
13
Orig (2)

Cell Formulas
Range	Formula
C2:C12	C2	=(B2-A2)*100%/A2
D2:D12	D2	=IFS(C2<100%,"",C2>100%,"50%",C2>125%,"55%",C2>150%,"60%",C2>175%,"65%",C2>200%,"70%",C2>225%,"75%",C2>250%,"80%",C2>300%,"85%",C2>325%,"90%")

 

[offthelip]

I think this requires the nested "IF" or the "IFS" function (which only works in Excel 2019 & above, but which I do have)

No it doesn't require ifs, it is a calculation and they are best done using one equation if at all possible , and the modification that you want if very simple, just get rid of the "A1*" at the start of the equation ,
So in D2 put this:

=MIN((0.5+0.05*ROUNDDOWN(4*(B2-2*A2)/A2,0)),0.9)

and format the cell as percentage,. or
put this:

=100*MIN((0.5+0.05*ROUNDDOWN(4*(B2-2*A2)/A2,0)),0.9)

and format it as a number

 

[Dhira]

No it doesn't require ifs, it is a calculation and they are best done using one equation if at all possible , and the modification that you want if very simple, just get rid of the "A1*" at the start of the equation ,
So in D2 put this:

=MIN((0.5+0.05*ROUNDDOWN(4*(B2-2*A2)/A2,0)),0.9)

and format the cell as percentage,. or
put this:

=100*MIN((0.5+0.05*ROUNDDOWN(4*(B2-2*A2)/A2,0)),0.9)

and format it as a number

Your formula does not work with other numbers input in columns A & B.
It just increases with every row, even if the numbers are compltely different and even if the percentages are lower than what should give a result... Not sure you understand what I'm asking.

 

[offthelip]

My formula produces exactly the figures that you posted in your #5, so I don't understand what you want