Speeding up a STDEV calculation

[Reboshua]

=IFERROR(STDEV(IF(('All Data'!$F:$F=[@Stuffername])*('All Data'!$AE:$AE<6),'All Data'!$AB:$AB)),"")

The above is the equation I'm using to get the standard deviation of values on a separate sheet with several conditions.

The other sheet has about 21000 rows of data.

I'm doing the STDEV calc above on a table and when I try and calculate this column, it takes 90 seconds or so.

Why does the array take so very long to calculate and is there anything I can do to markedly speed it up?

Thanks!

 

[AhoyNC]

One thing to try is don't reference whole columns in your formula. Instead of $F:$F maybe $F1:$F22000.
When you use IFERROR and reference a whole column the IFERROR function runs on all 1 million plus rows.

 

[joeu2004]

=IFERROR(STDEV(IF(('All Data'!$F:$F=[@Stuffername])*('All Data'!$AE:$AE<6),'All Data'!$AB:$AB)),"")
[....]
Why does the array take so very long to calculate and is there anything I can do to markedly speed it up?

For starters, try:

=IFERROR(STDEV(IF(('All Data'!$F$1:$F$21000=[@Stuffername])*('All Data'!$AE$1:$AE$21000<6),'All Data'!$AB$1:$AB$21000)),"")

Adjust 21000 as needed. And maybe $F$1 should be $F$2, if row 1 has titles.

You were asking Excel to process 1+ million rows multiple times for each STDEV formula.

You were also asking Excel create multiple temporary arrays of 8MB each.

 

[Reboshua]

Thank you for replying - much appreciated.

I thought that, because it's a table, it would know to restrict the calcs to only the rows in the table, but I guess not.

Your suggestion dropped the calculation time from 90 seconds to about 15, that's massive.

Cheers!