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.

 

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

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.

I think this now works; =IFS(AND(D17>=200, D17<=1299),D17-U32-9,AND(D17>1300, D17<=1500),D17-U33-9,AND(D17>1500, D17<=2000),D17-U34-9,AND(D17>2000, D17<=2500),D17-U35-9,AND(D17>2500, D17<=3000),D17-U36-9,AND(D17>3000, D17<=3500),D17-U37-9,AND(D17>3500, D17<=4000),D17-U38-9,AND(D17>4000, D17<=4500),D17-U38-9)
But I'll need to test it on all ranges, the resulting number using 2555 in D17 with the above gives the correct result, 2434 using U36.

 

[Bedford]

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.

Peter, how would you then adjust your formula; =(D17-XLOOKUP(D17,{1300;1500;2000;2500;3000;3500;4000;4500},$U$31:$U$38,,1)-9)*(J7="Yes"), to have it result subtracting U36 when D17 is =/>2500 and <3000?

 

[Peter_SSs]

I think this now works; =IFS(AND(D17>=200, D17<=1299),D17-U32-9,AND(D17>1300, D17<=1500),D17-U33-9,AND(D17>1500, D17<=2000),D17-U34-9,AND(D17>2000, D17<=2500),D17-U35-9,AND(D17>2500, D17<=3000),D17-U36-9,AND(D17>3000, D17<=3500),D17-U37-9,AND(D17>3500, D17<=4000),D17-U38-9,AND(D17>4000, D17<=4500),D17-U38-9)

So that looks like U38 is used for two existing ranges. Or presumably that could be combined into one range of AND(D17>3500, D17<=4500)

And also, U31 (76) is not used at all in that formula.

Are those two things how it should be?

 

[Bedford]

So that looks like U38 is used for two existing ranges. Or presumably that could be combined into one range of AND(D17>3500, D17<=4500)

And also, U31 (76) is not used at all in that formula.

Are those two things how it should be?

In actuality, outside of excel, the formula is meant to be; W-A-9, in this case, W=D17, A=the range U31-U38 and -9 speaks for itself, </=200 should use U31, 201-1300 using U32, and so on. So U37 is used for </= 3500, 3501-4500 it should use U38.

 

[Bedford]

So that looks like U38 is used for two existing ranges. Or presumably that could be combined into one range of AND(D17>3500, D17<=4500)

And also, U31 (76) is not used at all in that formula.

Are those two things how it should be?

I think I've answered my own question, with your help of course. I think your formula should read; =(D17-XLOOKUP(D17,{200;1300;1500;2000;2500;3000;3500;4000},$U$31:$U$38,,1)-9)*(J7="Yes"), I've tested it and it seems to work, can you confirm at your end.

 

[Peter_SSs]

Yes, based on the latest information you have provided (D17 <200 was not covered by either of the earlier formulas ), I think this is what you need.

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

 

[Bedford]

Yes, based on the latest information you have provided (D17 <200 was not covered by either of the earlier formulas ), I think this is what you need.

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

Peter, you are a genius, and very patient. I thank you very much for sharing your precious time with me.
Doug.

 

[Peter_SSs]

You're welcome. Glad we got there in the end.