Formula to calculate if distance between 2 points is at least X

[UncleKyller]

Hi, I'm new here to MrExcel, and still a newb to excel. So thought I would come here to ask for help from folks smarted than I.

I am trying to help a group of folks I play Minecraft with. We have a private server where you are allowed to claim a plot of land 200 square blocks from any other claim so a 400 or greater block distance between claims. No other player can build with in 200 blocks of your claim. I had a base started and because there is not a good system in place to check coordinates I was told I could build and it was too close to another claim. So I had to move my whole base after hours of work. I can live with this.

I would like to help the server and come up with a way to check claimed coordinates against all other claims. So, I am wondering, before I take an Excel course online, If any one has a way that I can put coordinates in and it will tell me if is is far enough from all of the other coordinated already claimed? I'm thinking one sheet for each type of claim, and then a cover where you can put the coordinates in and it will tell you if you are encroaching on another claim. I think X & Y axis is all I need (Longitude & Latitude). Maybe the cover sheet has a cell that turn green if the claim is valid and red if it is not. Not sure how easy or hard this is but thought I would go to the experts.


Thanks in advance for any help.

 

[UncleKyller]

I was just told by a friend it is a 200 block radius so I need an X,Y,Z calculator. I would delete and repost. but cant find a way to delete this.

 

[UncleKyller]

I'm not a kid skipping school btw. An adult that likes games on my day off. I work Wed. thru Sun.

 

[UncleKyller]

So I have gotten the sheets to calculate. But now I want the green box on the cover, the first pic to turn red if any cells in the column on sheet 2 Base Claims in the second pic are red. But as you can see it isn't working. I have tried many formulas and all lead me to what is happening now. The cell turned green but when I make a cell in the column red it isn't turning red. I hope there is a simple ish fix. I just haven't found it on the web.

 

[Alex Blakenburg]

I don't believe you can make the condition use the colour but you should be able to use the underlying condition.
See if this does what you need in the conditional formatting box.

=COUNTIFS('Base Claims'!C$3:C$35,">40000")

 

[UncleKyller]

Thanks! I will give this a shot.

 

[UncleKyller]

Hmm, it is staying green whether the condition is true or false, ie if I make one cell in the Base Claims red it does not affect the cell on the Cover. If I cant use the color what should I use? Green is greater than and red is less than.

 

[Alex Blakenburg]

Changing the color in Base Claims is not going to change anything detectable by conditional formatting.
What you can do in Conditional formatting is apply the rule that you are using to turn the cell red in Base Claims.
Another option is what value are you putting in F8 of the target cell ? Can you apply the Rule for it being red directly to that cell ?

If the cell can only be red or green then you can just make say green the default color and apply a condition to make it red so you only need one rule.
If the condition is that if "any one" cell in C3:C35 is < 40000 then if you make the default green and put the following conditonal format to make it red.

=COUNTIFS('Base Claims'!C$3:C$35,"<40000")

If you really want to code both the green and red then greater than for turning it green is not the opposite of the above formula, because it is possible and even likely that when 1 or more cells are < 40000 you have 1 or more cells that are >=40000.
You would need.
Red -->

=COUNTIFS('Base Claims'!C$3:C$35,"<40000")

Green -->

=NOT(COUNTIFS('Base Claims'!C$3:C$35,"<40000"))

You would do the red condition first and put click the stop button, since once it is true there is not point in evaluating the 2nd rule.
Note: with =Countif 0 is FALSE and and other value is TRUE

20221117 ConditionalFormatting UncleKyller.xlsx
	A	B	C	D	E	F
3
4
5
6
7
8			Test Formula count if > 40000 --->			0
9
Cover

Cell Formulas
Range	Formula
F8	F8	=COUNTIFS('Base Claims'!C$3:C$35,"<40000")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F8	Expression	=COUNTIFS('Base Claims'!C$3:C$35,"<40000")	text	YES
F8	Expression	=NOT(COUNTIFS('Base Claims'!C$3:C$35,"<40000"))	text	NO

 

[UncleKyller]

This worked.

"If the cell can only be red or green then you can just make say green the default color and apply a condition to make it red so you only need one rule.
If the condition is that if "any one" cell in C3:C35 is < 40000 then if you make the default green and put the following conditonal format to make it red."

 

[Alex Blakenburg]

Thanks for letting me know and being so clear about what worked for you. Glad I could help.