Scatter Chart Quadrants

[sdoppke]

Hi there everyone, is there a way to return what quadrant a value landed in a scatter chart (i.e. 1,2,3,or 4)

Here is an example of my chart:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64 align=right>2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=64 align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"> *</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 align=right>4</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl67 align=right>3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR></TBODY></TABLE>

So using the two values (x and Y) to place the mark on the scatter plot (in this case quadrant 1), it there a way to determine what quadrant the value landed in (other than visually)?


Thanks a ton in advacne for any help

sd

 

[sdoppke]

The light dawns.

Quadrants in the Cartesian coordinate system are defined by the axes (the x axis (y=0) and the y axis (x=0)), not by the portion of the plane you're looking at.

If you want to know where it falls in your plot area rectangle, based on the max and min x and y values,

=LOOKUP(3*SIGN(y - (maxY-minY)/2) + SIGN(x - (maxX-minX)/2), {-4,-3,-2,-1,2,3,4}, {3,0,4,0,2,0,1})

WOW thank you sooo much for sticking with me that works perfect!! I do have one last question. Is there a way to modify this to reflect uneven quadrants? i.e.
<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 20.25pt" height=27><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 20.25pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #ece9d8" class=xl76 height=27 width=64> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl77 width=64> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl78 width=64> </TD></TR><TR style="HEIGHT: 20.25pt" height=27><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 20.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl79 height=27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl80> </TD></TR><TR style="HEIGHT: 20.25pt" height=27><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 20.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl81 height=27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl82> </TD></TR><TR style="HEIGHT: 20.25pt" height=27><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 20.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl79 height=27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl80> </TD></TR><TR style="HEIGHT: 20.25pt" height=27><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 20.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl79 height=27> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl80> </TD></TR><TR style="HEIGHT: 21pt" height=28><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 21pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl83 height=28> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl85> </TD></TR></TBODY></TABLE>

 

[shg]

I don't know what those crossed lines represent. If they are the lines y=0 and x=0, then we're back to my first answer.

 

[sdoppke]

I don't know what those crossed lines represent. If they are the lines y=0 and x=0, then we're back to my first answer.

Im sorry I should have been more clear. In a scenario where we have:

x Min of 35 and x Max of 80000
y Min of 0 and Y Max of 90

if all quadrants were equal those lines would be at the mid point of each max (45 and 40000).

Can we adjust your last formula to allow for unequal quadrants. Maybe the intersecting lines would be at 70 and 65000?

Hope that was a bit more clear.

sd

 

[shg]

I still don't know that those crossing lines are in your last post.

 

[sdoppke]

I still don't know that those crossing lines are in your last post.

They represent my scatter chart quadrants (in this case, an unequal one)

 

[shg]

Right.

A horizontal line is a line with constant y; what's yours?

A vertical line is a line with constant x; what's yours?

Whatever they are, those are the values you subtract.

 

[sdoppke]

Right.

A horizontal line is a line with constant y; what's yours?

A vertical line is a line with constant x; what's yours?

Whatever they are, those are the values you subtract.

I have been playing with this ever since your last post and im getting beat.

My vertical line is 28808
My horizontal line is 37

my formula is

=LOOKUP(3*SIGN(19.45 - 37/2) + SIGN(18689 - 28808/2), {-4,-3,-2,-1,2,3,4}, {3,0,4,0,2,0,1})

which gives me a 1, but in actuallity its a 3.

What you said made sense earlier, but somehow im still not getting it?

sd

 

[shg]

Don't divide by 2.

 

[sdoppke]

Don't divide by 2.

Ohhhh you totaly rock!!

Thanks so much for taking the time!!

sd

 

[shg]

You're welcome, good luck.