Can this formula be made more efficient?

[GiventoFly]

---

I have a Spreadsheet/Dashboard that I created to look at sales trends YoY. I have created so many cells with formulas that now the spreadsheet takes forever to process between any changes.

I essentially have 3 or 4 drop down boxes where I can select Region, Metric, Segments.

What I'd like to know is if the following formulas could be written in a way that could cut down on the process time between each change. Any help would be greatly appreciated.


i.e. could I use Subtotals in any way?


=(B11-SUMIFS(INDIRECT($B$2),Division_Name__Code_and_Number,$B$4,Pyramid_Segment_Desc.,$B$3,PIM_Super_Class_Description__Current,$A10,Calendar_Year_Week,">="&$F$3,Calendar_Year_Week,"<="&$G$3)/52)/(SUMIFS(INDIRECT($B$2),Division_Name__Code_and_Number,$B$4,Pyramid_Segment_Desc.,$B$3,PIM_Super_Class_Description__Current,$A10,Calendar_Year_Week,">="&$F$3,Calendar_Year_Week,"<="&$G$3)/52)


=SUMIFS(QtyShipped,Division_Name__Code_and_Number,$B$2,Pyramid_Segment_Desc.,$B$3,PIM_Super_Class_Description__Current,$B$4,Calendar_Year_Week,">="&$D$3,Calendar_Year_Week,"<="&$E$3)/52

 

[J.Ty.]

Welcome to MrExcel!

My guess is that INDIRECT is the culprit. It is a volatile function, i.e., it will recalculate after any change to the spreadsheet, causing the whole formula recalculate, and its dependents, and so on. Can you tell me what a typical value of $B$2 is?

Otherwise there might be some speed-up possibilities that rely on the design of the whole spreadsheet, how the data is organized, etc.

J.Ty.

 

[GiventoFly]

Thank you J.Ty.

$B$2 references 9 different named metrics in a drop down menu. For Example, if I choose "Sales" in B2, it references that for the SUMIFS and sums only Sales information. I hope that makes sense.

 

[cb366374]

Not knowing how your spreadsheet is designed, it's hard to recommend a solution but INDIRECT should be avoided at all costs in real world applications.

It's often better to organize your data like a database table is laid out. So instead of different named ranges, you have one column that specifies the category, whether it's Sales/COGS/PP&E/etc. Then all you have to do is add another condition to your sumifs that compares that column to your dropdown. Or you could make each category (e.g. Sales) a separate column, or measure, on that table and then use index function to pass your sumif a dynamic range.

 

[J.Ty.]

Can you post here a small sample of your data? I mean the complete design, all columns, etc., but we need only a few rows of the real data, just to see their shape. However, we want the information how many rows you have in each table.

Section B of http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html lists a number of tools which can be used to generate the screenshot of your spreadsheet.

J.Ty.