Hi there!
We are currently working on a project that involves the making of a
scatter/line chart. We will be given a value (call it "Average") that
will be the center of the y-axis for each chart. The x-axis will be the
date that coordinates to each value to be compared to Average. The goal
is to make this chart as automated as possible - another user inputs
data and voila! the chart is made, formatted, etc. The problem: This
Average value will change periodically.
The chart will then have to have a minimum of Average - 0.20 and a
maximum of Average + 0.20, to compare the values from each date.
Additionally, when the value is below or above Average +/- 0.15, there
needs to be some conditional formatting (i.e., the dot turning red).
Is there a way to input the range of the axes in reference to a cell?
Or, is there some way around this?
Any help will be appreciated, Thanks in advance.

Nikki

I'm assuming that the auto scale feature is not being helpful? If this
is the case a solution may need some vba for axis problem.

With regard to the conditional formatting of your data points the
easiest solution is to have two points (one formatted for above average
and one for below) for each data pair. Reference each point to a pair
of cells with formulas referring to the data which show the data if it
complies with the criteria for that formatting ie only one of
referenced pair of cells for each data point will actually values for
each of actual data points you want to show.

Let me know if this works.

Nikki wrote:
Hi there!
We are currently working on a project that involves the making of a
scatter/line chart. We will be given a value (call it "Average") that
will be the center of the y-axis for each chart. The x-axis will be the
date that coordinates to each value to be compared to Average. The goal
is to make this chart as automated as possible - another user inputs
data and voila! the chart is made, formatted, etc. The problem: This
Average value will change periodically.
The chart will then have to have a minimum of Average - 0.20 and a
maximum of Average + 0.20, to compare the values from each date.
Additionally, when the value is below or above Average +/- 0.15, there
needs to be some conditional formatting (i.e., the dot turning red).
Is there a way to input the range of the axes in reference to a cell?
Or, is there some way around this?
Any help will be appreciated, Thanks in advance.

Nikki

Mat N wrote:
I'm assuming that the auto scale feature is not being helpful? If this
is the case a solution may need some vba for axis problem.

The auto scale is dependent on given values. Let me explain further:
The Average is calculated from the mean of the first 5 days of the
data, then made to be the center of the y-axis. The values from those 5
days, plus the days succeeding are then plotted to compare to this
Average value.

The value changes each month (sooner?) and a new graph will need to be
constructed, so it would be optimal if the graph could be formatted
loosely and then have the Average value as the center of the y-axis
once it is known.

Is there a way other than VBA to accomplish this? I have not
experimented with using VBA yet, but I guess there's no time like today
to start. =)

With regard to the conditional formatting of your data points the
easiest solution is to have two points (one formatted for above average
and one for below) for each data pair. Reference each point to a pair
of cells with formulas referring to the data which show the data if it
complies with the criteria for that formatting ie only one of
referenced pair of cells for each data point will actually values for
each of actual data points you want to show.

Can conditional formatting apply to an individual point on the chart?
If a value is below or above a certain value, I would like the
point/symbol on the chart to turn red, for example.

Let me know if this works.

Thanks for your help so far.
Nikki

Nikki,

Perhaps you could plot your values as their difference from the average
and use the auto scale (or if you are confident they'll be in a certain
range just have a fixed scale). Then hide this axis (or just the
values) and have a dummy axis or just floating values next to the axis
to indicate the max, minimum or average value.

VBA is not hard but it is best to avoid as there are frequently
circumstances which are not easy to anticipate which cause the code not
to work (eg the user doesn't select enable macros when opening the
file). If you want to give it a go just record a macro and check out
what the code looks like.

As far as I know there is no conditional formatting option for points
plotted on a chart and my original suggestion is a work around (and it
does not involve VBA).

Nikki wrote:
Mat N wrote:
I'm assuming that the auto scale feature is not being helpful? If this
is the case a solution may need some vba for axis problem.

The auto scale is dependent on given values. Let me explain further:
The Average is calculated from the mean of the first 5 days of the
data, then made to be the center of the y-axis. The values from those 5
days, plus the days succeeding are then plotted to compare to this
Average value.

The value changes each month (sooner?) and a new graph will need to be
constructed, so it would be optimal if the graph could be formatted
loosely and then have the Average value as the center of the y-axis
once it is known.

Is there a way other than VBA to accomplish this? I have not
experimented with using VBA yet, but I guess there's no time like today
to start. =)

With regard to the conditional formatting of your data points the
easiest solution is to have two points (one formatted for above average
and one for below) for each data pair. Reference each point to a pair
of cells with formulas referring to the data which show the data if it
complies with the criteria for that formatting ie only one of
referenced pair of cells for each data point will actually values for
each of actual data points you want to show.

Can conditional formatting apply to an individual point on the chart?
If a value is below or above a certain value, I would like the
point/symbol on the chart to turn red, for example.

Let me know if this works.

Thanks for your help so far.
Nikki

Mat N wrote:
Nikki,

Perhaps you could plot your values as their difference from the average
and use the auto scale (or if you are confident they'll be in a certain
range just have a fixed scale). Then hide this axis (or just the
values) and have a dummy axis or just floating values next to the axis
to indicate the max, minimum or average value.

This works good, so far. However, we would like to use a dynamic chart
(i.e. one that expands as we add data), as referenced at
http://peltiertech.com/Excel/Charts/...umnChart1.html. The
problem with this is that it uses the COUNTA function, and we are
including cells that have formulas included. ISNUMBER(in this cell), do
this, "") and the COUNTA function is including these cells into the
nonblank cell calculations. Is there another function we are
overlooking to accomplish what we're trying to do?

Thanks again for any help.