Axiom Home Page Forums General Discussion The Water Cooler Need some Excel help

I wouldn't worry too much about a few thousand VLOOKUPS, especially when the workbook recalcs in under a minute.

I once inherited a workbook with hundreds of thousands of VLOOKUPS that took over 40 minutes to recalculate. Not a typo. Minutes. I got it down to about 40 seconds by converting the approach. It took a couple of hours to change the approach.

Maybe it's just me, but I prefer to simplify the logic when building IF formulas, breaking out the calculation type work into separate cells, and then referencing those cells in the IF. Not only does it make it easier to review (and build) ALL of the formulas in the sheet, but because of how Excel recalculates it often helps to improve recalculation performance. See the comments on volatile functions. I've seen references that say that the INDEX function is not volatile, despite what some MS documentation says. My results back that up.

And no, I don't know of any better options for the IF and the Boolean functions. So long as you understand what you're building, you won't get surprises using those.

I'm self-taught too. You're definitely on the right track and are asking the right questions. Keep it up!

Once again many thanks...

If I'm not taking advantage ..... but I prefer to simplify the logic when building IF formulas, breaking out the calculation type work into separate cells, and then referencing those cells in the IF.

If I understand, your talking about performing the base calculations in there own cells and then "IF" them all in the same IF calculation?

For this particular workbook (and a few others), I have to keep all data for each "record" on one row (I'm printing the calculated results of the user's input options choices to a form/report using an excel mail merge) and I'd run out of columns breaking out all the individual If's/options.

But I wouldn't mind an example of "simplifying the logic when building IF formulas, breaking out the calculation type work into separate cells, and then referencing those cells in the IF", from one of my formulas or something you have available, if time allows.

Once again, if I'm not taking advantage.

Thanks

Jeff

In the end you've got to do what you've got to do, and if that would exceed the number of columns then, 1) Wow, that's some wide sheet!, and 2)"If it ain't broke, don't fix it".

Could you reorganize the sheet to be top to bottom instead of left to right? This allows for many more items and is often just as easy to manage. But if you're already working with a large number of rows, then that's probably not possible for you.

Think of this primarily as a design choice, and a choice that make it easier to maintain and review at that. No more, no less.

That said, you're correct, break out the True and False conditions of the If functions to separate cells when you can.

So your example of

=IF($F2="","",IF($F2="Oval",3.1416*(SQRT(2*((POWER($CE2,2)+(POWER($CF2,2))-((POWER(($CE2-$CF2),2)/2.2)))))),0))

in say C2, could change as follows:

A2 gets

=IF($F2="Oval",B2,0):

And B3 becomes:

=3.1416*(SQRT(2*((POWER($CE2,2)+(POWER($CF2,2))-((POWER(($CE2-$CF2),2)/2.2))))))

I think that I got the ( and ) count right there.

C2 becomes:

=IF($F2="","",A2,0))

IMO, much more readable, and easier to build.

An aside, if you'll allow me. If you're regularly connecting data via VLOOKUP functions, you owe it to yourself to have a hard look at Monarch. Simply put (really simply), it's a data management tool for the everyman. I'm not going to go off on some big sales pitch here (I don't sell it anyway), but clearly I'm a bit passionate about it. Spend a few minutes on my site, check out some videos and other posts, and email me if you have further questions.

I'd been using spreadsheets for years before I discovered Monarch software, and it really has changed my work, heck, my career, for the better.

Excel + Monarch is like Lennon + McCartney. Each is good on its own, but together, it's magic.

Mucho GrassyAss. Thanks for all the help! Might need more when I have time for the "Dynamic Ranges"!

I'll check into MOnarch. I've already saved the sight, after noticing your passion!

Jeff