# Formula Efficiency Questions

#### PTP86

##### Board Regular
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:

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### shg

##### MrExcel MVP
=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
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
It's a formula that contains a literal array, but doesn't require entering as an array formula.

Replies
26
Views
570
Replies
1
Views
330
Replies
3
Views
245
Replies
3
Views
180
Replies
5
Views
244

1,127,562
Messages
5,625,525
Members
416,116
Latest member
Joemamasuka

### 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.

### Which adblocker are you using?

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

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