Need formula that has faster calculation time

[pplstuff]

Workbook details:
-Large workbook with many complex calculations
-By hand, I've estimated the full re-calc time at around 4 hours (due to about 20 scenario analyses, aka data tables)
-When not calculating data tables, about 3 minute re-calc time

What I need help with:
(Sorry, I cannot post tables into the thread from my office.)
There are 3 general columns, and 1 column that I'm trying to sumif.

ColumnB: Month
ColumnC: Day of Year (1-365)
ColumnD: Hour of Day (1-24)

Here's how it looks
Month|Day|Hour
Jan|1|1
Jan|1|2
Jan|1|3
...
Jan|2|1
Jan|2|2
... all the way to
Dec|31|24

I need to populate tables where the column header is Month, and the row header is Hour of Day.
Currently, I use the sumproduct formula below.

=SUMPRODUCT(($B4=ColumnModel!$D$3:$D$8762)*(C$3=ColumnModel!$B$3:$B$8762)*ColumnModel!$F$3:$F$8762)
$b4: Month column header
c$3: Hour of day row header
F:F: Sum this
Entered as an array

This formula works, however I believe it is what causes 3 min recalc. Simple sum() would calc much faster, but would be a pain to enter 12*24 times.

Does anyone have any ideas for a formula that I can enter once, and will calculate faster? There are about 10 of these sumproduct() tables, and I'd like to take it from 3 min recalc to 30sec-1min, if possible.

 

[Domski]

You don't say what version of Excel you are using but I assume you can't use SUMIFS.

This is supposed to be a more efficient construct of sumproduct but probably won't be much faster:

=SUMPRODUCT(--($B4=ColumnModel!$D$3:$D$8762),--(C$3=ColumnModel!$B$3:$B$8762),ColumnModel!$F$3:$F$8762)

Otherwise could you use pivot tables?

Dom

 

[pplstuff]

Ah, sorry, Excel 2007. I haven't tried with sumifs, I'll do that now. Do you think sumifs() is faster than sum(if(and()))?

PivotTable wont do, because when I do my sensitivity analysis I need these tables to change after every iteration (and I don't think pivottables will update everytime). Additionally, pivottables tend to make files larger than needed (and it's already a very large model).

 

[jkpieterse]

Sounds like the summary could easily be done using a pivot table. If you drive it with a bit of VBA to run through the scenarios and gather the results somewhere I bet we can get this down to minutes rather than hours.

 

[jkpieterse]

You can set the pivot table to not save its data in the file, which greatly reduces the file size.

 

[Domski]

SUMIFS is more efficient than SUMPRODUCT or a SUM array formula but none of them will calculate as fast as a pivot table.

Dom

 

[pplstuff]

Sumifs was definitely faster!!! Thank you, Dom.

I know a pivottable could do this, I'm just less experienced with them. File size is less of an issue than calc time, so I'll look into this possiblility too.

And regarding driving the pivottables with VBA, that would be awesome to cut it down to minutes instead of hours!! (I hope the sumifs will cut it from 4+ hours to maybe ~1 hour) However, I'm new to VBA so I feel it would take too long to write/record+edit a macro for it to be effective.

Once again, thanks for all the input

 

[jkpieterse]

What you'd do is something along these lines:
- Create a table of iteration inputs (you already have something that is close to this: the DataTable table)
- Setup the pivottable's source list(s) so that changing a couple of input cells gives you the values for 1 "iteration"
- Create a results worksheet

The macro in question would run through the iteration inputs sheet and for each line it would:
- Update the formula input cells with the iteration values
- Refresh the pivot table(s)
- Copy the (relevant portion of the) pivot table results to the results sheet