# Best way to show "variance" in set of values

#### JenniferMurphy

##### Well-known Member
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

##### MrExcel MVP
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.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Fluff

##### MrExcel MVP, Moderator
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

#### NdNoviceHlp

##### Well-known Member
"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

Replies
2
Views
98
Replies
10
Views
985
Replies
0
Views
224
Replies
1
Views
354
Replies
1
Views
663

1,141,060
Messages
5,704,042
Members
421,324
Latest member
Devo182

### 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.

### Which adblocker are you using?

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

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