Calculation from exact position to unit position

[AnjaNi]

Hello, I hope somebody can help me with a calculation formula.

I have a database of artifacts which have been collected according to a local grid consisting of X-coords and Y-coords. In the database artifacts thus have two fields for X and Y. Most finds are related to their exact position within the grid, ie 114,33x 115,45y. On the other hand, the position of some artifacts are only related to the 50cm2 square units within the grid where they were found. Each such is named according to its orientation within the square meter (NW, SW, SE, NE). The position of these will look like this: 114x115y SW. To be able to perform my analysis, all artifacts need to be transformed to this last type of position. I therefore would like to make a separate field where the decimals of each exact position are used to calculate the corresponding unit and result in unit coordinates (i.e. 114,33x 115,45y = 114x115y SW).

There will be four variants:

,0-,49x/,0-,49y = SW

,50-99x/,0-,49y = SE

,0-,49x/,50-,99y = NW

,50-,99x/,50-,99y = NE

I hope this makes sense! Is such a calculation possible, and if so - how can I formulate it? Any help will be highly appreciated!

Anja

[comment]

If I understand this correctly (?!):

Case ( Mod ( y ; 1 ) < 0.5 ; "S" ; "N" )

&

Case ( Mod ( x ; 1 ) < 0.5 ; "W" ; "E" )

This is assuming y is increasing from S to N and x is increasing from W to E (I cannot decipher your examples).

[AnjaNi]

Yes! This worked perfectly. Thank you very much!

[AnjaNi]

I just have one more issue I hope you can help me with.

If X and Y are without any decimals (ie 114x113y), I would like the calculation to combine "X" & "Y", and in addition look the unit up from a separate field. This is because in the cases where the artifacts are only related to units I need to enter the unit-name in a separate field. I will therefore have these fields:

1. X (with or without decimals)

2. Y (same)

3. Unit (NW, NE, SW, SE)

4. XY and unit (should always have this format: fex 114x114y NW - that is no decimals)

So in other words: if X or Y are without decimals, I want the field to combine X and Y Unit fields (X & Y &" "& Unit). If X or Y are with decimals, I want the calculation from the above post to be performed.

Maybe this can be done by using some If-function?

Is this possible at all?

Thank you in advance for any help!

[comment]

The criteria of "without decimals" is somewhat problematic: numerically, there is no difference between "1" and "1.000". Wouldn't it be better to look at the Unit field, and if it has data let it override the calculation?

[AnjaNi]

Yes, this would of course be easier. Do you have a suggestion of how to formulate such a calculation?

(Sorry if this should be obvious - I am a complete newbie...).

Thanks, Anja

Edit: manged to figure it out - disregard this post!

Edited November 2, 2008 by Guest