#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 24
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,059
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 24
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 24
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Normalizing the y axis ragtopcaddy Charts and Charting in Excel 2 March 24th 06 05:25 PM
Normalizing the y axis ragtopcaddy Charts and Charting in Excel 2 March 24th 06 05:24 PM
normalizing data -- a twist Brad Excel Worksheet Functions 6 June 4th 05 03:42 PM
normalizing data 4gokycats Excel Discussion (Misc queries) 1 March 27th 05 03:09 AM
Normalizing data formula? Jessica Excel Discussion (Misc queries) 1 January 25th 05 11:48 PM


All times are GMT +1. The time now is 02:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"