# Yes or No response and shorten formula



## Bedford (Jan 1, 2023)

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 (Jan 1, 2023)

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


----------



## Peter_SSs (Jan 2, 2023)

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 (Jan 2, 2023)

Alex Blakenburg said:


> 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.xlsxPQRSTU29Chart of Dimension "A"30Screen Width"A"311200</=W</=763221300<W</=793331500<W</=873442000<W</=963552500<W</=1043663000<W</=1133773500<W</=1213884000<W</=130MG SS Width less than 2500


----------



## Bedford (Jan 2, 2023)

Peter_SSs said:


> =(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 (Jan 2, 2023)

Bedford said:


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


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



Bedford said:


> 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.xlsmDEFJPQRSTU7Yes161725552442Yours182442Mine311200</=W</=763221300<W</=793331500<W</=873442000<W</=963552500<W</=1043663000<W</=1133773500<W</=1213884000<W</=130Bedford (2)Cell FormulasRangeFormulaE17E17=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)E18E18=(D17-XLOOKUP(D17,{1300;1500;2000;2500;3000;3500;4000;4500},$U$31:$U$38,,1)-9)*(J7="Yes")


----------



## Bedford (Jan 2, 2023)

Peter_SSs said:


> =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 (Jan 2, 2023)

Bedford said:


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



Bedford said:


> ,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 (Jan 2, 2023)

Peter_SSs said:


> How do we know what "the appropriate number from column U" is?
> 
> Your original formula said (in part)
> 
> ...


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


----------



## Peter_SSs (Jan 2, 2023)

Bedford said:


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


----------



## Bedford (Jan 1, 2023)

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 (Jan 2, 2023)

Peter_SSs said:


> How do we know what "the appropriate number from column U" is?
> 
> Your original formula said (in part)
> 
> ...





Peter_SSs said:


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


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 (Jan 2, 2023)

Peter_SSs said:


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


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 (Jan 2, 2023)

Bedford said:


> 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 (Jan 2, 2023)

Peter_SSs said:


> 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 (Jan 2, 2023)

Peter_SSs said:


> 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 (Jan 2, 2023)

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 (Jan 2, 2023)

Peter_SSs said:


> 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 (Jan 2, 2023)

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


----------

