Conditional data point formatting with variable conditions?

  • Thread starter Thread starter Peter Frank
  • Start date Start date
P

Peter Frank

Hi,

I checked out Jon Peltier's great example/tutorial on conditional
chart formatting. I would like to conditionally format data points of
a scatter plot but the difficult thing about it is that the min-max
conditions are variable.
I have two data columns, x and y, yielding the scatter plot (Side
note: In the thread "Diagonal line in scatter plot?" I described the
data in a bit more detail.). The conditions for giving a data point a
certain color are not supposed to be fixed values but instead the
ratios of x and y. Let's say the ratio of x any y is above 1.5 or
below 0.7, then I would like the data points to be colored red instead
of black. My problem is that I can't figure out how to formulate that
condition so that it applies to each x-y pair with its own values and
not just one specific x-y pair.

Can such a condition be set up in Excel?

Peter
 
Peter,

The principle is exactly the same as described in Jon's site. Say yo
have your data in the Range A2:B10, with x values in A and y values i
B.

Write a formula in cell C2: = if(or(a2/b2>1.5,a2/b2<0.7),a2,na())
Write a formula in cell D2: = if(and(a2/b<=1.5,a2/b2>=0.7),a2,na())

The series in column C will represent the set of points that ar
outside the range (0.7,1.5) for x/y.

Now you can plot columns A,C & D in a scatter plot. You can then forma
the two series C & D individually as you like.

Hope this helps,
Regards,
Leni
 
LeninVMS said:
Peter,

The principle is exactly the same as described in Jon's site. Say you
have your data in the Range A2:B10, with x values in A and y values in
B.

Write a formula in cell C2: = if(or(a2/b2>1.5,a2/b2<0.7),a2,na())
Write a formula in cell D2: = if(and(a2/b2<=1.5,a2/b2>=0.7),a2,na())

The series in column C will represent the set of points that are
outside the range (0.7,1.5) for x/y.

Now you can plot columns A,C & D in a scatter plot. You can then format
the two series C & D individually as you like.
Click to expand...

That is the solution I was looking for. Thanks. I think I could have
come up with the formula for column C but not for D.
Besides, my subject line and my description were not exactly right
because the conditions are not variable, they are fixed. They are just
not based on the raw data but a transformation of those.

Peter
 
LeninVMS said:
Peter,

The principle is exactly the same as described in Jon's site. Say you
have your data in the Range A2:B10, with x values in A and y values in
B.

Write a formula in cell C2: = if(or(a2/b2>1.5,a2/b2<0.7),a2,na())
Write a formula in cell D2: = if(and(a2/b2<=1.5,a2/b2>=0.7),a2,na())

The series in column C will represent the set of points that are
outside the range (0.7,1.5) for x/y.

Now you can plot columns A,C & D in a scatter plot. You can then format
the two series C & D individually as you like.
Click to expand...

That is the solution I was looking for. Thanks. I think I could have
come up with the formula for column C but not for D.
Besides, my subject line and my description were not exactly right
because the conditions are not variable, they are fixed. They are just
not based on the raw data but a transformation of those.

Peter
 
Back
Top