Scatter Chart Quadrants

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
647
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 :)
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
You have quadrants 3 and 4 reversed.

One way:

Code:
      A- B- C -----------------------------------D------------------------------------
  1   x  y  Q                                                                         
  2    1  1 1 C2: =LOOKUP(3*SIGN(B2) + SIGN(A2), {-4,-3,-2,-1,2,3,4}, {3,0,4,0,2,0,1})
  3   -1  1 2                                                                         
  4   -1 -1 3                                                                         
  5    1 -1 4
 
Upvote 0
You have quadrants 3 and 4 reversed.

One way:

Code:
      A- B- C -----------------------------------D------------------------------------
  1   x  y  Q                                                                         
  2    1  1 1 C2: =LOOKUP(3*SIGN(B2) + SIGN(A2), {-4,-3,-2,-1,2,3,4}, {3,0,4,0,2,0,1})
  3   -1  1 2                                                                         
  4   -1 -1 3                                                                         
  5    1 -1 4


Thanks a ton for the reply. Im not sure I understand how to use this. Here is a small sample of my x and y values:


<TABLE style="WIDTH: 447px; BORDER-COLLAPSE: collapse; HEIGHT: 111px" border=0 cellSpacing=0 cellPadding=0 width=447><COLGROUP><COL style="WIDTH: 161pt; mso-width-source: userset; mso-width-alt: 7862" width=215><COL style="WIDTH: 174pt; mso-width-source: userset; mso-width-alt: 8484" width=232><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 161pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 width=215 align=right>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 174pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=232 align=right>Y</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 align=right>42909.7924</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>34.35</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 align=right>18689.22045</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>19.45</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 align=right>63591.12077</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>63.2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 align=right>17046.61686</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>22.05</TD></TR></TBODY></TABLE>


Am I miss understanging something with the 1's and -1's?
 
Upvote 0
The 1 and -1 were just examples of x and y values. Put the posted formula in C2 and copy down.
 
Upvote 0
The 1 and -1 were just examples of x and y values. Put the posted formula in C2 and copy down.


Thats what I did, and I got all 1's. Sorry for being dense. would you be albe to esplain what the formula does? Dont i have to set up the Major and Minor Values somewhere(the range)?

thanks for hanging in there with me.

sd
 
Upvote 0
Your data is all in the first quadrant (x and y are both positive)
Code:
      -----A----- --B-- C -----------------------------------D------------------------------------
  1        x        y   Q                                                                         
  2    42909.7924 34.35 1 C2: =LOOKUP(3*SIGN(B2) + SIGN(A2), {-4,-3,-2,-1,2,3,4}, {3,0,4,0,2,0,1})
  3   18689.22045 19.45 1                                                                         
  4   63591.12077  63.2 1                                                                         
  5   17046.61686 22.05 1
The formula generates a single number between -4 and 4 based on the sign of x and y. The lookup function matches that in the first array, and returns the corresponding value in the second.
 
Last edited:
Upvote 0
Your data is all in the first quadrant (x and y are both positive)
Code:
      -----A----- --B-- C -----------------------------------D------------------------------------
  1        x        y   Q                                                                         
  2    42909.7924 34.35 1 C2: =LOOKUP(3*SIGN(B2) + SIGN(A2), {-4,-3,-2,-1,2,3,4}, {3,0,4,0,2,0,1})
  3   18689.22045 19.45 1                                                                         
  4   63591.12077  63.2 1                                                                         
  5   17046.61686 22.05 1
The formula generates a single number between -4 and 4 based on the sign of x and y. The lookup function matches that in the first array, and returns the corresponding value in the second.


OK that makes sence. My original question was, is there a way to identify what quadrant my scatter chart is placing the data based on a the x and y values (I do have min and max amounts, which should make it easier)?


sd
 
Upvote 0
Each point appears in one of four quadrants (or no quadrant, if either x or y is zero) based on the signs of the x and y values.

Maybe I don't understand your question ...
 
Last edited:
Upvote 0
Each point appears in one of four quadrants (or no quadrant, if either x or y is zero) based on the signs of the x and y values.

Maybe I don't understand your question ...


Im sure it either they way im explaining it, or i just dont understand you have already given me the answer :)

These two sets of values:
<TABLE style="WIDTH: 124pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=165><COLGROUP><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 75pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl75 height=20 width=100>x</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 49pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl75 width=65>y</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: #ece9d8" class=xl74 height=20>21425.06</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl74>18.10</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: #ece9d8" class=xl74 height=20>59784.60</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl74>70.70</TD></TR></TBODY></TABLE>

land in two different quadrants of my scatter chart. The top set lands in the bottom, left corner of my chart(quad 4), and the top one lands in the top, left of my chart(quad 1). assuming 0 starts from the bottom left corner of the chart and the numbers increase outwards.

I am hoping to use your formula to tell me where (after knowing the min and max values) the sets of values (x and Y) will land in the scatter chart (which quadrant)

Hope that helps with my aim. Again thanks for hangin in there with me. :)


sd
 
Upvote 0
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})
 
Upvote 0

Forum statistics

Threads
1,224,534
Messages
6,179,390
Members
452,909
Latest member
VickiS

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top