Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT clue needed
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT clue needed
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. ------------------ In , Dallman Ross <dman@localhost. spake thusly: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT clue needed
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(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. 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" <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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT clue needed
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT clue needed
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" <dman@localhost. wrote in message ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT clue needed
In , David Benson
spake thusly: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Min/Max help needed with a SumProduct | Excel Worksheet Functions | |||
repost: Min/Max help needed with a SumProduct | Excel Worksheet Functions | |||
Sumproduct Help Needed | Excel Discussion (Misc queries) | |||
SUMPRODUCT help needed | Excel Worksheet Functions | |||
Sumproduct help needed! | Excel Worksheet Functions |