Nested Formula

[Dee8]

Hi! I'm sorry I've been successful with this one before but for some reason it's not working for me today and I need to get this done fast. Sorry!! Can anyone help with a formula that kicks back a result based on the following? -
If a percentage is greater than or equal to this and less than or equal to that, the percentage should be reduced by this percent - e.g.
If last year's percent in cell A1 is 155% because it is between 150% and 174.99% the result in C1 should be a reduction of 10% so in this case 139.5%. and nested because I need to include multiple scenarios e.g. if number is between 100% and 124.99% the reduction/ new goal should be a reduction of 8% etc.
Any help appreciated thank you!!

 

[DanteAmor]

Hi @Dee8

Try this:

Dante Amor
	A	B	C
1	155%		10%
Hoja5

Cell Formulas
Range	Formula
C1	C1	=IF(A1<100%,7%,IF(A1<125%,8%,IF(A1<150%,9%,IF(A1<175%,10%,11%))))

If it is not what you are looking for, then put all the possible cases and the result that you want.
--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[awoohaw]

i don't think you need any kind of if. try this:

Book1
	A	B
1	155%	139.500%
2
3	percent
4	100%	8%
5	125%	9%
6	150%	10%
7	175%	11%
Sheet1

Cell Formulas
Range	Formula
B1	B1	=A1*(1-XLOOKUP(A1,A4:A7,B4:B7,1,-1))

 

[awoohaw]

you could also do it with an IFS statement:

=(1-IFS(A1<100%,7%,A1<125%,8%,A1<150%,9%,A1<175%,10%,A1>=175%,11%))*A1

 

[Dee8]

Hi @Dee8

Try this:

Dante Amor
	A	B	C
1	155%		10%
Hoja5

Cell Formulas
Range	Formula
C1	C1	=IF(A1<100%,7%,IF(A1<125%,8%,IF(A1<150%,9%,IF(A1<175%,10%,11%))))

If it is not what you are looking for, then put all the possible cases and the result that you want.
--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

	Range	Expected reduction	New Goal
	<=	to	99.99%	5.00%	0.00%	to	94.99%
	100.00%	to	124.99%	8.00%	92.00%	to	114.99%
	125.00%	to	149.99%	10.00%	112.50%	to	134.99%
	150.00%	to	174.99%	15.00%	127.50%	to	148.74%
	175.00%	to	199.99%	20.00%	140.00%	to	159.99%
	200.00%	to	224.99%	25.00%	150.00%	to	168.74%
	225.00%	to	299.99%	30.00%	157.50%	to	200.00%
	300.00%	to	399.99%	40.00%	180.00%	to	200.00%
	>=	to	400.00%	>=50%	200.00%	to	200.00%
	Last Year Result	Expected reduction based on last year result	New Goal based on last year result and thusly expected reduction

 

[Dee8]

Hi @Dee8

Try this:

Dante Amor
	A	B	C
1	155%		10%
Hoja5

Cell Formulas
Range	Formula
C1	C1	=IF(A1<100%,7%,IF(A1<125%,8%,IF(A1<150%,9%,IF(A1<175%,10%,11%))))

If it is not what you are looking for, then put all the possible cases and the result that you want.
--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

Hi DanteAmor, thanks so much I sent an example let me know if it makes sense

 

[Dee8]

	Range	Expected reduction	New Goal
	<=	to	99.99%	5.00%	0.00%	to	94.99%
	100.00%	to	124.99%	8.00%	92.00%	to	114.99%
	125.00%	to	149.99%	10.00%	112.50%	to	134.99%
	150.00%	to	174.99%	15.00%	127.50%	to	148.74%
	175.00%	to	199.99%	20.00%	140.00%	to	159.99%
	200.00%	to	224.99%	25.00%	150.00%	to	168.74%
	225.00%	to	299.99%	30.00%	157.50%	to	200.00%
	300.00%	to	399.99%	40.00%	180.00%	to	200.00%
	>=	to	400.00%	>=50%	200.00%	to	200.00%
	Last Year Result	Expected reduction based on last year result	New Goal based on last year result and thusly expected reduction

Hi @Dee8

Try this:

Dante Amor
	A	B	C
1	155%		10%
Hoja5

Cell Formulas
Range	Formula
C1	C1	=IF(A1<100%,7%,IF(A1<125%,8%,IF(A1<150%,9%,IF(A1<175%,10%,11%))))

If it is not what you are looking for, then put all the possible cases and the result that you want.
--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

	Last Year Result	Expected reduction based on last year result	New Goal based on last year result and thusly expected reduction
Example​	155%	15%	133.75%

 

[awoohaw]

@Dee8 , you may want to post data instead of images. We cannot see your formulas in images. Plus it makes it easier for the forum to help you in two ways: 1-saves time, 2- fewer errors.

Mr. Excel has a tool, xl2bb add in (link below) that allows you to post mini workbooks. If you can use that it would be very helpful.

 

[Dee8]

@Dee8 , you may want to post data instead of images. We cannot see your formulas in images. Plus it makes it easier for the forum to help you in two ways: 1-saves time, 2- fewer errors.

Mr. Excel has a tool, xl2bb add in (link below) that allows you to post mini workbooks. If you can use that it would be very helpful.

Hi awoohaw! thanks so much, really helpful. There are no formulas in these images I'm trying to the accomplish the formula to reflect the ranges of data that I posted if that makes sense. Let me know thanks so much.

 

[awoohaw]

Hi awoohaw! thanks so much, really helpful. There are no formulas in these images I'm trying to the accomplish the formula to reflect the ranges of data that I posted if that makes sense. Let me know thanks so much.

it is still helpful to post the data as the forum does not have to manually type them in, and there can be typos.