Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Quote:
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.
__________________
I hope it can help you. Best regards, Marcilio Lobão --------------------------- Belo Horizonte, Brazil |
#3
|
|||
|
|||
Quote:
Thank you very much for your reply. After some smaller adjustments this worked perfectly. Have a nice day, and thank you again! AnnieMa |
#4
|
|||
|
|||
Quote:
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! Last edited by AnnieMa : February 8th 11 at 11:46 AM |
#5
|
|||
|
|||
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. Quote:
__________________
I hope it can help you. Best regards, Marcilio Lobão --------------------------- Belo Horizonte, Brazil |
#6
|
|||
|
|||
Quote:
The file should be available from this location: https://spreadsheets.google.com/ccc?...GdtQ UE&hl=en Thank you! |
#7
|
|||
|
|||
Quote:
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?
__________________
I hope it can help you. Best regards, Marcilio Lobão --------------------------- Belo Horizonte, Brazil |
#8
|
|||
|
|||
Quote:
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I set the unit value on the x-axis? | Charts and Charting in Excel | |||
Absolute cell reference will not remain absolute. | Excel Worksheet Functions | |||
Unit Forcast | Excel Worksheet Functions | |||
CF for over/under mean unit | Excel Worksheet Functions | |||
Need help with unit formulas | Excel Worksheet Functions |