Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
A subset of my data starts out the same in a text column, Column A.
The last part of the text is different. Nevertheless, I want to run a SUMPRODUCT function against columns that meet that criterion in Column A. That is, for lines where Column A starts out "XYZ " below. I'm trying to find the average days held for stock meeting my criterion, weighted by cost for the transaction. A ... E ... N Descr. Cost Days Held ======== ======= ========= XYZ ABCD 165.46 98 XYZ BCDE 195.62 15 XYZ CDEF 1240.54 42 I am able to do this weighted average of days held easily for the rest of my data. E.g.: =SUMPRODUCT(--($A$2:$A594=$A594),--($N$2:$N594),--($E$2:$E594))/$E595 (where E595 is the total cost for stock matching the description in Column A). But this one part of the data has only the beginning of the description match, not the whole field. I'm stuck. I tried some things with LEFT and with IF, but couldn't get it to work. Help appreciated! =dman= |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
If I understand what you want try this:
=SUMPRODUCT(--(LEFT($A$2:$A594,3)=$A594),$E$2:$E594,$N$2:$N594)/$E595 -- Biff Microsoft Excel MVP "Dallman Ross" <dman@localhost. wrote in message ... A subset of my data starts out the same in a text column, Column A. The last part of the text is different. Nevertheless, I want to run a SUMPRODUCT function against columns that meet that criterion in Column A. That is, for lines where Column A starts out "XYZ " below. I'm trying to find the average days held for stock meeting my criterion, weighted by cost for the transaction. A ... E ... N Descr. Cost Days Held ======== ======= ========= XYZ ABCD 165.46 98 XYZ BCDE 195.62 15 XYZ CDEF 1240.54 42 I am able to do this weighted average of days held easily for the rest of my data. E.g.: =SUMPRODUCT(--($A$2:$A594=$A594),--($N$2:$N594),--($E$2:$E594))/$E595 (where E595 is the total cost for stock matching the description in Column A). But this one part of the data has only the beginning of the description match, not the whole field. I'm stuck. I tried some things with LEFT and with IF, but couldn't get it to work. Help appreciated! =dman= |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
In , T. Valko
spake thusly: If I understand what you want try this: =SUMPRODUCT(--(LEFT($A$2:$A594,3)=$A594),$E$2:$E594,$N$2:$N594)/$E595 Biff, Doesn't work, unfortunately. Gives me zero. ===================== "Dallman Ross" <dman@localhost. wrote in message ... A subset of my data starts out the same in a text column, Column A. The last part of the text is different. Nevertheless, I want to run a SUMPRODUCT function against columns that meet that criterion in Column A. That is, for lines where Column A starts out "XYZ " below. I'm trying to find the average days held for stock meeting my criterion, weighted by cost for the transaction. A ... E ... N Descr. Cost Days Held ======== ======= ========= XYZ ABCD 165.46 98 XYZ BCDE 195.62 15 XYZ CDEF 1240.54 42 I am able to do this weighted average of days held easily for the rest of my data. E.g.: =SUMPRODUCT(--($A$2:$A594=$A594),--($N$2:$N594),--($E$2:$E594))/$E595 (where E595 is the total cost for stock matching the description in Column A). But this one part of the data has only the beginning of the description match, not the whole field. I'm stuck. I tried some things with LEFT and with IF, but couldn't get it to work. Help appreciated! =dman= |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
In , Dallman Ross <dman@localhost.
spake thusly: In , T. Valko spake thusly: If I understand what you want try this: =SUMPRODUCT(--(LEFT($A$2:$A594,3)=$A594),$E$2:$E594,$N$2:$N594)/$E595 Doesn't work, unfortunately. Gives me zero. Okay, I figured out the problem. It would need to be: =SUMPRODUCT(--(LEFT($A$2:$A594,4)="XYZ "),$E$2:$E594,$N$2:$N594)/$E595 This does work, and I thank you for helping me with it. I was tearing my hair out. Unfortunately, it's not a statement I'll be able to plug in to all the subtotals rows like that. I'd need an IF-statement in there. Wouldn't mind knowing how to do that, though. Basically, IF(ISERROR(FIND("@",A594)),$A$2:$A594=$A594,LEFT($ A$2:$A594,4)=LEFT($A594,4)) where we have the first statement. Ugh. That might even be right, but it's making my head spin. Holy cow. I plugged that in and, after I found a parens error (now fixed above), it actually works. Geez, I'm kind of stoked. I had been trying for about four hours. Your help was the key. Meanwhile, though, I decided to make my life easier and insert a helper column. So I basically don't necessarily need that anymore. Maybe I'll put that back in and ditch the helper column, though. :-) The formula that works -- real thing, not fake example -- is: =SUMPRODUCT(--(IF(ISERROR(FIND("@",A594)),$A$2:$A594=$A594,LEFT( $A$2:$A594,6)=LEFT($A594,6))),--($O$2:$O594),--($F$2:$F594))/F595 (The columns are shifted over now because of the helper column.) It's also an array formula, as I'm sure you can tell. I suppose I could get rid of the "--(123)" around the columns that are actually numbers instead of text -- as you did. That would make it 8 chars shorter and lose a couple of confusing internal paren sets. :-) Here's what's in that helper column (B): =IF(ISERROR(FIND("@",A595)),A595,LEFT(A595,6)) And here's how the SUMPRODUCT formula looks using that instead of the above: =SUMPRODUCT(--($B$2:$B594=$B594),--($O$2:$O594),--($F$2:$F594))/F595 FYI, the cells from Column A that have a "@" symbol in them are puts or calls. All other items in my data are regular stocks. I want to total all the puts together and all the calls together, because otherwise, well, it's just to darn messy, and also not useful information. But all the other data is subtotaled by individual issues. So where I wrote "XYZ ", it actually says " CALL " or " PUT " (two spaces there after; the broker did that, probably for convenience for similar purposes to mine.) Thanks again, Biff. Oh: next question: how can I have it work for the full data range, instead of just the range above the subtotals row? This is so I can sort different ways and not have the data all be contiguous, or at least not all above the particular subtotals row. =dman= ===================== "Dallman Ross" <dman@localhost. wrote in message ... A subset of my data starts out the same in a text column, Column A. The last part of the text is different. Nevertheless, I want to run a SUMPRODUCT function against columns that meet that criterion in Column A. That is, for lines where Column A starts out "XYZ " below. I'm trying to find the average days held for stock meeting my criterion, weighted by cost for the transaction. A ... E ... N Descr. Cost Days Held ======== ======= ========= XYZ ABCD 165.46 98 XYZ BCDE 195.62 15 XYZ CDEF 1240.54 42 I am able to do this weighted average of days held easily for the rest of my data. E.g.: =SUMPRODUCT(--($A$2:$A594=$A594),--($N$2:$N594),--($E$2:$E594))/$E595 (where E595 is the total cost for stock matching the description in Column A). But this one part of the data has only the beginning of the description match, not the whole field. I'm stuck. I tried some things with LEFT and with IF, but couldn't get it to work. Help appreciated! =dman= |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Let's see if I have this straight:
XYZ ABCD 165.46 98 XYZ BCDE 195.62 15 XYZ CDEF 1240.54 42 So where I wrote "XYZ ", it actually says " CALL " or " PUT " Why didn't you just "say" that to begin with? <g Okay, I figured out the problem. It would need to be: =SUMPRODUCT(--(LEFT($A$2:$A594,4)="XYZ "),$E$2:$E594,$N$2:$N594)/$E595 That formula would be the same as: =SUMPRODUCT(--(LEFT($A$2:$A594,3)="XYZ"),$E$2:$E594,$N$2:$N594)/$E595 Unless you need to specically include the trailing space. So, if you need to base this on "call" or "put" try one of these: =SUMPRODUCT((LEFT($A$2:$A594,4)="call")+(LEFT($A$2 :$A594,3)="put"),$E$2:$E594,$N$2:$N594)/$E595 Or: =SUMPRODUCT((ISNUMBER(SEARCH({"call","put"},$A$2:$ A594)))*($E$2:$E594)*($N$2:$N594))/$E595 Note the formula above could return incorrect results if the strings might contain either "call" or "put" somewhere else in the string. Like this: AAA CALL ABA CPUT Oh: next question: how can I have it work for the full data range, instead of just the range above the subtotals row? Not sure what you mean by that. -- Biff Microsoft Excel MVP "Dallman Ross" <dman@localhost. wrote in message ... In , Dallman Ross <dman@localhost. spake thusly: In , T. Valko spake thusly: If I understand what you want try this: =SUMPRODUCT(--(LEFT($A$2:$A594,3)=$A594),$E$2:$E594,$N$2:$N594)/$E595 Doesn't work, unfortunately. Gives me zero. Okay, I figured out the problem. It would need to be: =SUMPRODUCT(--(LEFT($A$2:$A594,4)="XYZ "),$E$2:$E594,$N$2:$N594)/$E595 This does work, and I thank you for helping me with it. I was tearing my hair out. Unfortunately, it's not a statement I'll be able to plug in to all the subtotals rows like that. I'd need an IF-statement in there. Wouldn't mind knowing how to do that, though. Basically, IF(ISERROR(FIND("@",A594)),$A$2:$A594=$A594,LEFT($ A$2:$A594,4)=LEFT($A594,4)) where we have the first statement. Ugh. That might even be right, but it's making my head spin. Holy cow. I plugged that in and, after I found a parens error (now fixed above), it actually works. Geez, I'm kind of stoked. I had been trying for about four hours. Your help was the key. Meanwhile, though, I decided to make my life easier and insert a helper column. So I basically don't necessarily need that anymore. Maybe I'll put that back in and ditch the helper column, though. :-) The formula that works -- real thing, not fake example -- is: =SUMPRODUCT(--(IF(ISERROR(FIND("@",A594)),$A$2:$A594=$A594,LEFT( $A$2:$A594,6)=LEFT($A594,6))),--($O$2:$O594),--($F$2:$F594))/F595 (The columns are shifted over now because of the helper column.) It's also an array formula, as I'm sure you can tell. I suppose I could get rid of the "--(123)" around the columns that are actually numbers instead of text -- as you did. That would make it 8 chars shorter and lose a couple of confusing internal paren sets. :-) Here's what's in that helper column (B): =IF(ISERROR(FIND("@",A595)),A595,LEFT(A595,6)) And here's how the SUMPRODUCT formula looks using that instead of the above: =SUMPRODUCT(--($B$2:$B594=$B594),--($O$2:$O594),--($F$2:$F594))/F595 FYI, the cells from Column A that have a "@" symbol in them are puts or calls. All other items in my data are regular stocks. I want to total all the puts together and all the calls together, because otherwise, well, it's just to darn messy, and also not useful information. But all the other data is subtotaled by individual issues. So where I wrote "XYZ ", it actually says " CALL " or " PUT " (two spaces there after; the broker did that, probably for convenience for similar purposes to mine.) Thanks again, Biff. Oh: next question: how can I have it work for the full data range, instead of just the range above the subtotals row? This is so I can sort different ways and not have the data all be contiguous, or at least not all above the particular subtotals row. =dman= ===================== "Dallman Ross" <dman@localhost. wrote in message ... A subset of my data starts out the same in a text column, Column A. The last part of the text is different. Nevertheless, I want to run a SUMPRODUCT function against columns that meet that criterion in Column A. That is, for lines where Column A starts out "XYZ " below. I'm trying to find the average days held for stock meeting my criterion, weighted by cost for the transaction. A ... E ... N Descr. Cost Days Held ======== ======= ========= XYZ ABCD 165.46 98 XYZ BCDE 195.62 15 XYZ CDEF 1240.54 42 I am able to do this weighted average of days held easily for the rest of my data. E.g.: =SUMPRODUCT(--($A$2:$A594=$A594),--($N$2:$N594),--($E$2:$E594))/$E595 (where E595 is the total cost for stock matching the description in Column A). But this one part of the data has only the beginning of the description match, not the whole field. I'm stuck. I tried some things with LEFT and with IF, but couldn't get it to work. Help appreciated! =dman= |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Biff, just wanted to let you know I'm not ignoring you. :-)
I sometimes need a few days to digest these ideas, or to find time to study them with a clear head. I'll try to work through this soon and report back. I think you hit the nail on the head, though. Your yeoman help is of course much appreciated. =dman= ========================================= In , T. Valko spake thusly: Let's see if I have this straight: XYZ ABCD 165.46 98 XYZ BCDE 195.62 15 XYZ CDEF 1240.54 42 So where I wrote "XYZ ", it actually says " CALL " or " PUT " Why didn't you just "say" that to begin with? <g Okay, I figured out the problem. It would need to be: =SUMPRODUCT(--(LEFT($A$2:$A594,4)="XYZ "),$E$2:$E594,$N$2:$N594)/$E595 That formula would be the same as: =SUMPRODUCT(--(LEFT($A$2:$A594,3)="XYZ"),$E$2:$E594,$N$2:$N594)/$E595 Unless you need to specically include the trailing space. So, if you need to base this on "call" or "put" try one of these: =SUMPRODUCT((LEFT($A$2:$A594,4)="call")+(LEFT($A$2 :$A594,3)="put"),$E$2:$E594,$N$2:$N594)/$E595 Or: =SUMPRODUCT((ISNUMBER(SEARCH({"call","put"},$A$2:$ A594)))*($E$2:$E594)*($N$2:$N594))/$E595 Note the formula above could return incorrect results if the strings might contain either "call" or "put" somewhere else in the string. Like this: AAA CALL ABA CPUT Oh: next question: how can I have it work for the full data range, instead of just the range above the subtotals row? Not sure what you mean by that. |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Nearly a month ago (on 13 August 2007)
in , T. Valko spake thusly: Let's see if I have this straight: XYZ ABCD 165.46 98 XYZ BCDE 195.62 15 XYZ CDEF 1240.54 42 So where I wrote "XYZ ", it actually says " CALL " or " PUT " Why didn't you just "say" that to begin with? <g Hi, "Biff" (or is it "T"?), :-) sorry for not answering sooner. I kept marking this article unread until I had time to get back to it. A vacation and then new work interrupted the flow. I want to say that I really appreciated your input last month. It was extremely helpful. I was pretty stuck for a couple of days. Your ideas helped me straighten out my thinking and provided needed clues. Answering your question: I didn't say the exact details because I was after a general solution to a common concept, not necessarily just a one-shot answer to a seldom-repeating circumstance. I felt able to take answers provided and apply them to my specifics on my own. But if you feel I kept you from seeing the overview by my vagueness, then I apologize. Okay, I figured out the problem. It would need to be: =SUMPRODUCT(--(LEFT($A$2:$A594,4)="XYZ "),$E$2:$E594,$N$2:$N594)/$E595 That formula would be the same as: =SUMPRODUCT(--(LEFT($A$2:$A594,3)="XYZ"),$E$2:$E594,$N$2:$N594)/$E595 Unless you need to specically include the trailing space. I did, indeed, want to include the trailing space, as a data-integrity check. That's because there are many hundreds of lines and there can be other text that is very similar. This is a table of stock transactions, and we're in the "company name" column. For example, Suppose -- as is the case here -- I have decided to treat stock options (calls and puts) differently formulaically from regular stocks. If I just test for a description that starts with "call" or "put" I would leave myself open for a serious problem were I also to trade in the NYSE listed issues CALLON PETROLEUM CO or CALLAWAY GOLF CO. Luckily, the options rows also all have an "@" symbol in them later on, and the regular stocks don't (that I've ever seen yet), so I do have other ways to help me with data validation as well. Example: PUT IBM JAN 08 @ 110.00 (Actually, the CSV file the broker provides has a leading space as well befor all transactions, so it's actually " CALL " or " PUT " [two spaces after "PUT"; I presume the database guys at Smith Barney did that to make it easier to to char-count-based work on the column]. The leading space was added about 1.5 years ago with no notice when the web pages were all revamped, and it threw me off badly for a couple of days until I could figure out why my Excel stuff suddenly wasn't working, I can tell you!) So, if you need to base this on "call" or "put" try one of these: =SUMPRODUCT((LEFT($A$2:$A594,4)="call")+(LEFT($A$2 :$A594,3)="put"),$E$2:$E594,$N$2:$N594)/$E595 Or: =SUMPRODUCT((ISNUMBER(SEARCH({"call","put"},$A$2:$ A594)))*($E$2:$E594)*($N$2:$N594))/$E595 Note the formula above could return incorrect results if the strings might contain either "call" or "put" somewhere else in the string. Like this: AAA CALL ABA CPUT Indeed possible. "APPLE COMPUTER" . . . :-) Oh: next question: how can I have it work for the full data range, instead of just the range above the subtotals row? Not sure what you mean by that. I meant I have been using relative refs from the top of the sheet to the current row to do subtotals, operating on the presumption that things are already sorted so that this works. But I wanted the freedom to have correct subtotals from a subtotal row I've added the middle of the table that nevertheless looks at data from the top to the bottom of the sheet. I've meanwhile got that sheet working really nicely now without that feature, and have decided I'm not very likely to want it anyway and it's a bother to program. I know theoretically how I might approach it algorithmically. But for now, I'm content to let sleeping dogs lie. :-) Best, Dallman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
SumProduct | Excel Discussion (Misc queries) | |||
Like Sumproduct, But Different | Excel Worksheet Functions | |||
HELP!!! On SumProduct | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions |