Forum Discussion
Is there a better way to optimize If formulas?
So I was tasked to make a workbook to help a team leader track the problem projects of his members. In this sample, I only used two formulas (columns G and H). My question is how should I optimize this? This is going to be used for the whole year with a lot of new entries added every month (let's say 200-400 every month) so I'm afraid everyone involved may eventually have a terrible experience.
Formulas:
SLA adherence - IF(C2<>"",IF((D2-C2)>3,"Beyond 72 Hours","Within 72 Hours"),"")
Status - IF(E2="Yes",(IF(AND(D2<>"",F2<>""),"Closed","Open")),(IF(B2<>"",IF(D2<>"","Closed","Open"),"")))
Addt'l context: The SLA adherence and Status formulas will be pasted to all the cells in their respective columns to make sure no one has to do anything other than put in new bad projects and some details. The original workbook has a few more columns (no formulas) and I added a dashboard for this where it will list all open status problem projects in real time so it might cause the file to slow down to a crawl in a few months.
If you convert the range to a table and use structured table references, each of the two columns will basically contain a single formula; this should be more efficient.
