Formula Efficiency Questions

PTP86

Board Regular
Joined
Nov 7, 2009
Messages
86
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
 
Last edited:

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
=if(or(day(A1) = {12,19,27), [[[Complex Calc]]], "-")

The complex calculation is only calculated on those days. IF is a very smart function.
 

PTP86

Board Regular
Joined
Nov 7, 2009
Messages
86
Thanks shg and that's an interesting trick you've just taught me or(day(A1) = {12,19,27})

Do those {} brackets make this an array formula?
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
It's a formula that contains a literal array, but doesn't require entering as an array formula.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,097
Members
415,876
Latest member
csibonga2k17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top