I have a workbook with lots of options, consequently some cells are not required for final calculations
What takes less processing power:
1. Leaving a zillion helper columns returning errors and only putting and IF statement in the final column(s) needed for total calculations.
2. Putting IF statements on every cell so they return blank or 0 instead of an error. (Granted this one looks better but does it slow down calculations?)
Looks like you answered your own question!Originally Posted by carsto
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
1. Make sure the most common results are tested for by an IF test at the beginning of a cascade of IF..tests so that once the formula finds a result the rest of the IFs are ignored.
2. If you're using helper columns make sure you're not testing the same thing in more than one helper column. If so create a helper column to get the result you want to use and refer to the helper cell in any other helper columns.
What exactly are you doing. Sometimes we can offer other alternatives to IF..tests? Uploas a simple but representative example and manually show what results you expect.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
I can’t take the time to upload a stripped sample but thank you.
To explain what I’m doing it’s like estimating the cost of a house. (I’m not really estimating a house and no there isn’t software available for what I am doing, at least not that my employer is purchasing!)
I get a file with known information such as Lot Size, Foot print, Furnace type, Siding, etc
Then the options get selected by the user
BATH room 1 :
...MANDATORY info:
…....Length
…....Width
…....Sink Style
…....Toilet Style
...OPTIONAL info:
…....Windows:
….........Window Width
….........Window Height
….........Up to 2 additional windows and their Lengths & Widths
…....Shower Style and Length & Width & any other options
…....Tube Style and Length & Width & any other options
Of course Bath rooms 2, 3, 4 are optional with all of the above req’d
Initially I intended to have (1) of each option and Buttons to “Add another bedroom”, “Add another Window” but that has its own issues. To reduce user entry time, I need to have defaults entered based on known parameters while still allowing the user total flexibility to overwrite any defaults. (For example a window dimensions by default could be 20% of the width of the wall & 70% of the height of the wall but the user could change them.)
Then I abandoned the busy button clicking method.
Whatever I do I still will have cells in a row that aren’t needed all the time, for example, Sink Type “with Vanity” has a vanity type and vanity cost but Sink Type “Pedestal” has no vanity or vanity cost so those error out.
A lot of the looked up information comes from Excel Tables in another workbook. I’ve switched to SQL in VBA to gather a lot of data thus far but so much needs to be live I and don’t want to get into a thousands line long WorksheetChange event!
BTW, Thanks Tony, I thought it would be faster but those errors are so glaring!
Just had a thought, wouldn't it be faster to have an IF statement return "" if the formula returning an error would have been an index & match lookup in another workbook?
Without a sample file, it is difficult to make any suggestions.
A quick test using the attached files suggests to me that lookups in closed files are much slower than lookups in open files. Whether searches resulting in errors take longer than searches that don't probably depends a great deal on whether you are using a linear search (FALSE in H2 of serach file) or a binary search (TRUE in H2). I would expect a linear search that results in an N/A error to take the most time, because Excel will need to search the entire search range from beginning to end before deciding there is an error, where the binary search returns an error is the lookup value is less than/before the first value.
I'm also not sure what you have in mind for the IF() to check for the error. It doesn't seem to me that something like IF(ISNA(vlookup(...),"",vlookup(...)) will be any faster, because the lookup needs to be performed in order to know if it results in an error. It would seem to me that this IF() check will only improve performance if you do it in a way that will allow you to anticipate the error without performing the lookup.
Since you did not provide a sample file, you might tinker with the files I uploaded. Try to make them better represent what you are trying to do, or simply try different combinations of lookups and error checking to see what is faster/slower.
So hide the helper columns?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thank you.
Mr Shorty, I could not open your attachments but my IF statement is very simple If(A2="","", Index(Match()) ). I wouldn't perform the lookup since I know a result is not required.
Many of the Index(Match()) are looking at an Excel Tables in another workbook, which does require that workbook to be open. (It opens automatically in read-only with the sheets hidden.)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks