Normalize

[Guest]

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?

 

[Guest]

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

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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 software&source=6 looks
promising.

regartds
Peter

 

[Guest]

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

 

[Guest]

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