Sparkline/Formula Help

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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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
 
Upvote 0

Forum statistics

Threads
1,223,716
Messages
6,174,069
Members
452,542
Latest member
Bricklin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top