In excel is it bad to have about 1000 embedded if statements? I have about 1000 options that I want to pre-populate in other excel fields, what is the best way to do this other then inefficient if statements?
+1
A:
Yes, it is bad.
Excel is not a good tool to use for complex programs. It would be better to buy Resolver One which gives you a spreadsheet interface, but actually generates Python code under the hood. That way you can audit and test the code for correctness, and you are less tempted to create awfully complex, and often buggy, spreadsheets.
There is a group called the European Spreadsheet Risks Interest Group that holds an annual conference about the risks inherent in complex buggy spreadsheets. A lot of the papers presented at this conference are available on their website.
Michael Dillon
2009-10-21 20:38:02
+1
A:
only 7 if statements can be embedded in one another in older versions of excel. 2007 allows more, but it gets unwieldly after that.
A better option may be to build a table with all your different options, then use the VLOOKUP formula to pull the result.
Whether you use Excel or another tool, the important thing is to design it well with a good flow of information that is easy to follow. Excel works great for large spreadsheet projects as long as they are well designed. The additional benefit to a well designed Excel file is that more people can open them.
guitarthrower
2009-10-21 20:48:01