Sparkline/Formula Help

[Gliffix101]

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

 

[Gliffix101]

Hello,

Thanks to another post, I was able to solve for all except the Spark lines. I still cannot get Spark Lines to ignore the #DIV/0! errors and not treat them as a zero. Any suggestions?

Sorry, I have not posted enough to remove/edit this question. After posting, found an easier way to handle my AT:BH lookups to remove DIV/0 errors, allowing my averages and stdev calculations to work. Spark lines, on the other hand....

Thank you,

Bill