Best way to show "variance" in set of values

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,560
Office Version
  1. 365
Platform
  1. Windows
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.

1576976664851.png


I would appreciate any comments or suggestions for better measures.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,191
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 9, 2002
Messages
2,803
"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
 

Forum statistics

Threads
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.
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
Top