• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Improve spreadsheet design

E

Eat More Bacon

Member
I have a spreadsheet with lots of if, sumif, lookup, index match, etc
It has become painfully slow. I suspect there is something faulty at some formula.
Where do I learn good spreadsheet design so I can choose formula that will deliver more speed?
Also is there any tool to pinpoint volatile formulas or any formulas that are taking a greater amount of time to calculate?
Thanks!
 
I have read a number of chandoo's articles. I'm not sure which specific one you are referring to?

Do Ref and Name errors cause slow calculations?

Is SumIf or Index Match more efficient?
 
Eat More Bacon
You wrote about learning ... You wrote that You've read.
Okay, that's part of learning. You should watch / test many things, before You could learn.

Ref and Name errors ..
Yes, those will slow. Each time, there is Ref or Name error there will be delay. You should handle all of those.
It could be same if someone has given You wrong instructions, how to go from point A to point B. You would stop and wondering every of those.

SumIf or Index Match ...
Where do would like to compare?
or
How would You like to compare those?
... those do different tasks.
Keep on learning.
 
I have three long formula (all similar but slightly different) in HK, HL, and HQ that I use to track estimated usage of ingredients, actual usage, and six week average usage. The results are then used to track inventory and alert if inventory gets low. (There are numerous copies of these formula, about 50 per week.)

So what this formula does is it checks each recipe for matches to the ingredient listed in HI and returns the estimated usage of the ingredient. Then all the matches are summed.
I was trying to decide if Index Match would be a better choice? Or if there would be another way entirely that would be more efficient?

WeTransfer - Send Large Files & Share Photos Online - Up to 2GB Free

WeTransfer is the simplest way to send your files around the world
we.tl we.tl

=SUM(SUMIF(A68:A113,HI90,F68:F113)*(G124),SUMIF(S68:S113,HI90,X68:X113)*(Y124),SUMIF(AK68:AK113,HI90,AP68:AP113)*(AQ124),SUMIF(BC68:BC113,HI90,BH68:BH113)*(BR124),SUMIF(BU68:BU113,HI90,BZ68:BZ113)*(CA124),SUMIF(CM68:CM113,HI90,CR68:CR113)*(CS124),SUMIF(DE68:DE113,HI90,DJ68:DJ113)*(DK124),SUMIF(DW68:DW113,HI90,EB68:EB113)*(EC124),SUMIF(EO68:EO113,HI90,ET68:ET113)*(EU124),SUMIF(FG68:FG113,HI90,FL68:FL113)*(FM124),SUMIF(FY68:FY113,HI90,GD68:GD113)*(GE124),SUMIF(GQ68:GQ113,HI90,GV68:GV113)*(GW124))

My first 62 rows have numerous NA errors because the date in H1 isn't set. (I copy row 1 to 62 each week for that week's diet recipes and then set the date.) I never considered that NA might be causing Excel to slow down, but that will be easy to resolve by setting the date in H1 to December 25.
 
Ok, I deleted more so that it is small enough to post file directly.
 

Attachments

  • Feed Mixing Sheets for VTM Diets 2023 reduced.xlsx
    999.2 KB · Views: 9
Eat More Bacon
Two questions about Your Improve spreadsheet design... Feed Order-sheet
> Why there are almost same looking ranges many times? ... You're working like with papers.
> Why there are many merged cells? ... it's possible to adjust row heights too.
 
vletm said:
same looking ranges
Click to expand...
Are you referring to the formula I posted above? Or to another formula? In the formula I posted above each of those ranges refer to another diet recipe which may or may not have the ingredient I am looking for.

vletm said:
many merged cells
Click to expand...
I have two rows merged for the ingredients, price per lb, lb per ton, etc then I use those two rows to track actual pounds and actual cost. Is there a better method?

Another thing you may want to look at is the changing prices on the ingredient cost and cost comparison sheets. Costs change frequently and I need to be able to show current costs on current diet without changing the cost on former week's diet recipe. I am not sure if what I did is the right way to accomplish this, but I am copying former week's cost, unless we got a new shipment, then we simply type over the new price and the diet then uses the new cost till we change it again.
 
Eat More Bacon
#1 Your thread is Improve spreadsheet design or what?
It looks like many A4-papers pasted to one sheet
... instead to use Excel-features.

#2 Avoid merged cells ... rows - as I wrote it's possible to adjust row heights too.

>> maybe after Improve spreadsheet design could check more something else eg formulas.
 
Eat More Bacon
#1 You have there 60 times [almost] same layout - instead of one layout.
One layout would be more readable and smoother to modify.

#2 Design layout, which would be smoother to use - even to own sheet.
 
There are 13 diet recipes for every week. Every week I duplicate row 1 to 62 for the current week.

Each one prints to a separate page.
As soon as I fill in how much I want of each recipe, I get an estimated usage.
Once I enter scale weights, I get an exact usage. These figure into the inventory tracking.
At the end of the month/quarter/year, I can figure up the total usage, total cost, etc.

I don't doubt there would be a better method. Do you have an example of what I should be doing?
 
You must log in or register to reply here.
Back
Top