Very long IF statement 2

[Skierarc]

Hey guys,

This is my first post here, but I've found other topics here very helpful.

So I have a very long IF statement that sorts a custom parameter 'stocksize'. I did this because I am making a gauge to measure arc length and I am going to have preset lengths engraved on the gauge that the guys in the shop can easily lock into position. At first I had 20 different lengths which were 20 IF statements in a line, but when I laid that out it wasn't going to be accurate enough for what we do. So I then went to 40 locations which had a 1/8" increment in arc length, but when I wrote that equation Soildworks crashes.

So here is my equation:
IF(`StockSize`<6.125;1;IF(`StockSize`<6.25;2;IF(`StockSize`
<6.375;3;IF(`StockSize`<6.5;4;IF(`StockSize`<6.625;5;IF(`Sto
ckSize`<6.75;6;IF(`StockSize`<6.875;7;IF(`StockSize`<7;8;IF
(`StockSize`<7.125;9;IF(`StockSize`<7.25;10;IF(`StockSize`
<7.375;11;IF(`StockSize`<7.5;12;IF(`StockSize`<7.625;13;IF
(`StockSize`<7.75;14;IF(`StockSize`<7.875;15;IF(`StockSize`
<8;16;IF(`StockSize`<8.125;17;IF(`StockSize`<8.25;18;IF(`Sto
ckSize`<8.375;19;IF(`StockSize`<8.5;20;IF(`StockSize`<8.625
;21;IF(`StockSize`<8.75;22;IF(`StockSize`<8.875;23;IF(`Stock
Size`<9;24;IF(`StockSize`<9.125;25;IF(`StockSize`<9.25;26;IF
(`StockSize`<9.375;27;IF(`StockSize`<9.5;28;IF(`StockSize`<9
.625;29;IF(`StockSize`<9.75;30;IF(`StockSize`<9.875;31;IF(
`StockSize`<10;32;IF(`StockSize`<10.125;33;IF(`StockSize`<10
.25;34;IF(`StockSize`<10.375;35;IF(`StockSize`<10.5;36;IF(`S
tockSize`<10.625;37;IF(`StockSize`<10.75;38;IF(`StockSize`<1
0.875;39;IF(`StockSize`<11;40;123456789)))))))))))))))))))))
)))))))))))))))))))

Don't worry I didn't write this by hand, I had excel create it for me. Its 969 characters long, which I think may be the problem. Is there a character limit on equations or is there a simpler way of doing this?

Also the equation is going into a BOM on a drawing sheet.

Thanks,
Josh

 

[DesignerGuy16]

One thing I noticed with Solidworks based excel stuff is that If() statements needed to be stripped to one if statement. It doesn't like handling multiple if/then statements.

IE:
A=If('stocksize'<6.125,1,B)
B=If('stocksize'<6.25,2,C)
C=....

Even excel has limits to how many nested If statements can be put into one cell. At least older versions did. I've had to split a 50 some variable If chain into like 10 cells for it to work correctly. Solidworks is even more rudimentary then that with it's ability to handle excel functions.

James Spisich
Design Engineer, CSWP

 

[NL8]

Josh,

Instead of a nested if statement, why dont' you use a mathematical equation.

Something along the line of:

Custom Property = Roundup(IF(StockSize<6.1256,1,(stocksize-5.875)/.125),0)

At least, that is the excel equivalent of the equation. The only thing I'm not sure will work is the RoundUp command.

Joe Hasik, CSWP/SMTL
SW 09 x64, SP 4.0
Dell T3400
Intel Core2 Quad
Q6700 2.66 GHz
3.93 GB RAM
NVIDIA Quadro FX 4600

 

[CorBlimeyLimey]

V

Please note that posting large images, or long statements, messes up the page formatting. If needed, an Excel or TXT or whatever file can be linked via the ENGINEERING.com upload function in the New Post/Reply section.

I believe Excel 2003 is limited to 7 nested IF statements.

 

[Skierarc]

Thanks for the info guys,I used Joe's advice. I can't believe I didn't think of writing a simple equation. It's so obvious it went right over my head.

={0}(`StockSize`-6)/.125

That was my final equation, the {0} is the precision, which basically does that roundup function to a whole number.

Thank you guys for the help, CorBlimeyLimey I'll remember that in my next post, thanks.

 

[NL8]

Glad to help, sometimes it just takes a second set of eyes to look at the problem.

Joe Hasik, CSWP/SMTL
SW 09 x64, SP 4.0
Dell T3400
Intel Core2 Quad
Q6700 2.66 GHz
3.93 GB RAM
NVIDIA Quadro FX 4600