Hi everyone,
Sorry for the bad english. I am working on a large spreadsheet and need to figure out porfolio returns. Basically I need to sumproduct the weight of each stock with the appropriate return but only if the rank/position matches that of the portofolio (eg. 1,2 or 3). So in the example below (see attachment) for D19 I would need to sumproduct all A.Returns with postition 1 with the weight. Can this be done with the sumproduct?
Attachment 234431
Your attachment doesn't work.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Sorry, I'm new to this.... I cant seem to upload the file.
Think it should be attached now
Last edited by maxfiesta; 05-09-2013 at 11:01 AM.
Maybe with an pivot table.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
try this, copied down and across...
=SUMPRODUCT(($B$3:$B$14=LEFT(D$18,1))*($C$3:$C$14=$C19)*($D$3:$D$14)*($F$3:$F$14))
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Maybe this is what you are looking for:
newdoverman: This only returns the sum of all the labelled 1 returns unfortunately. I need those to be multiplied with the weights.
Last edited by maxfiesta; 05-09-2013 at 12:08 PM.
FDibbins: Sorry, calculates wrong value.
Last edited by maxfiesta; 05-09-2013 at 12:26 PM.
By using newdovermans suggestion of
=SUMPRODUCT(--($C$3:$C$14=$C23);--(D$3:D$14))
and multiplying this value by the weight, it gives me the correct answer.
I do not know how to incorporate this into the formula however.
Thanks for the help to you both.
it doesnt, i tested it. try again
Last edited by FDibbins; 05-09-2013 at 12:58 PM.
Ok, this incorporates the weights.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks