Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Normalizing Data
I am diabetic and test my blood glucose level 2 hours (approximately) after
eating. Since I don't always test exactly after 2 hours, how can I normalize the glucose reading to 2 hours? Below is a sample of my data: ELAPSED TIME GLUCOSE Normalized 1.9 150 ? 2.2 110 ? 1.7 160 ? 2.3 100 ? 2.0 130 ? Thanks, k1ngr |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Normalizing Data
The data you have given follows a linear relationship... if you plot Glucose
level vs. time, on a scatter plot or line graph, you can see this. Since this is the case, the linear correlation applies: X1/Y1 = X2/Y2. To normalize, the time you want (X2) is always equal to 2. Y1 is your recorded Glucose level, and X1 is your recorded time. Your normalized Glucose level is Y2. In short, if you have time in column A and glucose level in B, fill this formula into C (normalized): =2*B2/A2 Of course, you should get more data points to see how accurate this is. Cheers! -- Anne Murray "k1ngr" wrote: I am diabetic and test my blood glucose level 2 hours (approximately) after eating. Since I don't always test exactly after 2 hours, how can I normalize the glucose reading to 2 hours? Below is a sample of my data: ELAPSED TIME GLUCOSE Normalized 1.9 150 ? 2.2 110 ? 1.7 160 ? 2.3 100 ? 2.0 130 ? Thanks, k1ngr |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Normalizing Data
When I tried it I got increasing values as time increases. I should be
getting decreasingvalues, e.g. for 1.9 I should get a value less than 150. "FinRazel" wrote: The data you have given follows a linear relationship... if you plot Glucose level vs. time, on a scatter plot or line graph, you can see this. Since this is the case, the linear correlation applies: X1/Y1 = X2/Y2. To normalize, the time you want (X2) is always equal to 2. Y1 is your recorded Glucose level, and X1 is your recorded time. Your normalized Glucose level is Y2. In short, if you have time in column A and glucose level in B, fill this formula into C (normalized): =2*B2/A2 Of course, you should get more data points to see how accurate this is. Cheers! -- Anne Murray "k1ngr" wrote: I am diabetic and test my blood glucose level 2 hours (approximately) after eating. Since I don't always test exactly after 2 hours, how can I normalize the glucose reading to 2 hours? Below is a sample of my data: ELAPSED TIME GLUCOSE Normalized 1.9 150 ? 2.2 110 ? 1.7 160 ? 2.3 100 ? 2.0 130 ? Thanks, k1ngr |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Normalizing Data
On Dec 12, 12:11 pm, k1ngr wrote:
I am diabetic and test my blood glucose level 2 hours (approximately) after eating. Since I don't always test exactly after 2 hours, how can I normalize the glucose reading to 2 hours? I would suggest that you ask your doctor or dietician. The answer depends on what assumption you can make about the normal rate of change of glucose levels. Does it change linearly? Does it decay exponentially? Or do you need conduct an experiment to determine your individual rate of change? Off-hand, I would think that any "normalization" procedure would be presumptuous. The purpose of taking readings at a prescribed time after eating is to determine how much your glucose level is changing. Having said all that, it seems to me that your times are not too far off from exactly 2 hours. So it might be reasonable to assume that the rate of change is "locally" linear. If you can make that assumption, the formula might be: =G1 * A1 / 2 where G1 is your glucose reading, and A1 is the elapsed time in hours. Medical note: This assumes that your glucose level declines continuously. In fact, it does not, at least not for normal people. But when I had fasting glucose tests many years ago, my turn-around was around 3 to 4 hours later. ----- original posting ----- On Dec 12, 12:11*pm, k1ngr wrote: I am diabetic and test my blood glucose level 2 hours (approximately) after eating. Since I don't always test exactly after 2 hours, how can I normalize the glucose reading to 2 hours? *Below is a sample of my data: ELAPSED TIME * *GLUCOSE * * * *Normalized * * * *1.9 * * * * * * * * * * * * *150 * * * * * * * * * ? * * * *2.2 * * * * * * * * * * * * *110 * * * * * * * * * ? * * * *1.7 * * * * * * * * * * * * *160 * * * * * * * * * ? * * * *2.3 * * * * * * * * * * * * *100 * * * * * * * * * ? * * * *2.0 * * * * * * * * * * * * *130 * * * * * * * * * ? Thanks, k1ngr |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Normalizing Data
Sorry, I think I accidentally reverse sorted one column independently of the
other, finding a direct, rather than an inverse correlation. For the inverse equation: X = (Elapsed Time)*(Meaured Glucose)/(Time=2 hours) But, I think you should follow what joe says, and measure your glucose levels at exactly the same time. You don't want to be estimating numbers based on an assumption of linearity that is probably not correct. For the sake of experiment, though, you could test, for example, at 1.6, 1.8, 2, and 2.2 hours in a given day and see how well the transformed values match your 2 hour time point. -- Anne Murray "FinRazel" wrote: The data you have given follows a linear relationship... if you plot Glucose level vs. time, on a scatter plot or line graph, you can see this. Since this is the case, the linear correlation applies: X1/Y1 = X2/Y2. To normalize, the time you want (X2) is always equal to 2. Y1 is your recorded Glucose level, and X1 is your recorded time. Your normalized Glucose level is Y2. In short, if you have time in column A and glucose level in B, fill this formula into C (normalized): =2*B2/A2 Of course, you should get more data points to see how accurate this is. Cheers! -- Anne Murray "k1ngr" wrote: I am diabetic and test my blood glucose level 2 hours (approximately) after eating. Since I don't always test exactly after 2 hours, how can I normalize the glucose reading to 2 hours? Below is a sample of my data: ELAPSED TIME GLUCOSE Normalized 1.9 150 ? 2.2 110 ? 1.7 160 ? 2.3 100 ? 2.0 130 ? Thanks, k1ngr |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Normalizing Data
I agree with you about the dangers of normalizing diabetes data, however, I
was courious about how to do the process and wanted to see what the results would be. How would you do the normalization if the data were not linear? Years aga I ded research with beef calves where we measured their weight all on the same day and thne normalized their weight to 1 year old. Ther weights were nor linear and I can't remember what statistical model was used to do the normalization. "FinRazel" wrote: Sorry, I think I accidentally reverse sorted one column independently of the other, finding a direct, rather than an inverse correlation. For the inverse equation: X = (Elapsed Time)*(Meaured Glucose)/(Time=2 hours) But, I think you should follow what joe says, and measure your glucose levels at exactly the same time. You don't want to be estimating numbers based on an assumption of linearity that is probably not correct. For the sake of experiment, though, you could test, for example, at 1.6, 1.8, 2, and 2.2 hours in a given day and see how well the transformed values match your 2 hour time point. -- Anne Murray "FinRazel" wrote: The data you have given follows a linear relationship... if you plot Glucose level vs. time, on a scatter plot or line graph, you can see this. Since this is the case, the linear correlation applies: X1/Y1 = X2/Y2. To normalize, the time you want (X2) is always equal to 2. Y1 is your recorded Glucose level, and X1 is your recorded time. Your normalized Glucose level is Y2. In short, if you have time in column A and glucose level in B, fill this formula into C (normalized): =2*B2/A2 Of course, you should get more data points to see how accurate this is. Cheers! -- Anne Murray "k1ngr" wrote: I am diabetic and test my blood glucose level 2 hours (approximately) after eating. Since I don't always test exactly after 2 hours, how can I normalize the glucose reading to 2 hours? Below is a sample of my data: ELAPSED TIME GLUCOSE Normalized 1.9 150 ? 2.2 110 ? 1.7 160 ? 2.3 100 ? 2.0 130 ? Thanks, k1ngr |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Normalizing Data
I looked up blood glucose level on wikipedia, and the article has an
idealized curve showing blood glucose with time. Blood glucose levels generally increase throughout the day, with major and minor spikes after meals. This is, of course, nonlinear, but there are intervals that could, perhaps, be treated as linear. On the other hand, with enough data points, you might be able to obtain the rate constants for the polynomial function (2nd order) representing each spike, which might lead to a more reliable transform. -- Anne Murray "k1ngr" wrote: I agree with you about the dangers of normalizing diabetes data, however, I was courious about how to do the process and wanted to see what the results would be. How would you do the normalization if the data were not linear? Years aga I ded research with beef calves where we measured their weight all on the same day and thne normalized their weight to 1 year old. Ther weights were nor linear and I can't remember what statistical model was used to do the normalization. "FinRazel" wrote: Sorry, I think I accidentally reverse sorted one column independently of the other, finding a direct, rather than an inverse correlation. For the inverse equation: X = (Elapsed Time)*(Meaured Glucose)/(Time=2 hours) But, I think you should follow what joe says, and measure your glucose levels at exactly the same time. You don't want to be estimating numbers based on an assumption of linearity that is probably not correct. For the sake of experiment, though, you could test, for example, at 1.6, 1.8, 2, and 2.2 hours in a given day and see how well the transformed values match your 2 hour time point. -- Anne Murray "FinRazel" wrote: The data you have given follows a linear relationship... if you plot Glucose level vs. time, on a scatter plot or line graph, you can see this. Since this is the case, the linear correlation applies: X1/Y1 = X2/Y2. To normalize, the time you want (X2) is always equal to 2. Y1 is your recorded Glucose level, and X1 is your recorded time. Your normalized Glucose level is Y2. In short, if you have time in column A and glucose level in B, fill this formula into C (normalized): =2*B2/A2 Of course, you should get more data points to see how accurate this is. Cheers! -- Anne Murray "k1ngr" wrote: I am diabetic and test my blood glucose level 2 hours (approximately) after eating. Since I don't always test exactly after 2 hours, how can I normalize the glucose reading to 2 hours? Below is a sample of my data: ELAPSED TIME GLUCOSE Normalized 1.9 150 ? 2.2 110 ? 1.7 160 ? 2.3 100 ? 2.0 130 ? Thanks, k1ngr |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Normalizing the y axis | Charts and Charting in Excel | |||
Normalizing the y axis | Charts and Charting in Excel | |||
normalizing data -- a twist | Excel Worksheet Functions | |||
normalizing data | Excel Discussion (Misc queries) | |||
Normalizing data formula? | Excel Discussion (Misc queries) |