Using X,Y coords to assign grid area

[Wolfmanfafa]

I have a list of x and y values for a 600x600 coordinate plane. I want the plane divided up into 9 equal grids (200x200). Is there a formula to return which grid the x,y coordinate exists in? As of now, I think I can do it using several IF AND formulas in multiple cells. (ie.
IF (A2>0 AND A2<201,”zone 1”)
IF (A2>200 AND A2<401,”zone 2”)

Then do another one for B (y value) in a different column.
Then write a formula where it uses both values to determine which grid it’s in.

I’d like to do it with one simple formula if possible.

Thanks in advance!

 

[wideboydixon]

Book1
	A	B	C
1	x	y	zone
2	1	200	zone 1
3	201	150	zone 2
4	567	199	zone 3
5	55	201	zone 4
Sheet4

Cell Formulas
Range	Formula
C2	="zone "&INT(($A2-1)/200)+INT(($B2-1)/200)*3+1

Something like that?

WBD

 

[Wolfmanfafa]

Wow!! That is amazing! I would’ve never come up with that. Thank you so much. And what a fast response.

That will work perfect!
I am guessing that the /200 would make it difficult to subdivide these zones into blocks of 50. Is there an easy way to accomplish that? If not, don’t trouble yourself. I already greatly appreciate the swift and awesome answer.
Also, If the cells are blank it returns “zone -3”. I can always jest delete blankncells but just curious if there is an easy way to correct that like putting the formula into an IF, THEN statement to only use numbers that return as positive?

Thanks again! You were super helpful!

 

[wideboydixon]

=IF(AND($A2>=1,$A2<=600,$B2>=1,$B2<=600),"zone "&INT(($A2-1)/200)+INT(($B2-1)/200)*3+1,"")

That will ensure the coordinates are in the right range. Not sure what you mean about sub-dividing into 50s.

WBD

 

[Wolfmanfafa]

Let me explain to you my overall plan. I have a 600x600 grid in a game. The grid is filled with resource islands and player’s bases.

I’ve entered in 250 coordinates of high level resource islands throughout the grid.

With your help (thank you), I currently have everything divided into 200x200 zones.

Instead of subdividing into 50x50 blocks, the plan is to enter your coordinates and select a radius from that coordinate and have it only show those mines and bases within the specified range of your coordinates. (The resource islands are fixed but you can move your base.) I am making something for my team so they can enter their coordinates and get all the abandoned bases and high level mines that are near them.

I also created a scatter plot. The mines are blue dots. Once I create the table of abandoned bases, I’d like those to show up on the same scatter plot in red.

I haven’t had a chance to get to the last part yet. I’m doing it in phases as I have time.