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!

"Jim Cone" wrote:

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"

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.)