|
Normalize
I have two sets of data (2 very large columns) same type, same source,
different data sets. One set is correct the other (that I want to normalize) has a +10 shift in the (arithm.) mean and +8 difference in the Stand. Dev I do the normalization now with histograms by subtracting 10 from the data that I normalize till I get a perfect histogram overlay (bell) How to do this with a statistical function in Excel? I tried STANDARDIZE using the array to normalize for A1 (variable) and constants for A2 and A3 (mean and Stand. Dev. computed from the the coreect data set) but the value that I obtain makes no sense. Anyone can help me with this problem? -- Frank |
Normalize
"Frank" wrote: I have two sets of data (2 very large columns) same type, same source, different data sets. One set is correct the other (that I want to normalize) has a +10 shift in the (arithm.) mean and +8 difference in the Stand. Dev I do the normalization now with histograms by subtracting 10 from the data that I normalize till I get a perfect histogram overlay (bell) How to do this with a statistical function in Excel? I tried STANDARDIZE using the array to normalize for A1 (variable) and constants for A2 and A3 (mean and Stand. Dev. computed from the the coreect data set) but the value that I obtain makes no sense. Anyone can help me with this problem? -- Frank Frank You want something like y = xb + c where y is sales, x temperature and c is the intercept. First, sort you data by temperature and use these formulas; I created range names for the Sales and Temperature. =SLOPE(Sales,Temp) =INTERCEPT(Sales,Temp) then assuming that you enter a value in A15 for estimated temperature you calculate estimated sales with: 20 =A15*slope+Intercept You might want to also use =STEYX(Sales,Temp) to find the Standard Error and another set of calculations on the month if your sales (like ice cream) are seasonally effected. Regards Peter |
Normalize
Thanks Peter, I am working now with trend lines, you are correct that is the
best approach, also found a formula ("definition of a straight line") on the web to normalize well logs: "For a log curve whose unnormalized values are designated Vlog, the normalized values of the curve Vnorm are given by: Vnorm=Rmin + (Rmax - Rmin)(Vlog - Wmin) / (Wmax - Wmin) . Wmin is the value of a specific lithology in each well. Generally, it is near the minimum value for that curve in that interval.Wmax is the value of a different specific lithology in each well. It is usually near the maximum value for that curve in that interval. Parameters Wmin and Wmax are measured using the uncorrected data. Parameters Rmin and Rmax are the regional best estimates of the correct value for the two lithologies at that location, whether they are constant values or are taken from trend surfaces." I will play with both methods and see what gives me the best results (histogram overlay). Do you know of a better (free) statistics graphics add-in for Excel that draws histograms with the proper bell shape and cummulative frequency curve. That Histogram option in Statistics (Excel-add-in) is a bit clumsy and unattractive (vertical bars rather than a curve). Cheers Frank -- Frank "Billy Liddel" wrote: "Frank" wrote: I have two sets of data (2 very large columns) same type, same source, different data sets. One set is correct the other (that I want to normalize) has a +10 shift in the (arithm.) mean and +8 difference in the Stand. Dev I do the normalization now with histograms by subtracting 10 from the data that I normalize till I get a perfect histogram overlay (bell) How to do this with a statistical function in Excel? I tried STANDARDIZE using the array to normalize for A1 (variable) and constants for A2 and A3 (mean and Stand. Dev. computed from the the coreect data set) but the value that I obtain makes no sense. Anyone can help me with this problem? -- Frank Frank You want something like y = xb + c where y is sales, x temperature and c is the intercept. First, sort you data by temperature and use these formulas; I created range names for the Sales and Temperature. =SLOPE(Sales,Temp) =INTERCEPT(Sales,Temp) then assuming that you enter a value in A15 for estimated temperature you calculate estimated sales with: 20 =A15*slope+Intercept You might want to also use =STEYX(Sales,Temp) to find the Standard Error and another set of calculations on the month if your sales (like ice cream) are seasonally effected. Regards Peter |
Normalize
"Frank" wrote: Thanks Peter, I am working now with trend lines, you are correct that is the best approach, also found a formula ("definition of a straight line") on the web to normalize well logs: "For a log curve whose unnormalized values are designated Vlog, the normalized values of the curve Vnorm are given by: Vnorm=Rmin + (Rmax - Rmin)(Vlog - Wmin) / (Wmax - Wmin) . Wmin is the value of a specific lithology in each well. Generally, it is near the minimum value for that curve in that interval.Wmax is the value of a different specific lithology in each well. It is usually near the maximum value for that curve in that interval. Parameters Wmin and Wmax are measured using the uncorrected data. Parameters Rmin and Rmax are the regional best estimates of the correct value for the two lithologies at that location, whether they are constant values or are taken from trend surfaces." I will play with both methods and see what gives me the best results (histogram overlay). Do you know of a better (free) statistics graphics add-in for Excel that draws histograms with the proper bell shape and cummulative frequency curve. That Histogram option in Statistics (Excel-add-in) is a bit clumsy and unattractive (vertical bars rather than a curve). Cheers Frank Thanks for you complete reply, I'll look at this in more detail later. With regards to a better chart add-in I don't know off hand, however, I tried creating two charts from the same data in Excel. The first was a normal Column chart, the second was a logarithmic chart, on the custom chart tab. I then deleted the borders, plot and chart backgrounds and all the data series. You end up with just a curved line. thjis you resize and drag over the column chart and tweek the curve until it almost fits. I do not know if this would suffice be its worth a try. regards Peter -- Frank "Billy Liddel" wrote: "Frank" wrote: I have two sets of data (2 very large columns) same type, same source, different data sets. One set is correct the other (that I want to normalize) has a +10 shift in the (arithm.) mean and +8 difference in the Stand. Dev I do the normalization now with histograms by subtracting 10 from the data that I normalize till I get a perfect histogram overlay (bell) How to do this with a statistical function in Excel? I tried STANDARDIZE using the array to normalize for A1 (variable) and constants for A2 and A3 (mean and Stand. Dev. computed from the the coreect data set) but the value that I obtain makes no sense. Anyone can help me with this problem? -- Frank Frank You want something like y = xb + c where y is sales, x temperature and c is the intercept. First, sort you data by temperature and use these formulas; I created range names for the Sales and Temperature. =SLOPE(Sales,Temp) =INTERCEPT(Sales,Temp) then assuming that you enter a value in A15 for estimated temperature you calculate estimated sales with: 20 =A15*slope+Intercept You might want to also use =STEYX(Sales,Temp) to find the Standard Error and another set of calculations on the month if your sales (like ice cream) are seasonally effected. Regards Peter |
Normalize
A search on google for free charts through up a large number of free chart
software. http://www.mylot.tv/c/c.aspx?k=chart...tware&source=6 looks promising. regartds Peter "Billy Liddel" wrote: "Frank" wrote: Thanks Peter, I am working now with trend lines, you are correct that is the best approach, also found a formula ("definition of a straight line") on the web to normalize well logs: "For a log curve whose unnormalized values are designated Vlog, the normalized values of the curve Vnorm are given by: Vnorm=Rmin + (Rmax - Rmin)(Vlog - Wmin) / (Wmax - Wmin) . Wmin is the value of a specific lithology in each well. Generally, it is near the minimum value for that curve in that interval.Wmax is the value of a different specific lithology in each well. It is usually near the maximum value for that curve in that interval. Parameters Wmin and Wmax are measured using the uncorrected data. Parameters Rmin and Rmax are the regional best estimates of the correct value for the two lithologies at that location, whether they are constant values or are taken from trend surfaces." I will play with both methods and see what gives me the best results (histogram overlay). Do you know of a better (free) statistics graphics add-in for Excel that draws histograms with the proper bell shape and cummulative frequency curve. That Histogram option in Statistics (Excel-add-in) is a bit clumsy and unattractive (vertical bars rather than a curve). Cheers Frank Thanks for you complete reply, I'll look at this in more detail later. With regards to a better chart add-in I don't know off hand, however, I tried creating two charts from the same data in Excel. The first was a normal Column chart, the second was a logarithmic chart, on the custom chart tab. I then deleted the borders, plot and chart backgrounds and all the data series. You end up with just a curved line. thjis you resize and drag over the column chart and tweek the curve until it almost fits. I do not know if this would suffice be its worth a try. regards Peter -- Frank "Billy Liddel" wrote: "Frank" wrote: I have two sets of data (2 very large columns) same type, same source, different data sets. One set is correct the other (that I want to normalize) has a +10 shift in the (arithm.) mean and +8 difference in the Stand. Dev I do the normalization now with histograms by subtracting 10 from the data that I normalize till I get a perfect histogram overlay (bell) How to do this with a statistical function in Excel? I tried STANDARDIZE using the array to normalize for A1 (variable) and constants for A2 and A3 (mean and Stand. Dev. computed from the the coreect data set) but the value that I obtain makes no sense. Anyone can help me with this problem? -- Frank Frank You want something like y = xb + c where y is sales, x temperature and c is the intercept. First, sort you data by temperature and use these formulas; I created range names for the Sales and Temperature. =SLOPE(Sales,Temp) =INTERCEPT(Sales,Temp) then assuming that you enter a value in A15 for estimated temperature you calculate estimated sales with: 20 =A15*slope+Intercept You might want to also use =STEYX(Sales,Temp) to find the Standard Error and another set of calculations on the month if your sales (like ice cream) are seasonally effected. Regards Peter |
Normalize
Peter, can you help me with this:
I have an intercept (b) and slope (C) from the correct data set. How to generate (correct) my other data set with that slope and intercept?? Thanks Frank -- Frank "Billy Liddel" wrote: A search on google for free charts through up a large number of free chart software. http://www.mylot.tv/c/c.aspx?k=chart...tware&source=6 looks promising. regartds Peter "Billy Liddel" wrote: "Frank" wrote: Thanks Peter, I am working now with trend lines, you are correct that is the best approach, also found a formula ("definition of a straight line") on the web to normalize well logs: "For a log curve whose unnormalized values are designated Vlog, the normalized values of the curve Vnorm are given by: Vnorm=Rmin + (Rmax - Rmin)(Vlog - Wmin) / (Wmax - Wmin) . Wmin is the value of a specific lithology in each well. Generally, it is near the minimum value for that curve in that interval.Wmax is the value of a different specific lithology in each well. It is usually near the maximum value for that curve in that interval. Parameters Wmin and Wmax are measured using the uncorrected data. Parameters Rmin and Rmax are the regional best estimates of the correct value for the two lithologies at that location, whether they are constant values or are taken from trend surfaces." I will play with both methods and see what gives me the best results (histogram overlay). Do you know of a better (free) statistics graphics add-in for Excel that draws histograms with the proper bell shape and cummulative frequency curve. That Histogram option in Statistics (Excel-add-in) is a bit clumsy and unattractive (vertical bars rather than a curve). Cheers Frank Thanks for you complete reply, I'll look at this in more detail later. With regards to a better chart add-in I don't know off hand, however, I tried creating two charts from the same data in Excel. The first was a normal Column chart, the second was a logarithmic chart, on the custom chart tab. I then deleted the borders, plot and chart backgrounds and all the data series. You end up with just a curved line. thjis you resize and drag over the column chart and tweek the curve until it almost fits. I do not know if this would suffice be its worth a try. regards Peter -- Frank "Billy Liddel" wrote: "Frank" wrote: I have two sets of data (2 very large columns) same type, same source, different data sets. One set is correct the other (that I want to normalize) has a +10 shift in the (arithm.) mean and +8 difference in the Stand. Dev I do the normalization now with histograms by subtracting 10 from the data that I normalize till I get a perfect histogram overlay (bell) How to do this with a statistical function in Excel? I tried STANDARDIZE using the array to normalize for A1 (variable) and constants for A2 and A3 (mean and Stand. Dev. computed from the the coreect data set) but the value that I obtain makes no sense. Anyone can help me with this problem? -- Frank Frank You want something like y = xb + c where y is sales, x temperature and c is the intercept. First, sort you data by temperature and use these formulas; I created range names for the Sales and Temperature. =SLOPE(Sales,Temp) =INTERCEPT(Sales,Temp) then assuming that you enter a value in A15 for estimated temperature you calculate estimated sales with: 20 =A15*slope+Intercept You might want to also use =STEYX(Sales,Temp) to find the Standard Error and another set of calculations on the month if your sales (like ice cream) are seasonally effected. Regards Peter |
Normalize
Frank
I'm not sure that I can help, Don't know anything about minerology, how about a small sample of data with the results that your seeking. By the way, I played around with Excel's charts with a ficticious data set, so that it would produce a normall curve. I then duplicated the set in the next column. In Custom charts I chose column - line on two axis. Then I right clicked on the columns in the chart, chose Format, Options, and reduced the column spacing to nil this rounded the histogram quite nicely. Regards Peter "Frank" wrote: Peter, can you help me with this: I have an intercept (b) and slope (C) from the correct data set. How to generate (correct) my other data set with that slope and intercept?? Thanks Frank -- Frank "Billy Liddel" wrote: A search on google for free charts through up a large number of free chart software. http://www.mylot.tv/c/c.aspx?k=chart...tware&source=6 looks promising. regartds Peter "Billy Liddel" wrote: "Frank" wrote: Thanks Peter, I am working now with trend lines, you are correct that is the best approach, also found a formula ("definition of a straight line") on the web to normalize well logs: "For a log curve whose unnormalized values are designated Vlog, the normalized values of the curve Vnorm are given by: Vnorm=Rmin + (Rmax - Rmin)(Vlog - Wmin) / (Wmax - Wmin) . Wmin is the value of a specific lithology in each well. Generally, it is near the minimum value for that curve in that interval.Wmax is the value of a different specific lithology in each well. It is usually near the maximum value for that curve in that interval. Parameters Wmin and Wmax are measured using the uncorrected data. Parameters Rmin and Rmax are the regional best estimates of the correct value for the two lithologies at that location, whether they are constant values or are taken from trend surfaces." I will play with both methods and see what gives me the best results (histogram overlay). Do you know of a better (free) statistics graphics add-in for Excel that draws histograms with the proper bell shape and cummulative frequency curve. That Histogram option in Statistics (Excel-add-in) is a bit clumsy and unattractive (vertical bars rather than a curve). Cheers Frank Thanks for you complete reply, I'll look at this in more detail later. With regards to a better chart add-in I don't know off hand, however, I tried creating two charts from the same data in Excel. The first was a normal Column chart, the second was a logarithmic chart, on the custom chart tab. I then deleted the borders, plot and chart backgrounds and all the data series. You end up with just a curved line. thjis you resize and drag over the column chart and tweek the curve until it almost fits. I do not know if this would suffice be its worth a try. regards Peter -- Frank "Billy Liddel" wrote: "Frank" wrote: I have two sets of data (2 very large columns) same type, same source, different data sets. One set is correct the other (that I want to normalize) has a +10 shift in the (arithm.) mean and +8 difference in the Stand. Dev I do the normalization now with histograms by subtracting 10 from the data that I normalize till I get a perfect histogram overlay (bell) How to do this with a statistical function in Excel? I tried STANDARDIZE using the array to normalize for A1 (variable) and constants for A2 and A3 (mean and Stand. Dev. computed from the the coreect data set) but the value that I obtain makes no sense. Anyone can help me with this problem? -- Frank Frank You want something like y = xb + c where y is sales, x temperature and c is the intercept. First, sort you data by temperature and use these formulas; I created range names for the Sales and Temperature. =SLOPE(Sales,Temp) =INTERCEPT(Sales,Temp) then assuming that you enter a value in A15 for estimated temperature you calculate estimated sales with: 20 =A15*slope+Intercept You might want to also use =STEYX(Sales,Temp) to find the Standard Error and another set of calculations on the month if your sales (like ice cream) are seasonally effected. Regards Peter |
| All times are GMT +1. The time now is 07:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com