X and Y coordinates and Zones in between them

I have a long list of actions.
Each action has an X coordinate, and a Y coordinate.
The pitch on which these actions occur is divided into 15 zones.

What I want to do is;

If an action's X and Y coordinates are within certain values, it should be marked as "Zone 1".
If an action's X and Y coordinates are within certain values, it should be marked as "Zone 2".
... goes on up to "Zone 15".

For each zone I have Xmin, Xmax, Ymin and Ymax.
So, 4 variables in a nested if and function is what I tried, and failed

The exact coordinates and zones are as follows:

Zones X_min X_max Y_min Y_max
1 0.0 5.8 36.8 50.0
2 0.0 5.8 50.0 63.2
3 0.0 17.0 21.1 36.8
4 5.8 17.0 36.8 50.0
5 5.8 17.0 50.0 63.2
6 0.0 17.0 63.2 78.9
7 0.0 17.0 0.0 21.1
8 0.0 17.0 78.9 100.0
9 17.0 33.0 0.0 21.1
10 17.0 33.0 21.1 78.9
11 17.0 33.0 78.9 100.0
12 33.0 50.0 0.0 50.0
13 33.0 50.0 50.0 100.0
14 50.0 100.0 0.0 50.0
15 50.0 100.0 50.0 100.0

The column titles appear a little crooked but I guess it give the idea...

I created a custom file with only a small data set.

Column A is the X values.
Column B is the Y values.
Column C is where I intend to put the formula.
Column D is the expected results which I entered manually.
Column F to J is the intervals for X and Y values and corresponding Zone IDs (which I copy/pasted to my initial post).

I somehow cannot attach files since the attachment button does not show anything other than a white, blank bar on Chrome or Mozilla.
I uploaded it to WeTransfer, hope it works.

https://www.wetransfer.com/downloads...2134559/1fdc30

Originally Posted by WouterM

Hi,

I have placed your zone table in a sheet, and created an User Defined Function to solve your problem.

Hoop This Helps,

Hello WouterM,

First of all, thanks a lot for your time. I see that this works with a Macro and the output is as correct but I cannot use it since I have around 5000 rows of data at the moment. I cannot manually input all of them to determine their zones. But still, thanks I need a formula to run on the side of each row which holds the raw data to process.

Originally Posted by WouterM

Hi,

I do not see your problem with 5000 rows, is you use my FindZoned function with $ singes you can copy it down.

Formula:

Please Login or Register  to view this content.

Ok, sorry, I see what you mean. Will give it a try.
Thanks again.

Originally Posted by WouterM

Hi,

I have copied my function to your sample file.

I hoop that this makes my previous post clear.

The thing is, the original file that I am working on has many rows that are no use to me, but since it is an automatically generated report from an internal tool, I wish to apply the "solution" from here to an automatically generated excel file.

Of course I can extract the "necessary" parts and run but

Originally Posted by WouterM

Hi,

This internal tool, is it developed and maintained in house, or from an external party.

At this point I would suggest to have the owner of this tool put in a request for change to have the zone added to the report.

If this is not possible you have to find out a filter option on the report to see only the rows that have use for you. Then you can copy in the formula, which need some work. After calculating you will have to replace the formula’s with the results. Otherwise if you send the report to another collogue who does not have the Excel file with the zones table will get error messages.
The tweaked formula should look like this.

Formula:

Please Login or Register  to view this content.

Where zones.xls is the excel document with both the zones table and thse function.

Unfortunately I cannot amend the original report file since it is a globally used report page
My X and Y are on AJ and AK columns, that's fixed.

I most probably will keep your xlsm file and play with it. Import my original report's data into your solution file and run. Thanks a lot again, once I try it with the entire file and see the result I will post again and (hopefully) change to topic to "SOLVED"

Once again, thank you all for your effort and suggestions, they worked just fine. Now this is my go-to place for the future.

I also asked another friend of mine, who have sent me a loooooong formula to do the trick as well, I am sharing it below:

=IF(AND(AND(AJ2>94.2;AJ2<=100);AND(AK2>50;AK2<=63.2));1;"")&IF(AND(AND(AJ2>94.2;AJ2<=100);AND(AK2>36.8;AK2<=50));2;"")&IF(AND(AND(AJ2>83;AJ2<=100);AND(AK2>63.2;AK2<=78.9));3;"")&IF(AND(AND(AJ2>83;AJ2<=94.2);AND(AK2>50;AK2<=63.2));4;"")&IF(AND(AND(AJ2>83;AJ2<=94.2);AND(AK2>36.8;AK2<=50));5;"")&IF(AND(AND(AJ2>83;AJ2<=100);AND(AK2>21.1;AK2<=36.8));6;"")&IF(AND(AND(AJ2>83;AJ2<=100);AND(AK2>78.9;AK2<=100));7;"")&IF(AND(AND(AJ2>83;AJ2<=100);AND(AK2>0;AK2<=21.1));8;"")&IF(AND(AND(AJ2>66;AJ2<=83);AND(AK2>78.9;AK2<=100));9;"")&IF(AND(AND(AJ2>66;AJ2<=83);AND(AK2>21.1;AK2<=78.9));10;"")&IF(AND(AND(AJ2>66;AJ2<=83);AND(AK2>0;AK2<=21.1));11;"")&IF(AND(AND(AJ2>50;AJ2<=66);AND(AK2>50;AK2<=100));12;"")&IF(AND(AND(AJ2>50;AJ2<=66);AND(AK2>0;AK2<=50));13;"")&IF(AND(AND(AJ2>0;AJ2<=50);AND(AK2>50;AK2<=100));14;"")&IF(AND(AND(AJ2>0;AJ2<=50);AND(AK2>0;AK2<=50));15;"")

Note: The coordinates in the final are a little different since I have used the wrong end of the pitch In the formula above the coordinates are amended therefore, don't be confused.