x-y charting and regions

  • Thread starter Thread starter geobob
  • Start date Start date
G

geobob

I would like to utilise Excel charting and VBA to return some value
relating to a region on the graph.

The XY chart is log-log divided into 11 regions. Each region has
unique name.

The points that are displayed on the chart will lie within a region o
the chart.

I would like a cell to return the name of the region/area that the X-
point lies in.

Is this possibl
 
Are the regions as simply defined as Hi-Med-Low Y by Hi-Med-Low X? You
could do something with look up functions and the data on the worksheet.

Suppose your cutoffs are at 10 and 100. I set up this grid in E1:H4

1 10 100
1 A B C
10 D E F
100 G H I

(so if I'm above 100 in X and between 10 and 100 in Y, I'm in region H).
I put my X values in column A and my Y values in column B. For the
point defined by A2:B2, I put this formula into C2:

=INDEX($F$2:$H$4,MATCH(A2,$E$2:$E$4,1),MATCH(B2,$F$1:$H$1,1))

Note the careful placement of the dollar signs. My data range looks
like this, with the formula in C2 copied down the column:

X Y Zone
2 2 A
2 20 B
2 200 C
20 2 D
20 20 E
20 200 F
200 2 G
200 20 H
200 200 I

You could plot your points, then use Rob Bovey's Chart Labeler (a free
addin available at http://appspro.com) to place these zone labels next
to the points.

But that was too simple. Do you need to do this while clicking on a
point? I have some code showing how to identify the clicked point and
how to get its X and Y values. Again, look up functions or other
algorithms can return the region.

http://www.geocities.com/jonpeltier/Excel/Charts/chartvba.html#PointInfo

- Jon
 
Back
Top