SUMPRODUCT clue needed

[Dallman Ross]

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

 

[Dallman Ross]

Wow, but I believe I have figured it out. I put this
in, e.g., O39:

=SUMPRODUCT(OFFSET($P$1,MATCH($A39,$A:$A,0)-1,):$P39,OFFSET($F$1,MATCH($A39,$A:$A,0)-1,):$F39)/SUM(OFFSET($F$1,MATCH($A39,$A:$A,0)-1,):$F39)

If anyone has a better way, I'm all ears. But this is working.

 

[David Benson]

Dallman,

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(O38=$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.

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.

Good luck!

-- David

 

[Dallman Ross]

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(O38=$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.

----------------

 

[David Benson]

Dallman,

Oops -- so sorry about the typo in the formula I suggested. I'm glad you
were able to figure out what I _meant_, as opposed to what I _typed_!

There are two kinds of array formulas. One kind operates on an array of
inputs. This is the kind I suggested, and is by far the more useful of the
two. The other kind produces an array of outputs. One example of the
latter is the LINEST function. It produces an output array that gives you a
bunch of statistical data resulting from a linear curve fit.

In the case of the function I suggested, each of the "IF" functions produces
an array whose values depend on the condition specified by the "IF". The
first "IF" function looks at the values in Column A in Rows 1-200. If the
value in Column A equals the designated value (the value in Column A in the
row in which the formula is entered), then the "IF" returns the
corresponding value from Column P; otherwise, the "IF" function returns a
zero ("0"). The second "IF" statement does the same comparison, returning
values from Column F. The SUPRODUCT function then uses these two sets of
values to produce the final result.

You have to be a little careful how you enter array formulas because there
are two kinds of formulas, as I described above. If you copy an array
formula to a range using an ordinary Copy > Paste, Excel often decides that
you are creating the second kind of formula -- that is, a single formula
that produces an array of outputs. The result is that the entire range is
treated as a single function, and you can't change the formula in individual
cells within that range. Suppose you entered the formula in Cell O38, then
wanted to copy it into Cells O1:O37. After an ordinary Copy > Paste, all of
the cells in O1:O37 could be treated as a single array. To change the
formula, you would have to highlight the entire range and then make the
change. I find that it quickly gets to be impossible to remember exactly
which cells are included in what arrays, which makes editing a real
nightmare.

As you discovered, the beauty of array formulas is that they relieve the
user of the burden of insuring that the input data stays in a particular
order and a particular location. You can insert or delete rows to your
heart's content, and the array formula will still work.

Good luck!

-- David

 

[Dallman Ross]

Oops -- so sorry about the typo in the formula I suggested. I'm
glad you were able to figure out what I _meant_, as opposed to
what I _typed_!

Thanks for the good explanation (snipped here), David. Appreciated.

I've run up against a new problem now that I've adopted the array
formula. I have a macro that inserts new data. It's painstakingly
created to move a known existing line to the bottom of the newly
elongated data range, so that my dynamic charts will still work
right. (That was a week-long pain in the rear for me to get right.)
Then the macro copies a good row from the top and pastes it all
the way down in the new range. Again, the whole point is to keep
my charts working right with the new data of variable length.
Well, now with an array formula in the works, I can't paste
multiple rows anymore! I get an error message. I'm trying to
recode my macro to use drag-and-fill instead, but I'm having
trouble getting the syntax to work. I don't know the length of
the data in advance, and the macro has to figure it out and drag
only so far.

Dallman