Help an Excel novice with a graph formula?
 

I have worked some with MS Access, much less in Excel and really am not very
math oriented but I need help. Not much on graphing in the Access Forum but
several suggestions to use Excel because it is much more powerful in this
area. I have numerical test values on the Y axis matched with numerical
values representing age in hours on the X axis, plotting a line graph in
Access which represent a limit.

I need to compare new data sets on a daily basis with this line graph
identifying each set as either above or below, Less Than or Greater Than the
limit set my this graph. Is there a formula or method available or that can
be created or viewed in Excel?

Is this possible? Can someone point me to an example or maybe help walk me
through? Thank You.

Hi Jeff,

As I read your post, it seems that the comparison you're after is entirely
feasible. But how do you want to decide whether new data sets are above or
below, Less Than or Greater Than a limit that's been set by your graph? On
the basis of a discrete point comparison? [e.g., Average(OldSet)
Average(NewSet)]? In that case, a chart/graph wouldn't be needed. On some
other basis? This sounds like an SPC application.

C^2
Conrad Carlberg

"Jeff C" wrote in message
...
I have worked some with MS Access, much less in Excel and really am not
very
math oriented but I need help. Not much on graphing in the Access Forum
but
several suggestions to use Excel because it is much more powerful in this
area. I have numerical test values on the Y axis matched with numerical
values representing age in hours on the X axis, plotting a line graph in
Access which represent a limit.

I need to compare new data sets on a daily basis with this line graph
identifying each set as either above or below, Less Than or Greater Than
the
limit set my this graph. Is there a formula or method available or that
can
be created or viewed in Excel?

Is this possible? Can someone point me to an example or maybe help walk
me
through? Thank You.

The graph simply plots a numerical test value with an age in hours and the
line graph represents a percentile ranking. I just want to flag each new
data pair as being either above or below the line. I am just too much of a
math novice to know the correct term that I am looking for in this
description and way too much of a novice to know how to solve the problem.
Thanks for trying to help. I can offer more detail if you would like.

"Conrad Carlberg" wrote:

Hi Jeff,

As I read your post, it seems that the comparison you're after is entirely
feasible. But how do you want to decide whether new data sets are above or
below, Less Than or Greater Than a limit that's been set by your graph? On
the basis of a discrete point comparison? [e.g., Average(OldSet)
Average(NewSet)]? In that case, a chart/graph wouldn't be needed. On some
other basis? This sounds like an SPC application.

C^2
Conrad Carlberg

"Jeff C" wrote in message
...
I have worked some with MS Access, much less in Excel and really am not
very
math oriented but I need help. Not much on graphing in the Access Forum
but
several suggestions to use Excel because it is much more powerful in this
area. I have numerical test values on the Y axis matched with numerical
values representing age in hours on the X axis, plotting a line graph in
Access which represent a limit.

I need to compare new data sets on a daily basis with this line graph
identifying each set as either above or below, Less Than or Greater Than
the
limit set my this graph. Is there a formula or method available or that
can
be created or viewed in Excel?

Is this possible? Can someone point me to an example or maybe help walk
me
through? Thank You.

Hi Jeff,

Now it does seem that you'll want a chart instead of a simple numeric
comparison. Yes, more detail might be helpful, but in the meantime:

An Excel chart can display more than just one data set (where "data set"
means, in the terminology you're using, a series of age in hours paired with
a percentile ranking). So, a chart could display two data sets. Both data
sets would have the same column for the X axis, but two different columns
for the Y axis.

1. In (say) Column A, age in hours, for the X axis.
2. In (say) Column B, percentile ranking, for the Y axis.
3. In (say) Column C, test value for the new observation, as a percentile,
also on the Y axis.

You would have a value in Column A for each data point. There would be a
value in Column B or Column C, but not both, depending on whether it's an
old, comparison observation, or a new one.

I guess that if you need to compare data sets on a daily basis, while each
observation is made on an hourly basis, that you'll need to update/refresh
the data every 24 hours. In that case, you're probably better off using
Excel rather than Access -- not due to its greater power, but due to its
greater convenience.

If you want to provide more detail, please attach an Excel workbook showing
what you've done so far to a reply to this message.

C^2
Conrad Carlberg

"Jeff C" wrote in message
...
The graph simply plots a numerical test value with an age in hours and the
line graph represents a percentile ranking. I just want to flag each new
data pair as being either above or below the line. I am just too much of
a
math novice to know the correct term that I am looking for in this
description and way too much of a novice to know how to solve the problem.
Thanks for trying to help. I can offer more detail if you would like.

"Conrad Carlberg" wrote:

Hi Jeff,

As I read your post, it seems that the comparison you're after is
entirely
feasible. But how do you want to decide whether new data sets are above
or
below, Less Than or Greater Than a limit that's been set by your graph?
On
the basis of a discrete point comparison? [e.g., Average(OldSet)
Average(NewSet)]? In that case, a chart/graph wouldn't be needed. On
some
other basis? This sounds like an SPC application.

C^2
Conrad Carlberg

"Jeff C" wrote in message
...
I have worked some with MS Access, much less in Excel and really am
not
very
math oriented but I need help. Not much on graphing in the Access
Forum
but
several suggestions to use Excel because it is much more powerful in
this
area. I have numerical test values on the Y axis matched with
numerical
values representing age in hours on the X axis, plotting a line graph
in
Access which represent a limit.

I need to compare new data sets on a daily basis with this line graph
identifying each set as either above or below, Less Than or Greater
Than
the
limit set my this graph. Is there a formula or method available or
that
can
be created or viewed in Excel?

Is this possible? Can someone point me to an example or maybe help
walk
me
through? Thank You.

5.9 20
6.4 24
7 28
9 36
9.9 40
10.1 44
10.8 48
12.7 60
13.4 72
14.7 84
15.2 96
15.8 120


Simply, lab values above left charted against age in hours on right. The
resulting plotted graph represents a percentile ranking across the X axis and
below the plotted line of lab values. If this was on a board and pins were
used to identify the location of the points identified by subsequent data, I
would want to flag all those values as either above or below the plotted
graph represented by the above data. Thank You for being patient with my
ignorance. I could not see a way to attach a workbook or worksheet to this
reply.

"Conrad Carlberg" wrote:

Hi Jeff,

Now it does seem that you'll want a chart instead of a simple numeric
comparison. Yes, more detail might be helpful, but in the meantime:

An Excel chart can display more than just one data set (where "data set"
means, in the terminology you're using, a series of age in hours paired with
a percentile ranking). So, a chart could display two data sets. Both data
sets would have the same column for the X axis, but two different columns
for the Y axis.

1. In (say) Column A, age in hours, for the X axis.
2. In (say) Column B, percentile ranking, for the Y axis.
3. In (say) Column C, test value for the new observation, as a percentile,
also on the Y axis.

You would have a value in Column A for each data point. There would be a
value in Column B or Column C, but not both, depending on whether it's an
old, comparison observation, or a new one.

I guess that if you need to compare data sets on a daily basis, while each
observation is made on an hourly basis, that you'll need to update/refresh
the data every 24 hours. In that case, you're probably better off using
Excel rather than Access -- not due to its greater power, but due to its
greater convenience.

If you want to provide more detail, please attach an Excel workbook showing
what you've done so far to a reply to this message.

C^2
Conrad Carlberg

"Jeff C" wrote in message
...
The graph simply plots a numerical test value with an age in hours and the
line graph represents a percentile ranking. I just want to flag each new
data pair as being either above or below the line. I am just too much of
a
math novice to know the correct term that I am looking for in this
description and way too much of a novice to know how to solve the problem.
Thanks for trying to help. I can offer more detail if you would like.

"Conrad Carlberg" wrote:

Hi Jeff,

As I read your post, it seems that the comparison you're after is
entirely
feasible. But how do you want to decide whether new data sets are above
or
below, Less Than or Greater Than a limit that's been set by your graph?
On
the basis of a discrete point comparison? [e.g., Average(OldSet)
Average(NewSet)]? In that case, a chart/graph wouldn't be needed. On
some
other basis? This sounds like an SPC application.

C^2
Conrad Carlberg

"Jeff C" wrote in message
...
I have worked some with MS Access, much less in Excel and really am
not
very
math oriented but I need help. Not much on graphing in the Access
Forum
but
several suggestions to use Excel because it is much more powerful in
this
area. I have numerical test values on the Y axis matched with
numerical
values representing age in hours on the X axis, plotting a line graph
in
Access which represent a limit.

I need to compare new data sets on a daily basis with this line graph
identifying each set as either above or below, Less Than or Greater
Than
the
limit set my this graph. Is there a formula or method available or
that
can
be created or viewed in Excel?

Is this possible? Can someone point me to an example or maybe help
walk
me
through? Thank You.

What you provided works just fine. Suppose that you have these data:

5.9 20
6.4 24
7 28
9 36
9.9 40
10.1 44
10.8 48
12.7 60
13.4 72
14.7 84
15.2 96
15.8 120
5.9 25
6.4 29
7 33
9 41
9.9 45
10.1 49
10.8 53


in columns A, B and C, and in rows 1 through 19. Begin by selecting the
range A1:C19. Choose Insert | Chart, or click the Chart Wizard button
on Excel's main toolbar. In Step 1 of the Chart Wizard, choose
XY(Scatter) Chart, and click Next. In Step 2 of the Chart Wizard, you
should see two tabs, one labeled "Data Range" and one labeled "Series."


Click the "Series" tab. You'll see a listbox labeled "Series" and it
will have Series1 and Series2 in it. There are another two boxes on
that tab, one that is labeled X Values and one labeled Y Values. Both
Series1 and Series2 should have as their X values the range $A$1:A$19.
Series1 should have $B$1:B$19 as its Y values, and Series2 should have
$C$1:C$19 as its Y values. Click Finish, and I think you'll have the
chart that you're looking for.

C^2
Conrad Carlberg

The chart worked just like you described but how would I identify those
values as above or below the original curve without the graphics? I may have
been approaching the problem incorrectly but the chart is the only starting
point I have. Completing your chart gave me values all falling just
underneath the original graph. This put them under the 75th percentile of
this nomograph. I need to identify each value as underneath or above the
original curve or just identify those values on one side of the curve knowing
if not that side they would lie on the other side. The other way I am seeing
the problem is that each new value is either <, =, or the orginal line, or
values in the line. Excel extrapolates the line between the values I have
entered with infinity values between which I cannot begin to calculate.

Am I making sense or going around in a circle? Thanks Conrad


"Conrad Carlberg" wrote:

What you provided works just fine. Suppose that you have these data:

5.9 20
6.4 24
7 28
9 36
9.9 40
10.1 44
10.8 48
12.7 60
13.4 72
14.7 84
15.2 96
15.8 120
5.9 25
6.4 29
7 33
9 41
9.9 45
10.1 49
10.8 53


in columns A, B and C, and in rows 1 through 19. Begin by selecting the
range A1:C19. Choose Insert | Chart, or click the Chart Wizard button
on Excel's main toolbar. In Step 1 of the Chart Wizard, choose
XY(Scatter) Chart, and click Next. In Step 2 of the Chart Wizard, you
should see two tabs, one labeled "Data Range" and one labeled "Series."


Click the "Series" tab. You'll see a listbox labeled "Series" and it
will have Series1 and Series2 in it. There are another two boxes on
that tab, one that is labeled X Values and one labeled Y Values. Both
Series1 and Series2 should have as their X values the range $A$1:A$19.
Series1 should have $B$1:B$19 as its Y values, and Series2 should have
$C$1:C$19 as its Y values. Click Finish, and I think you'll have the
chart that you're looking for.

C^2
Conrad Carlberg

Maybe I can put the question another way? Can the first series of data be
represented in such a way that subsequent data can be either above or below
the limit, = < the limit, or true false? If so, is Excel capable of
generating the answer? Thanks as always.


"Jeff C" wrote:

The chart worked just like you described but how would I identify those
values as above or below the original curve without the graphics? I may have
been approaching the problem incorrectly but the chart is the only starting
point I have. Completing your chart gave me values all falling just
underneath the original graph. This put them under the 75th percentile of
this nomograph. I need to identify each value as underneath or above the
original curve or just identify those values on one side of the curve knowing
if not that side they would lie on the other side. The other way I am seeing
the problem is that each new value is either <, =, or the orginal line, or
values in the line. Excel extrapolates the line between the values I have
entered with infinity values between which I cannot begin to calculate.

Am I making sense or going around in a circle? Thanks Conrad


"Conrad Carlberg" wrote:

What you provided works just fine. Suppose that you have these data:

5.9 20
6.4 24
7 28
9 36
9.9 40
10.1 44
10.8 48
12.7 60
13.4 72
14.7 84
15.2 96
15.8 120
5.9 25
6.4 29
7 33
9 41
9.9 45
10.1 49
10.8 53


in columns A, B and C, and in rows 1 through 19. Begin by selecting the
range A1:C19. Choose Insert | Chart, or click the Chart Wizard button
on Excel's main toolbar. In Step 1 of the Chart Wizard, choose
XY(Scatter) Chart, and click Next. In Step 2 of the Chart Wizard, you
should see two tabs, one labeled "Data Range" and one labeled "Series."


Click the "Series" tab. You'll see a listbox labeled "Series" and it
will have Series1 and Series2 in it. There are another two boxes on
that tab, one that is labeled X Values and one labeled Y Values. Both
Series1 and Series2 should have as their X values the range $A$1:A$19.
Series1 should have $B$1:B$19 as its Y values, and Series2 should have
$C$1:C$19 as its Y values. Click Finish, and I think you'll have the
chart that you're looking for.

C^2
Conrad Carlberg

Hi Jeff,

Sorry, I've been away for a couple of days. I'm a little surprised that
someone else didn't jump in; that's the way I remember it happening
when these newsgroups were Compuserve forums. I hope that the following
information will be useful.

The data I sent you probably didn't paste properly into an Excel
worksheet. I intended the first 12 rows to go into column A (values 5.9
through 15.8) and column B (values 20 through 120). Then, another seven
rows to go into column A (values 5.9 through 10.8) and column C (values
25 through 53). [BTW, Data | Text To Columns is of no particular help
here.] If you now select all those values, in cells A1:C19, and create
an XY(Scatter) Chart, Excel will show you two data series in the chart.
You should see seven values in the second data series that are
numerically greater on the vertical, Y axis, than are the associated
seven values in the first data series, where that association is
defined by their equality on the variable in column A.

Ye gods, this is almost impossible to describe verbally, and yet you'd
see it immediately if we were looking at the worksheet and chart.

Thanks Conrad: The graph works just as you say, but for a report on
approximately 2000 values at a time I needed a solution that will rate each
value relative to the static values, either +/-, true,false etc. ie;

iif([livelabValue]<[staticLabValue],yes,no) where
[hourLiveLabValue]=[hourstaticLabValue]

In your absence I had to rethink and approached the problem differently in
Access. By manually estimating the missing values in the static data set I
came up with a solution.

Thank You very much......

"Conrad Carlberg" wrote:

Hi Jeff,

Sorry, I've been away for a couple of days. I'm a little surprised that
someone else didn't jump in; that's the way I remember it happening
when these newsgroups were Compuserve forums. I hope that the following
information will be useful.

The data I sent you probably didn't paste properly into an Excel
worksheet. I intended the first 12 rows to go into column A (values 5.9
through 15.8) and column B (values 20 through 120). Then, another seven
rows to go into column A (values 5.9 through 10.8) and column C (values
25 through 53). [BTW, Data | Text To Columns is of no particular help
here.] If you now select all those values, in cells A1:C19, and create
an XY(Scatter) Chart, Excel will show you two data series in the chart.
You should see seven values in the second data series that are
numerically greater on the vertical, Y axis, than are the associated
seven values in the first data series, where that association is
defined by their equality on the variable in column A.

Ye gods, this is almost impossible to describe verbally, and yet you'd
see it immediately if we were looking at the worksheet and chart.