Best way to show "variance" in set of values

[JenniferMurphy]

I am working on a worksheet to analyze blood sugar readings for diabetics. One of the columns contains the actual readings in mg/dL. As diabetic treatment progresses, the goal is to lower blood sugar levels, but also to get the readings to be more consistent (lower the variability). I would appreciate suggestions for the best measure of variability for the purposes of comparing readings for different time periods so as to be able to assess progress.

The table below has 4 sample readings (rows 5-8) over 4 different time periods (columns D-G) showing improvement in the readings. Rows 9-13 show the maximum, average, median, middle, and minimum of these values. Row 14 shows the range, which is one measure of variability. Rows 15 & 167 show the variance and std dev using built-in Excel functions, other measures of variability. I find the variance difficult to fathom. The std dev seems more comprehensible.

Rows 17-19 contain some measures I concocted. Each of them calculate the percentage that one half of one of the measures is of the average or middle values. For example, D17 shows that half of the range (183 / 2 = 91.5) is 27% of the average (338), plus or minus. D18 shows a similar result, but compared to the middle value. D19 does a similar calculation but using the std dev.

I would appreciate any comments or suggestions for better measures.

 

[MARK858]

I also can't sort out what those @ signs are all about.

Possibly the below (From support.office.com)....

#This Row or @ or @[Column Name]

Just the cells in the same row as the formula. These specifiers can’t be combined with any other special item specifiers. Use them to force implicit intersection behavior for the reference or to override implicit intersection behavior and refer to single values from a column. Excel automatically changes #This Row specifiers to the shorter @ specifier in tables that have more than one row of data. But if your table has only one row, Excel doesn’t replace the #This Row specifier, which may cause unexpected calculation results when you add more rows. To avoid calculation problems, make sure you enter multiple rows in your table before you enter any structured reference formulas.

 

[Fluff]

That is something that was added when I moved the workbook from Excel 2007 to 2016. I got a #SPILL! error, which must be something new since 2007. It then added @ sings in front of each of my named ranges. I haven't had a chance to read up on what is going on.

It's to do with implicit intersection and the new dynamic array functions

Implicit intersection operator: @ - Microsoft Support

Learn about the implicit intersection operator (@), which returns a single value using logic known as implicit intersection. The implicit intersection operator (@) may return a value, single cell range, or an error. The implicit intersection operator (@) is in a class of functions called dynamic...

support.office.com

 

[NdNoviceHlp]

"trying for a measure that will show how two or more sets of readings compare with each other with respect to the goal of lowering blood sugar readings into the target range" I've read and re-read this thread numerous times and have always wondered how your statistics will actually benefit the subjects? The normal range for blood glucose is fasting 70-100 mg/dl and less than 180 mg/dl pc. So I'm not sure how your readings are being compared to normal... for the most part they're being compared to the other abnormal results for the reading period. It seems that you should be creating a variance from normal not a variance per time period (column). There are many, many factors that effect blood glucose(activity, intake, individual metabolism, the measuring instrument itself, etc.) So doing some fancy statistics and using them to somehow maintain a normal range seems a stretch. Not to mention the vagaries of using statistics to tell U anything that U want to hear. Anyways, the graphical approach with a range representing high/low values overlaid with the actual readings is commonly used to provide a simple visual result that may be most effective to encourage the end user to try to maintain their levels within the "ideal" normal range. HTH. Dave