Calculating and concatenating from absolute to unit coordinates
 

I have a spreadsheet with two columns for two-decimal coordinates (X and Y) denoting the absolute location of points (=absolute coordinates). I need a to transform the coordinates to only indicate which of four 50cm2 units within each square metre the point is found within (=unit coordinates). The 50 cm units are named according to orientation within the square metre (NW, SW, NE, SE).

The decimals of each X and Y should be used to calculate the corresponding unit and the metre coordinate should be extracted, resulting in unit coordinates in a separate column (i.e. 114,33x 115,45y = 114x115y SW).

There are four variants:
,0-,49x/,0-,49y = SW
,50-99x/,0-,49y = SE
,0-,49x/,50-,99y = NW
,50-,99x/,50-,99y = NE

Y is increasing from S to N, and X is increasing from W to E.

Is there a calculation that allows me to do this?

All help much appreciated!

--------------------------------------------------------------------------

Dear AnnieMa, Good Evening.

It´s a little confused to understand your variants.
But I tried to help you developing a kind of compass based on your explanation.

It´s he 07-02-2011_ExcelBanter_Pontos_Cardeais.xls

I used Excel 2003 to do this.

Do a test and tell me if it worked for you.
Feel free to ask anything about it.

Dear Mazzaropi,

Thank you very much for your reply.
After some smaller adjustments this worked perfectly.

Have a nice day, and thank you again!

AnnieMa

Hello again,

I have encountered another problem. The X and Y columns are formatted as numbers, with two decimals. However when the last decimal is zero it is rounded the nearest integer and consequently only one decimal, i.e. 113.10 - 113.1. This results in XY coordinates as 113.1x 114.9 y and a subsequent #VALUE!-error when the your calculation is applied.

How can I format the columns so that two decimals ALWAYS are shown? Alternatively, how can I alter the calculation to work also with one decimal-numbers?

Again, thanks a lot!

Dear AnnieMa, Good Morning.

Coul´d you save your file at a free site, www.4shared.com and put the link here, to easier the answer?

I believe that will be faster to help you.

I want to help you.

Hello again,

The file should be available from this location:
https://spreadsheets.google.com/ccc?...GdtQ UE&hl=en

Thank you!

------------------------------------------------------------------------

Dear AnnieMa, Good Afternoon.

Now than I could see your Worksheet, it´s was VERY EASY to solve the question.
I spent just a few seconds to create a new formula.

You have x and y at a different cells.
It´s make all the diference.

The new formula is MORE SIMPLE, QUICKER and MORE EASY to adapt to another cells if necessary.

The new worksheet is he http://www.4shared.com/document/KHmW...r_CONC_CA.html

Please, take a look at it and tell me if it helped you.

Feel free to ask anything about it.

Ps.: I am from Brazil (Country), Minas Gerais (State), Belo Horizonte (City)
Where are you from?

Good Morning, Mazzaropi.

Your new formula works excellently. Again thank you very much for your help, you have saved me a lot of work today!

I am from Tromso, Norway, quite far north...