Smarter way to write formula?

[RhesusMinus]

Hi.

I the attached drawing I have a rectangle the can be resized into 4 different sizes:

50 x 50 mm
50 x 100 mm
100 x 50 mm
100 x 100 mm

I've attached a formula to the text, so that it changes the letter, depending on the size of the rectangle.

However... this formula is:

Code Select Expand

=IF(AND(Width=50 mm,Height=50 mm),"A",IF(AND(Width=100 mm,Height=50 mm),"B",IF(AND(Width=50 mm,Height=100 mm),"C",IF(AND(Width=100 mm,Height=100 mm),"D","INVALID SIZE"))))

Quite a long string, for only 4 combinations of sizes. I have to make some shapes that have 4 different widths, and 10 different heights. And all these 400 different combinations have their own part number (represented by the letter in this test shape). And add to that, that they might come in 3 different colors, represented by a Shape Property drop down.. then I suddenly have 1200 combinations.

So, I've got 2 questions:

1. Is there a smarter way to create a formula like this?
2. Is there any limitation on the length of a formula in one cell?


THL

[wapperdude]

One possibility would be an indexed list, especially if you know the part number, then it can drive the various options.

Using datagraphics and Excel file is another way, but, I've not had a need to go there, so, someone else will have to chime in.

Trying to do all of those permutations via boolean logic would result in one really mind-blowing equation!  It might be possible to break it down into several smaller equations, say, a set for each color option.  But, that will still be quite a logistical undertaking.

Wapperdude

[Nikolay]

Yes, indexes seems to be a neat solution here!
You could first get your sizes indexed, and then use indexes instead of size values.

For example, you could state that Width "50 mm" is "width number 1" and "100 mm" is "width number 2".
The same goes for the Height; i.e. "50 mm" is  "height number 1", and "100 mm" is the "height number 2".
Then, you could combine both indexes into one to get the text.

You could implement the above idea by introducing 3 helper "index cells" in the user section (or in the scratch section):

Code Select Expand


User.WidthIndex=LOOKUP(FORMAT(Width,"0"),"50;100",";")
User.HeightIndex=LOOKUP(FORMAT(Height,"0"),"50;100",";")
User.CombinedIndex=User.HeightIndex*2+User.WidthIndex

Field.Value=INDEX(User.CombinedIndex,"A;B;C;D",";")


This approach looks scalable and maintainable; i.e. you shouldn't face any problems with more than 2 sizes.
I've attached an example having 4 sizes in each directions.
Keep in mind that the sample does not handle any errors for clarity, but I think that error handling can be added quite easily.

As for the second question, AFAIK the maximum text size in a cell is not limited; i.e. it is 4GB.

[Jumpy]

As for the second question, AFAIK the maximum text size in a cell is not limited; i.e. it is 4GB.

VisioGuy made a test how many IF-clauses you could nest inside each other (for example only 7 in Excel) and Visio managed 50-something without problems.

[Nikolay]

Yes, you are right, thank you for the notice! It slipped my mind that it is not just plain text we are talking about but a bunch of nested IFs to calculate

As for the "plain text", I stored a few megabytes of text in a cell without any visible problems.
The 4Gb must be a physical limitation of the COM API (BSTR); I believe there was a topic about that.

[RhesusMinus]

Thanks for excellent answers