Making a Formula Skip Over #Div/0

[Philip1957]

Greetings,

I have a formula that calculates -3 StDev using the absolute difference of 13 data points. It works fine until a #Div/0 error shows up in the Range of the 13 data points. This is a normal infrequent occurrence dependent on the manufacturing schedule, not an indication of an error elsewhere in the Workbook.

Here's my formula:

=IF($O$60-(STDEV(ABS($D$60-$C$60),ABS($E$60-$D$60),ABS($P$4-$O$4),ABS($F$60-$E$60),ABS($G$60-$F$60),ABS($H$60-$G$60),ABS($I$60-$H$60),ABS($J$60-$I$60),ABS($K$60-$J$60),ABS($L$60-$K$60),ABS($M$60-$L$60)*3))<0, 0,$O$60-(STDEV(ABS($D$60-$C$60),ABS($E$60-$D$60),ABS($P$4-$O$4),ABS($F$60-$E$60),ABS($G$60-$F$60),ABS($H$60-$G$60),ABS($I$60-$H$60),ABS($J$60-$I$60),ABS($K$60-$J$60),ABS($L$60-$K$60),ABS($M$60-$L$60)*3)))

Is there any way to make this formula skip over the errors and advance so it still captures 13 data points? I tried using nested IFs but only succeeded in confusing myself trying to keep track of them.

The Workbook is stored on SharePoint so I want to avoid VBA if at all possible.

Thanks in advance for your time, patience, and assistance.
~ Phil

 

[joeu2004]

Array-enter (press ctrl+shift+Enter instead of just Enter) the following formula:

=MAX(0, O60 - 3*STDEV(IF(ISNUMBER(D60:M60-C60:L60), ABS(D60:M60-C60:L60)), IF(ISNUMBER(P4-O4), ABS(P4-O4))))

Note the position of "3*". I believe your original formula was incorrect, insofar as it multiplied the last term ABS($M$60-$L$60)*3, not the STDEV result.

If my "correction" is wrong, LMK. I can help you modify my formula so that the last term is indeed multiplied by 3.

Caveat: That formula can result in a #DIV/0 error if less than 2 terms qualify. If that is a concern, you can avoid it by wrapping IFERROR around the entire MAX expression. Briefly:

=IFERROR(MAX(...), "")

 

[StephenCrump]

Is there any way to make this formula skip over the errors and advance so it still captures 13 data points?

@joeu2004 has nicely simplified your formula and allowed for errors, but I think you are saying you want to expand the data range - presumably to O60, P60, Q60 .... as required - so that you have 13 valid data points, and hence 12 valid differences?

If that's correct, two questions:
- Are the two orphan data points O4 and P4 meant to be in the formula? It complicates things if the datapoints aren't in a single row or column.
- What version of Excel are you running?

 

[Philip1957]

joeu2004 / StephenCrump,​

Thank you for the rapid responses and my apologies for not getting back to you sooner.
I'll have to look closer at my original formula before testing the suggested solution. I frequently get lost & confused when setting up complex formulas, and this may be the case here. I'll be able to better answer StephenCrump's question about the orphans then, but I'm pretty sure I shouldn't have orphans. As to my Excel - I'm using whichever incarnation is current in Office 365. It started out as Excel 2016 but with all the updates since then I don't know what they are calling it.

My priorities have been rearranged for me so I may not be able to return to this until Wed or Thur this week. I'll get back to you as soon as I can with more details and test results.