Philip1957
Board Regular
- Joined
- Sep 30, 2014
- Messages
- 182
- Office Version
- 365
- Platform
- Windows
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:
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
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:
Excel 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