Excel advice to improve speed on a complex formula looking up keywords
Hi Experts
I have a fairly complex spreadsheet which performs loads of calculations & its a tad slow especially as I'm adding more functionality as I go, so I am looking to improve some of the formulas to make it run quicker.
One of the calculations I need advice on looks up certain keywords in a cell and returns a value based on that if the keywords are found - an example is below.
What I'm using is an "ISNUMBER(SEARCH" formula as the key words I'm looking for sometimes don't appear at the start of the text, but would I be better off (from a speed perspective) to either:
- Look up values from a list
- Try & use some kind of Function to sort this
- Do something else?
Any ideas would be gratefully received, I'm kind of thinking the Function route might be best, but don't really know where to begin on that (I post a separate question if that's the recommended route
Example ....
=IF(ISNUMBER(SEARCH("/talent/profile/",B15)),"name",IF(ISNUMBER(SEARCH("www.linkedin.com/in/",B15)),"name",IF(A15="ExperienceProfile experience","Profile Section",IF(LEFT(A15,9)="Show less","End Profile",IF(LEFT(A15,8)="Show all","End Profile",IF(C14="End Profile","",IF(A15="EducationProfile education","End Profile",IF(AND(C14="Profile Section",A15=""),"Start Profile",IF(ISNUMBER(SEARCH("Show all items in this list.",A15)),"End Profile",IF(ISNUMBER(SEARCH("ActivityProfile",A15)),"End Profile",IF(ISNUMBER(SEARCH("Show fewer Show fewer items in this list",A15)),"End Profile",IF(ISNUMBER(SEARCH("Skills Match",A15)),"End Profile",IF(ISNUMBER(SEARCH("SpotlightsProfile",A15)),"End Profile","")))))))))))))
Many thanks
ASKER
Thats great & thank you for your advice - the revised formula works perfectly, its hard to tell how much difference it makes on speed, but is certainly neater with less steps so it will definitely help. Re your second comment - I need to test the viability (i.e the variations that the raw data outputs), but that looks much simpler & therefore even quicker.
Many thanks
Tom
An OR would actually be slightly less efficient than nested IF because it will always perform all of the searches, whereas nested IF will stop at the first one that is true.
On the other hand, a slightly shorter formula may lead to a smaller workbook, and less memory usage, so you'd have to test. For shorter formulas, you could use syntax like:
COUNT(SEARCH({"/talent/profile/","www.linkedin.com/in/"},B15))
rather than:
OR(ISNUMBER(SEARCH("/talent/profile/",B15)),ISNUMBER(SEARCH("www.linkedin.com/in/",B15)))
Managed to shorten the formula by combining some of the nested IFs into an OR statement and have re-ordered the checks slightly:
=IF(C14="End Profile","",
IF(OR(ISNUMBER(SEARCH("/talent/profile/",B15)),ISNUMBER(SEARCH("www.linkedin.com/in/",B15))),"name",
IF(AND(C14="Profile Section",A15=""),"Start Profile",
IF(A15="ExperienceProfile experience","Profile Section",
IF(OR(LEFT(A15,9)="Show less",LEFT(A15,8)="Show all",A15="EducationProfile education",ISNUMBER(SEARCH("Show all items in this list.",A15)),ISNUMBER(SEARCH("ActivityProfile",A15)),ISNUMBER(SEARCH("Show fewer Show fewer items in this list",A15)),ISNUMBER(SEARCH("Skills Match",A15)),ISNUMBER(SEARCH("SpotlightsProfile",A15))),
"End Profile","")))))
The re-order is putting the check of previous row first