Gliffix101
Board Regular
- Joined
- Apr 1, 2014
- Messages
- 81
Hello All,
Thank you for taking a moment to review and see if you can assist. Attached is a copy of the sample data I am working with in Microsoft Excel 32-bit 2013.
* Columns A:AS are my weeks 11-25 data to calculate the Pass %.
* Some weeks failed (see Week 11 with 0 out of 3), while other weeks had no samples (Weeks 15 & 16).
* In Columns AT:BH, I am compiling all of the Pass % for calculations. As you can see, some of the 0% and some of the #DIV/0! errors carried over (as expected).
- Formula: =C4 to reference the earlier Pass % calculations
* Columns BI:BO contain the statistical calculations of the population.
- Formula: '=AVERAGEIF(AT4:BH4,"<>#DIV/0!")
- Formula: '=STDEV(IF(NOT(ISERROR(AT4:BH4)),AT4:BH4))
- Formula: '=IF(BO4>0,"Up",IF(BO4<0,"Down","Neutral"))
- Formula: '=SLOPE(BF4:BH4,$BG$1:$BI$1)
Here is my question:
My slope in Column BO and the Trending Sparkline in Column BM are reflecting the #DIV/0! as a zero value, skewing my metrics. How can I structure these formulas and Sparklines to not include the #DIV/0? For the Sparkline, I did check off in the Sparkline options to show empty cells as gaps, but since it's a #DIV/0! error, it's not technically empty. I tried using IFERROR which returns the cell blank, but still reflects as 0 in the Sparkline.
Do I have any options other than deleting the formula in all #DIV/0! cells? My original data has thousands of rows.
Any help is appreciated!
Thank you,
Bill
Thank you for taking a moment to review and see if you can assist. Attached is a copy of the sample data I am working with in Microsoft Excel 32-bit 2013.
* Columns A:AS are my weeks 11-25 data to calculate the Pass %.
* Some weeks failed (see Week 11 with 0 out of 3), while other weeks had no samples (Weeks 15 & 16).
* In Columns AT:BH, I am compiling all of the Pass % for calculations. As you can see, some of the 0% and some of the #DIV/0! errors carried over (as expected).
- Formula: =C4 to reference the earlier Pass % calculations
* Columns BI:BO contain the statistical calculations of the population.
- Formula: '=AVERAGEIF(AT4:BH4,"<>#DIV/0!")
- Formula: '=STDEV(IF(NOT(ISERROR(AT4:BH4)),AT4:BH4))
- Formula: '=IF(BO4>0,"Up",IF(BO4<0,"Down","Neutral"))
- Formula: '=SLOPE(BF4:BH4,$BG$1:$BI$1)
Here is my question:
My slope in Column BO and the Trending Sparkline in Column BM are reflecting the #DIV/0! as a zero value, skewing my metrics. How can I structure these formulas and Sparklines to not include the #DIV/0? For the Sparkline, I did check off in the Sparkline options to show empty cells as gaps, but since it's a #DIV/0! error, it's not technically empty. I tried using IFERROR which returns the cell blank, but still reflects as 0 in the Sparkline.
Do I have any options other than deleting the formula in all #DIV/0! cells? My original data has thousands of rows.
Any help is appreciated!
Thank you,
Bill