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