In , David Benson
spake thusly:

One approach would be to use array formulas. If the full range
of data extends from Row 1 to Row 200, the formula in Coll O38
would be:

=SUMPRODUCT(if(O38=$A$1:$A$200,$P$1:$P$200,0),if(O 38=$A$1:$A$200,$F$1:$F$200,0))/SUM(if(O38=$A$1:$A$200,$F$1:$F$200,0))

After you type in this formula, press <CTRL<SHIFT<ENTER -- all
three keys at the same time. You will see curly brackets ("{"
and "}") appear around the expression, indicating it is an array
formula.

David,

Thanks. I wrote a draft answer here describing how, though it looked
promising, I couldn't get it to work. Then I stared some more at it
and, after some time, figured out how to change it so that it does work.
It is, indeed, better than my solution, for the cases when I sort in
other ways and the categories of things in Column "A" are not contiguous.

First I'll post here what I drafted to say before I figured out the
tweak I needed:

I . . . can't understand the logic, I'm afraid. Let me also state here
that I screwed up when I used Column "F" in my working sample -- should
have been Column "G". Not important, but I'll refer to F's as G's from
here on out to keep my sanity. :-)

Since Column "A" contains a text string and Column "O" contains
a calculated percent, I can't figure out how "O38=$A$1:$A$200",
for example, is ever supposed to be true.

Repeating what I'm after conceptually, I want "running" weighted
averages for fields in Column "O" based on the data in the same
rows in Columns "P" and "G" -- but only for rows with the same
string values in Column "A".

I chose SUMPRODUCT to give me the weighted averages, as in

=SUMPRODUCT($P$2:$Pxxx,$G$2:$Gxxx)/SUM($G2:$Gxxx)

where "xxx" is the current row. (Data starts in Row 2, as there
are headers in Row 1.)

Okay, I stared until I believed I figured out what you were trying
to tell me. :-) Here is the working array formula from O38 now:

=SUMPRODUCT(IF($A58=$A$2:$A58,$P$2:$P58,),IF($A58= $A$2:$A58,$G$2:$G58,))/SUMPRODUCT(IF($A58=$A$2:$A58,$G$2:$G58,))

A note about array formulas: If you copy the formula to other
cells, be sure and do a Copy Paste Special Formulas, not just
a regular Copy Paste (you can also Paste Special Formats
if you need to replicate formats). If you do a regular Copy
Paste, you may be designating the entire range covered by the
Paste as an array, which means you will then be unable to modify
the formula in individual cells.

I got it to work! Thanks. But I'm still a bit unclear on array
formulas. Why are they needed, and what's different about them?
When I'm editing stuff, what should I watch out for?

Thank you very much, David.

----------------
"Dallman Ross" <dman@localhost. wrote in message
...
Assume a table of data sort of like this:


A ... G ... O P

1 Stock Cost Running Avg Nominal Rate
Return by Issue of Return

2 Foo 3555.20 6.77% 6.77%
3 Foo 3225.29 2.23% -2.30%
4 Foo 2708.08 2.39% 2.71%
5 Foo 3838.93 2.65% 7.62%
--------------------------------------------------
6 Bar 12984.44 11.77% 11.77%
7 Bar 11543.87 14.67% 17.73%
8 Bar 11543.87 15.56% 17.38%


(The data in "O" and "P" are actual samples.) I'm trying
to devise a formula to do the work automatically. When
a new stock shows up in Column "A," the running average
should reset -- as it does here in Row 6.

Right now, Column "O" has a formula that looks like this
for rows 2 through 38, with this sample being from "O38":

=SUMPRODUCT($P$2:$P38,$F$2:$F38)/SUM($F$2:$F38)

And for rows 39 onward until the next stock appears, it
looks like this:

=SUMPRODUCT($P$39:$P39,$F$39:$F39)/SUM($F$39:$F39)

I had to adjust the start of the range manually when I
added data to the table. I don't want to have to do that
anymore. I want the formula to keep track of when the
value in Column "A" changes. I'm stuck . . .

Thanks for any help!

Dallman Ross