Hello, I have a problem that I'm struggling with.
I have a shape that is going to be in any one of 28 known positions. I want to take the PinY position, rationalise it so that each vertical increase of 20mm is an increment of 1, and use that in an INDEX() funtion to return a text string from a list of strings.
e.g. 1st position is y=230mm, 2nd position is y=250mm, and so on. To rationalise this I've used: position=IF((PinY-230 mm)=0,1,((PinY-230 mm)/20)+1 mm) (the IF statement is required to avoid dividing 0 by 20). I would then like to use this within: INDEX(position,"a;b;c;d;e") where the string has 28 possible values.
When I try the above, the result is ALWAYS the first string in the list, regardless of position! I've tried an alternative approach by using the MID() function and having the string without seperators, but the result is the same - ALWAYS the first letter.
Help!!! (My only thought is that the dimension is somehow preventing the formulas from recognising it as a number).
P.S. I have also posted this in the "Shapes & Templates" forum as I am not sure which it should live in.
Quote from: Panther on November 18, 2008, 01:46:24 PM
Hello, I have a problem that I'm struggling with.
I have a shape that is going to be in any one of 28 known positions. I want to take the PinY position, rationalise it so that each vertical increase of 20mm is an increment of 1, and use that in an INDEX() funtion to return a text string from a list of strings.
e.g. 1st position is y=230mm, 2nd position is y=250mm, and so on. To rationalise this I've used: position=IF((PinY-230 mm)=0,1,((PinY-230 mm)/20)+1 mm) (the IF statement is required to avoid dividing 0 by 20). I would then like to use this within: INDEX(position,"a;b;c;d;e") where the string has 28 possible values.
I dont know if I am oversimplifying or not, but I woudl approach this is two steps. But first, I would avoid placing formulas in the PinX/PinY cells.
1. I would start by creatng a User Cell that calculates the numeric index. Would not something like this work:
User.index= INT((PinY-230)/20)
2. Second, you can use the Index into your string
User.stringy = INDEX(User.index,"A;B;C;D...")
I tested this on a smple line that sets its text to the letter and it works fine. My guess is that you forgot the INT() to get rid of the decimal places from the division and maybe that screws up the INDEX function? Separating the Index into a user cell will make it clear what the values are so you can see whats happening.
And I dont think you need the IF() statement either since it would be the numerator that is zero and that divides just fine. In my example it worked just fine with just the two User Cells.
I'd be happy to mail you the sheet with the line shape if you need.
Good luck
If you put units on the "/20" part of your original formula, it should work fine. The INDEX gets confused with the 1.0000 mm value for position, and if you have your formula look like this: =IF((PinX-230 mm)=0,0,((PinX-230 mm)/20 mm)) the units all cancel out. I also changed the formula to be 0-based, since the INDEX function is 0-based.
And iankoe is correct -- you don't need the IF.
-- Michelle
Thanks michelleh and iankoe. I've tried as you suggested and get a slightly different anomoly.
Quote from: michelleh on November 21, 2008, 02:06:27 AM
... have your formula look like this: =IF((PinX-230 mm)=0,0,((PinX-230 mm)/20 mm))
Using the above, the number changes exactly as I expect. When I add the INDEX() function, it appears to work, but then gets out of sync. As an example, as I move the box to a few different locations, it will show "E" when it should be showing "D", thereafter it will always be one out of sync. Any ideas?