Extremely slow recalculation time

[jday]

I have a spreadsheet containing 25000 rows of data. For each row, I am
trying to calculate a weighted average cost (rate) based upon certain
criteria. Here is an example of the calculation that appears in cell D2
(same basic formula is copied down for all rows):

=iferror(sumif($A$2:$A$25000,$A2,$B$2:$B$25000)/sumif($A$2:$A$25000,$A2,$C$2:$C$25000),0)

Here is an example of what the data in A-C, with the calculated result of my
formula in column D:

A B C D
(calculation)
Jones $10000 1,000 $7.714
Jones $ 5000 2,000 $7.714
Smith $25000 3,000 $7.368
Jones $12000 500 $7.714
Smith $30000 4,000 $7.368
Smith $15000 2,500 $7.368

This formula works fine & gives me the result I need, HOWEVER, the way the
formula gets populated is thru a macro that copies/pastes the formula down
after the "data" (col's A-C) is imported into the worksheet. This copy/paste
process takes a LONG time to recalculate for each of the 100 columns that
contain a similar formula to what I have in column D. The problem is, I
cannot "turn off" the calculation because I need to have the result of some
columns before I can calculate others. The, when the macro is finished
running, it does a copy/paste values over all of the columns to "lock in" the
result.

Can anyone provide a suggestion in terms of a different formula or function
that can help me achieve a more optimal calculation speed? Currently, it
takes nearly 90 minutes to "calculate" this worksheet when the data is
imported (based upon 20-25,000 rows of data x 100 columns of SUMIF
calculations.)

 

[KidontheBlock]

You may want to use countif [=countif($A$2:A2,A2) in cell A2 and
=countif($a$2:A3,A3) in cell A3 and so on]. This is the formula to identify
how many dupliate entries are there in a give range, use that as weight,
obtain a product and then arrive at weighted average.

If this post is helpful, choose yes to close the thread

 

[Jim Cone]

A way...
Sort the data by column A.
Use the built-in Subtotals utility to sum columns B and C at each name change.
Place this formula in column D... =IF(RIGHT(A2,5) = "Total",B2/C2,"")
Fill formula down.

And another way...
Upgrade to XL 2003 from xl 2007
--
Jim Cone
Portland, Oregon USA




"jday"
<[email protected]>
wrote in message
I have a spreadsheet containing 25000 rows of data. For each row, I am
trying to calculate a weighted average cost (rate) based upon certain
criteria. Here is an example of the calculation that appears in cell D2
(same basic formula is copied down for all rows):

=iferror(sumif($A$2:$A$25000,$A2,$B$2:$B$25000)/sumif($A$2:$A$25000,$A2,$C$2:$C$25000),0)

Here is an example of what the data in A-C, with the calculated result of my
formula in column D:
A B C D
(calculation)
Jones $10000 1,000 $7.714
Jones $ 5000 2,000 $7.714
Smith $25000 3,000 $7.368
Jones $12000 500 $7.714
Smith $30000 4,000 $7.368
Smith $15000 2,500 $7.368

This formula works fine & gives me the result I need, HOWEVER, the way the
formula gets populated is thru a macro that copies/pastes the formula down
after the "data" (col's A-C) is imported into the worksheet. This copy/paste
process takes a LONG time to recalculate for each of the 100 columns that
contain a similar formula to what I have in column D. The problem is, I
cannot "turn off" the calculation because I need to have the result of some
columns before I can calculate others. The, when the macro is finished
running, it does a copy/paste values over all of the columns to "lock in" the
result.

Can anyone provide a suggestion in terms of a different formula or function
that can help me achieve a more optimal calculation speed? Currently, it
takes nearly 90 minutes to "calculate" this worksheet when the data is
imported (based upon 20-25,000 rows of data x 100 columns of SUMIF
calculations.)

 

[jday]

Unfortunately the subtotal insertion won't be practical since I actually have
more than 160 columns of data (I just used columns B/C as an example to
simplify my question---assumed I could apply the same formula logic across
all of the columns I actually need it for). I have 160 columns of data that
need to be 'summed' and another 100 columns that contain calculations using
this summed data. I think I'm just gonna have to live with my calculation
speed it sounds like!

P.S. XL 2003 is an upgrade from xl 2007 ?? Unfortunately my company just
upgraded to 2007, so probably will be living with this for awhile!

 

[Niek Otten]

Check Charles William's site for sound advice about Excel performance:

www.decisionmodels.com

 

[Tanya M]

Explore Pivot Tables...

 

[Jim Cone]

For what it is worth...
The Subtotals feature will sum multiple columns.
It will probably do 160 columns.

Further, I haven't used it but one new xl2007 function is SumIfs.
Maybe it would have some application to your problem.

And, xl2007 does much of everything slower; In your case 90 minutes, versus maybe 90 seconds in an earlier version. That's a
downgrade in my opinion.
--
Jim Cone
Portland, Oregon USA



"jday" <[email protected]>
wrote in message
Unfortunately the subtotal insertion won't be practical since I actually have
more than 160 columns of data (I just used columns B/C as an example to
simplify my question---assumed I could apply the same formula logic across
all of the columns I actually need it for). I have 160 columns of data that
need to be 'summed' and another 100 columns that contain calculations using
this summed data. I think I'm just gonna have to live with my calculation
speed it sounds like!

P.S. XL 2003 is an upgrade from xl 2007 ?? Unfortunately my company just
upgraded to 2007, so probably will be living with this for awhile!

 

[Pankaj]

Hi jday,
Just wanted to follow up if you are having trouble with the countif formula
too?

If the post resolved your problem, click 'Yes' to close the thread.