Yes or No response and shorten formula

[Bedford]

I have created a monster formula that I need help with 2 issues. First issue is to have the below formula result with a "0" if the dependent cell J7 is set to "No", or if J7 is set to "Yes" it calculates as per the below formula returning the appropriate value.
Next is I'm wondering if there is a way to shorten the formula as I'm wondering if it is too long for calculating more efficiently.

=IFS(AND(D17>=200, D17<=1300),D17-U31-9,AND(D17>1300, D17<=1500),D17-U32-9,AND(D17>1500, D17<=2000),D17-U33-9,AND(D17>2000, D17<=2500),D17-U34-9,AND(D17>2500, D17<=3000),D17-U35-9,AND(D17>3000, D17<=3500),D17-U36-9,AND(D17>3500, D17<=4000),D17-U37-9,AND(D17>4000, D17<=4500),D17-U38-9)

Thank you for any help.

 

[Alex Blakenburg]

What do you want the result to be if D17 < 200 or D17 > 4500 ?

 

[Peter_SSs]

Try this

=IF(J7="No",0,IF(AND(D17>=200,D17<=4500),D17-XLOOKUP(D17,{1300;1500;2000;2500;3000;3500;4000;4500},$U$31:$U$38,1)-9,NA()))

Or if it suits, put those values in the curly braces in my formula above into the worksheet (I've used cells K2:K9) and then use this form of the formula.

=IF(J7="No",0,IF(AND(D17>=200,D17<=4500),D17-XLOOKUP(D17,$K$2:$K$9,$U$31:$U$38,1)-9,NA()))

If the answer to Alex's question is "It is not possible for D17 to be outside the range 200 to 4500", then each of the above formulas can be shortened.

=(D17-XLOOKUP(D17,{1300;1500;2000;2500;3000;3500;4000;4500},$U$31:$U$38,1)-9)*(J7="Yes")

=(D17-XLOOKUP(D17,$K$2:$K$9,$U$31:$U$38,,1)-9)*(J7="Yes")

In all of the above I have assumed that J7 is either "Yes" or "No"

 

[Bedford]

What do you want the result to be if D17 < 200 or D17 > 4500 ?

Alex, I have copied a "Mini Sheet" using the MrExcel Xl2bb function and pasted it here, as I use a mac, I can't be certain it will result in a true mini sheet. If you are able to view the copied sheet, you will note there are reference cells to the formula, column "U", rows; 31 through 38. Seeing the referenced cells might make some sense of what the results should be.
Thank you.

MG Cut Sheet.xlsx
	P	Q	R	S	T	U
29		Chart of Dimension "A"
30		Screen Width				"A"
31	1	200	</=	W	</=	76
32	2	1300	<	W	</=	79
33	3	1500	<	W	</=	87
34	4	2000	<	W	</=	96
35	5	2500	<	W	</=	104
36	6	3000	<	W	</=	113
37	7	3500	<	W	</=	121
38	8	4000	<	W	</=	130
MG SS Width less than 2500

 

[Bedford]

=(D17-XLOOKUP(D17,{1300;1500;2000;2500;3000;3500;4000;4500},$U$31:$U$38,1)-9)*(J7="Yes")

Peter, so this formula resulted in 2545 when D17 was set to 2555, as 2555 is > 2500 it should calculate D17-113-9, resulting in 2433. To add to the final result, if the result of the calculation is >2500, it should /2. I feel I'm asking too much of excel, and I prefer to avoid an array as they seem to be demanding on the cpu of the mac, purchased a dell xps 17 a few months back but struggling with getting use to the trackpad when a mouse can't be convenient.
Thank you.

 

[Peter_SSs]

so this formula resulted in 2545 when D17 was set to 2555

Yes, I have omitted a comma in my formula - see below.

as 2555 is > 2500 it should calculate D17-113-9, resulting in 2433.

I don't understand why that calculation & it is not what your original formula calculated.

Here is the sheet with your original formula in E17 and my corrected formula in E18.
Can you clarify further if this is still incorrect.

23 01 02.xlsm
	D	E	F				J						P	Q	R	S	T	U
7							Yes
16
17	2555	2442	Yours
18		2442	Mine
31													1	200	</=	W	</=	76
32													2	1300	<	W	</=	79
33													3	1500	<	W	</=	87
34													4	2000	<	W	</=	96
35													5	2500	<	W	</=	104
36													6	3000	<	W	</=	113
37													7	3500	<	W	</=	121
38													8	4000	<	W	</=	130
Bedford (2)

Cell Formulas
Range	Formula
E17	E17	=IFS(AND(D17>=200, D17<=1300),D17-U31-9,AND(D17>1300, D17<=1500),D17-U32-9,AND(D17>1500, D17<=2000),D17-U33-9,AND(D17>2000, D17<=2500),D17-U34-9,AND(D17>2500, D17<=3000),D17-U35-9,AND(D17>3000, D17<=3500),D17-U36-9,AND(D17>3500, D17<=4000),D17-U37-9,AND(D17>4000, D17<=4500),D17-U38-9)
E18	E18	=(D17-XLOOKUP(D17,{1300;1500;2000;2500;3000;3500;4000;4500},$U$31:$U$38,,1)-9)*(J7="Yes")

 

[Bedford]

=IFS(AND(D17>=200, D17<=1300),D17-U31-9,AND(D17>1300, D17<=1500),D17-U32-9,AND(D17>1500, D17<=2000),D17-U33-9,AND(D17>2000, D17<=2500),D17-U34-9,AND(D17>2500, D17<=3000),D17-U35-9,AND(D17>3000, D17<=3500),D17-U36-9,AND(D17>3500, D17<=4000),D17-U37-9,AND(D17>4000, D17<=4500),D17-U38-9)

Peter, looking at my formula, it seems to have a flaw. It should be subtracting from D17 the appropriate number from column "U", then -9, but it isn't completing the -9. In other words, the resulting 2442 should be -9 to result at 2433.
My mistake.

 

[Peter_SSs]

It should be subtracting from D17 the appropriate number from column "U"

How do we know what "the appropriate number from column U" is?

Your original formula said (in part)

,AND(D17>2500, D17<=3000),D17-U35-9

2555 fits this range, so the calculation would be

D17-U35-9
=2555-104-9
=2442

So the formula is subtracting the 9. If it is the wrong answer then it would seem using U35 is the problem.

 

[Bedford]

How do we know what "the appropriate number from column U" is?

Your original formula said (in part)


2555 fits this range, so the calculation would be

D17-U35-9
=2555-104-9
=2442

So the formula is subtracting the 9. If it is the wrong answer then it would seem using U35 is the problem.

Peter, you are correct with your assumption. In theory it needs to be using U36 (-113) then -9.

 

[Peter_SSs]

In theory it needs to be using U36

If D17 = 2555 should be using U36 then presumably
D17 = 3300 should be using U37
D17 = 3800 should be using U38
D17 = 4300 should be using U39 ... only problem is U39 doesn't contain anything (as far as we know).

You need to clarify what ranges for D17 should be using what cells from column U.