calculation speed questions

[Harvey Waxman]

In a large spread sheet (11000 rows x 20 columns) calculation was pretty
speedy.

I then added some sections with several frequency arrays and averaged the
resulting frequencies. Next I created a couple of pie charts based on these
arrays, percentages and averages etc.

Now a goal seek and recalculation takes forever ( I actually had to force
quit). If I copy and paste back only the values of the arrays and associated
averages, the calculation goes back to normal.

Is there a way to select a range of cells and exclude it from calculation so I
don't have to lose the formulas in the cells?

I duplicated the spreadsheet and worked on the copy as a workaround but I hope
there's a better solution.

Thanks,


Harvey

--
Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me

 

[JE McGimpsey]

Is there a way to select a range of cells and exclude it from calculation so
I
don't have to lose the formulas in the cells?

I duplicated the spreadsheet and worked on the copy as a workaround but I
hope
there's a better solution.

See my reply to your other post.

 

[JE McGimpsey]

In a large spread sheet (11000 rows x 20 columns) calculation was pretty
speedy.

I then added some sections with several frequency arrays and averaged the
resulting frequencies. Next I created a couple of pie charts based on these
arrays, percentages and averages etc.

Now a goal seek and recalculation takes forever ( I actually had to force
quit). If I copy and paste back only the values of the arrays and associated
averages, the calculation goes back to normal.

One thing you might check is if you are calculating values multiple
times, you can reduce the calc time if you calc it once and store it.

See Charles William's site for tips:

http://www.decisionmodels.com/optspeed.htm

for more explanation.

 

[Harvey Waxman]

One thing you might check is if you are calculating values multiple
times, you can reduce the calc time if you calc it once and store it.

This is probably where the slowdown is. The frequency formulas along with the
averages of each of the frequency elements needs to be recalculated whenever
the source data is altered.

The VBA is a good idea.

I tried copying the formulas to a new sheet in the same relative location and
deleted the area on the main sheet.

As expected, the sheet calculated right away.

I then copied the formulas back. I works fine this way. I'd like to know if
there is any way to change those formulas so they might be less burdensome.


--
Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me

 

[JE McGimpsey]

I then copied the formulas back. I works fine this way. I'd like to know if
there is any way to change those formulas so they might be less burdensome.

After looking at the site I posted, if you still need help optimizing,
post a sample of the functions you're using that have repetitive
elements (or that are still slowing things down).

 

[Harvey Waxman]

After looking at the site I posted, if you still need help optimizing,
post a sample of the functions you're using that have repetitive
elements (or that are still slowing things down).

Q1 contains list of seven bins
R1: (all array entered)
=FREQUENCY(R11:R10786,Q1:Q6) copied down next six cells (array entered)
S1: (all array entered)
=FREQUENCY(S11:S10786,Q1:Q6) copied down next six cells (array entered)
T1: (all array entered)
=AVERAGE(IF($R11:$R$10786<=Q1,$R11:$R$10786)) (array entered)
=AVERAGE(IF(($R$11:$R$10786>Q1)*($R$11:$R$10786<=Q2),$R$11:$R$10786))
copied down next five cells
U1: (all array entered)
=AVERAGE(IF($S11:$S$10786<=Q1,$S11:$S$10786)) (array entered)
=AVERAGE(IF(($S$11:$S$10786>Q1)*($S$11:$S$10786<=Q2),$S$11:$S$10786))
copied down next five cells

Sorry for being such a pain. BTW, I still can't translate the 'average'
formulas into English but they work perfectly.

Thanks


--
Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me

 

[JE McGimpsey]

Q1 contains list of seven bins
R1: (all array entered)
=FREQUENCY(R11:R10786,Q1:Q6) copied down next six cells (array entered)
S1: (all array entered)
=FREQUENCY(S11:S10786,Q1:Q6) copied down next six cells (array entered)
T1: (all array entered)
=AVERAGE(IF($R11:$R$10786<=Q1,$R11:$R$10786)) (array entered)
=AVERAGE(IF(($R$11:$R$10786>Q1)*($R$11:$R$10786<=Q2),$R$11:$R$10786))
copied down next five cells
U1: (all array entered)
=AVERAGE(IF($S11:$S$10786<=Q1,$S11:$S$10786)) (array entered)
=AVERAGE(IF(($S$11:$S$10786>Q1)*($S$11:$S$10786<=Q2),$S$11:$S$10786))
copied down next five cells

Array functions are convenient, but they don't do much for calculation
time. In fact, they can be much slower when data changes only a small
number of cells at a time.

For instance, if you change R12, then R10000, the array formulae would
calculate the entire 10700+ cell array (twice for each array formula
containing R11:R10786).

If instead, you had a helper column, say column Z, with the formula

Z11: =If(R11<$Q$1,R11,"")

and copy down.

Each of these 10700+ functions will only get recalculated if their
corresponding cell in R11 or the value in Q1 is changed, then the
average (which would then be

=AVERAGE(Z11:Z10786)

would calculate the average of column Z.

With more formulae, the file will be larger, but it will calculate
faster.

 

[Harvey Waxman]

Array functions are convenient, but they don't do much for calculation
time. In fact, they can be much slower when data changes only a small
number of cells at a time.

For instance, if you change R12, then R10000, the array formulae would
calculate the entire 10700+ cell array (twice for each array formula
containing R11:R10786).

If instead, you had a helper column, say column Z, with the formula

Z11: =If(R11<$Q$1,R11,"")

and copy down.

Each of these 10700+ functions will only get recalculated if their
corresponding cell in R11 or the value in Q1 is changed, then the
average (which would then be

=AVERAGE(Z11:Z10786)

would calculate the average of column Z.

With more formulae, the file will be larger, but it will calculate
faster.

I'd get the same information with a column for each bin, average each column
separately, countif each separately and remove the array formulas entirely. Is
that right?

I guess the tradeoff depends on the number of rows of data? which scenarios
are changed most often etc?

Good analysis as always, thanks


--
Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Ladies' handbags and accessories
http://www.deducktibles.com
Remove thefrown to email me