Validity checking - finding a threshold

[Clif McIrvin]

Office 2003.

Spreadsheet contains aged test data (specimen created date, test date,
test result, design target ...)

I'd like to use conditional formatting to highlight test results that
fall below a threshold curve, but I don't know how to obtain
intermediate values from the curve.

Known:

Age % of
at design
test target

7 days 70%
14 days 90%
28 days 100%

Does Excel include a function that will give me the percentage of design
target for any arbitrary test age? (well, less than 28 days, at least.)

Thanks in advance!

 

[Jim Cone]

The Forecast function returns linear trend values.
With Age in B6:B8 and Percent in C6:C8 then
=FORECAST(21,C6:C8,B6:B8) returns 92.86 for the 21st day.

However, it also returns 102.14 for the 28th day.
In Excel help, search for Functions | Statistical Functions for others.
'--
Jim Cone
Portland, Oregon USA



"Clif McIrvin"
<[email protected]>
wrote in message
Office 2003.
Spreadsheet contains aged test data (specimen created date, test date,
test result, design target ...)
I'd like to use conditional formatting to highlight test results that
fall below a threshold curve, but I don't know how to obtain
intermediate values from the curve.

Known:

Age % of
at design
test target

7 days 70%
14 days 90%
28 days 100%

Does Excel include a function that will give me the percentage of design
target for any arbitrary test age? (well, less than 28 days, at least.)
Thanks in advance!

 

[Clif McIrvin]

Thank you.

I'll check it out.

The Forecast function returns linear trend values.
With Age in B6:B8 and Percent in C6:C8 then
=FORECAST(21,C6:C8,B6:B8) returns 92.86 for the 21st day.

However, it also returns 102.14 for the 28th day.
In Excel help, search for Functions | Statistical Functions for
others.
'--
Jim Cone
Portland, Oregon USA



"Clif McIrvin"
<[email protected]>
wrote in message
Office 2003.
Spreadsheet contains aged test data (specimen created date, test date,
test result, design target ...)
I'd like to use conditional formatting to highlight test results that
fall below a threshold curve, but I don't know how to obtain
intermediate values from the curve.

Known:

Age % of
at design
test target

7 days 70%
14 days 90%
28 days 100%

Does Excel include a function that will give me the percentage of
design
target for any arbitrary test age? (well, less than 28 days, at
least.)
Thanks in advance!

 

[Clif McIrvin]

Pretty much all I found in the documentation was either Forecast / Trend
(linear), and a plethora of statistical functions whose terminology are
outside of the scope of my knowledge (and available time to investigate
/ research is more limited than I like).

GROWTH seemed promising, because it referenced "exponential" in the help
file; but it didn't follow the polynomial curve either.

For my purposes, I am thinking to simply manually create an array of 56
elements and index into the array to retrieve the needed threshold value
rather than attempt to find or define the relevant polynomial function.

My choice of 56 is arbitrary, but should be workable because a) it
covers in excess of 95% of all cases and b) the curve is rapidly
approaching flat by 56 days.

Thanks again for the pointer!

 

[Clif McIrvin]

Found the answer I was looking for .....

Turned out to be a logarithmic equation, not a polynomial like I was
thinking.

Not being familiar with statistical analysis, I may have simply
overlooked the relevant formula(s) out of my ignorance.

How I found my formula was to graph my known points then play around
with Excel's trending graphs until I found the best match -- then I just
asked Excel to display the formula on the chart.

Not exactly sure how I stumbled onto that process ... playing with the
chart and noticed the trending tab, I think.

Jim, thanks again for helping me along the path!

 

[Jim Cone]

Sounds like you did most of the work.
Glad it worked out.
'--
Jim Cone



"Clif McIrvin"
<[email protected]>
wrote in message
Found the answer I was looking for .....
Turned out to be a logarithmic equation, not a polynomial like I was
thinking.
Not being familiar with statistical analysis, I may have simply
overlooked the relevant formula(s) out of my ignorance.
How I found my formula was to graph my known points then play around
with Excel's trending graphs until I found the best match -- then I just
asked Excel to display the formula on the chart.
Not exactly sure how I stumbled onto that process ... playing with the
chart and noticed the trending tab, I think.
Jim, thanks again for helping me along the path!
--
Clif



"Jim Cone"