# 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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### 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
683
Replies
1
Views
413
Replies
3
Views
257
Replies
3
Views
192
Replies
5
Views
252

1,129,549
Messages
5,636,929
Members
416,953
Latest member
prakashkumar

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