Hi Having seen how bad/slow some spreadsheets have been allowed to get, I'm really keen to try and do everything efficiently from the start even before it gets big. So I've done some googling on efficiency of formulas and I've got a few questions I wondered if an expert could help with please.
Background: I've got a model that needs to have 1 row of calculations for each day in the calculation period. And there's about 400 columns.
e.g. If Start Day = 01/08/2010 and End Day = 01/08/2018 then I've got it so that the VBA copies the formulas down for approx 8*365 rows
Q1: The row that contains the final answer will vary each time the model is ran. It will be whatever the row containing the End Day is. What is the best way of picking out the final answer from the last row?
My Thoughts: Index(Match), Lookup and Vlookup, Sumifs don't feel like the most efficient way because they involve referencing a whole column just to get one value. Is there a more efficient formula?
Q2: I've read about Excel's smart calculation engine and how it builds a map of dependencies. So I'm beginning to wonder whether I'm really saving any time/resource in this example:
In those 8*365 row, I've got a calculation that only needs to be performed on certain days (rows). e.g. Pretend it needed to only be performed on the 12th, 19th and 27th of every month. I could just make the calculation happen on every row i.e. 8*365 times. But I thought it would be a good idea to put on each row a formula like if(or(day(A1) = 12, day(A1) = 19, day(A1) = 27), [[[Complex Calc]]], "-")
Does this save time/resouce by preventing Excel from doing the [[[Complex Calc]]] when it doesn't need to? Or am I no better off because Excel still puts that [[[Complex Calc]]] into it's map of dependencies / smart calculation engine?
Thanks
Background: I've got a model that needs to have 1 row of calculations for each day in the calculation period. And there's about 400 columns.
e.g. If Start Day = 01/08/2010 and End Day = 01/08/2018 then I've got it so that the VBA copies the formulas down for approx 8*365 rows
Q1: The row that contains the final answer will vary each time the model is ran. It will be whatever the row containing the End Day is. What is the best way of picking out the final answer from the last row?
My Thoughts: Index(Match), Lookup and Vlookup, Sumifs don't feel like the most efficient way because they involve referencing a whole column just to get one value. Is there a more efficient formula?
Q2: I've read about Excel's smart calculation engine and how it builds a map of dependencies. So I'm beginning to wonder whether I'm really saving any time/resource in this example:
In those 8*365 row, I've got a calculation that only needs to be performed on certain days (rows). e.g. Pretend it needed to only be performed on the 12th, 19th and 27th of every month. I could just make the calculation happen on every row i.e. 8*365 times. But I thought it would be a good idea to put on each row a formula like if(or(day(A1) = 12, day(A1) = 19, day(A1) = 27), [[[Complex Calc]]], "-")
Does this save time/resouce by preventing Excel from doing the [[[Complex Calc]]] when it doesn't need to? Or am I no better off because Excel still puts that [[[Complex Calc]]] into it's map of dependencies / smart calculation engine?
Thanks
Last edited:
