Returns Calculation Using Sumproduct |
Returns Calculation Using Sumproduct - Excel |
|
Dates are down column A
I want colum J to give me the weighted return for the streams of returns.
But it must re-allocate to funds that have performance and not count funds
that do not. So the Dec-01 portfolio return should be 50% fund 2, and 50%
Fund 3, and 0% fund 1
the allocations are in row 45 and should be flexible to be adjusted as
needed.
THANKS!
Fund1 Fund2 Fund 3 Portfolio
Dec-01 -1.88% 0.36% -0.51%
Jan-02 1.51% 1.85% 1.12%
Feb-02 0.06% 0.16% 0.07%
Mar-02 -0.66% 0.63% -0.01%
Apr-02 1.00% 1.20% 0.73%
May-02 0.99% 1.40% 0.80%
Jun-02 1.27% 1.20% 0.82%
Jul-02 1.22% 0.06% 0.43%
Aug-02 1.17% 1.02% 0.73%
Sep-02 3.27% 1.70% 1.66%
Oct-02 4.51% 1.03% 1.85%
Nov-02 2.28% 1.50% 1.26%
Dec-02 1.38% 0.79% 0.72%
Jan-03 2.80% 1.58% 1.46%
Feb-03 1.31% 1.00% 0.77%
Mar-03 1.05% 0.18% 0.41%
Apr-03 3.20% 0.71% 1.30%
May-03 3.13% 0.58% 1.24%
Jun-03 2.13% -1.32% -2.54% -0.58%
Jul-03 3.33% -0.79% -1.94% 0.20%
Aug-03 1.79% -0.40% -0.73% 0.22%
Sep-03 2.98% 2.16% 3.12% 2.75%
Oct-03 1.35% 3.83% 1.38% 2.19%
Nov-03 2.27% 1.92% 1.12% 1.77%
Dec-03 1.05% 1.36% 1.04% 1.15%
Jan-04 1.32% 2.21% 0.88% 1.47%
Feb-04 0.20% 0.07% -0.18% 0.03%
Mar-04 1.19% 0.61% 0.77% 0.86%
Apr-04 1.15% 0.55% -0.10% 0.53%
May-04 -0.85% -0.97% -1.82% -1.21%
Jun-04 -0.76% 0.03% -0.74% -0.49%
Jul-04 0.92% 0.74% 1.25% 0.97%
Aug-04 0.65% 0.59% 1.32% 0.85%
Sep-04 2.87% 0.41% 0.20% 1.16%
Oct-04 1.00% 2.11% -0.42% 0.90%
Nov-04 1.65% 2.15% 0.85% 1.55%
Dec-04 1.79% 1.40% 0.91% 1.37%
Allocations 33.3% 33.3% 33.3% 100.00%
Similar Topics
=IF(a1=0,"ERROR","")
The trailing "" returns an empty cell. The problem is, it is not TRULY empty. If I fill that formula down, I cannot, for example, jump from one "ERROR" cell to the next by hitting Ctrl arrow-down because it seems to think that these empty cells have contents.
Is there a way to designate, in such a formula as above, to return a TRULY empty cell?
Thanks
date range that was provided, and to return Early, Late or Ok accordingly.
Im looking for a vlookup formula which will give me the maximum number from a list which contains multiple matches, i.e.
Lookup number 1 from column A, then give me the largest number from column B
example table:
A B
-----
1 5
2 2
1 11
3 2
4 5
the result would be 11
thanks all!
I wonder if you could help with this one, I have a list of data like this (no spaces or other characters)
JN551122B
What I need to do is split each of the characters in to its own cell
if JN551122B was in cell A1 I'd want to return:
B2 C2 D2 E2 F2 G2 H2 I2 J2
J N 5 5 1 1 2 2 B
The nearest I have got is the right function, but it brings over the preceeding characters, and I need each character in its own cell. Hopefully I could copy/paste a formula and change the reference for each cell value, i.e. 1 would return J, 2 = N, 3 = 5, 4 = 5 etc.
Thanks for your help!
I have the AutoOpen code that starts things off, and then, in turn, loads the UserForm with the fields that need to be populated.
I can't figure out how to route the user back to the UserForm, to enter the 'missed' data.
Cells accept hours over 23,
Adding cells in column returns correct total time.
Have not found a way to multiply these cells by a $ hourly rate.
So use HOUR(cell ref) and MINUTE(cell ref) to capture values in referenced
cell - then use these values to calculate total payment for $rate per hour.
However, the HOUR(cell ref) formula returns the hours in excess of 24 when
the cell contains an hour value in excess of 23 (ie 27 hours returns 3).
Many Thanks
Karamazov
I'm working with Excel 2003 sp 3 on Windows xp.
I have been banging my head trying to jar loose the formula to calculate man days where 1 equals 8 hours. I have one that was given to me by a coworker:
=INT(Q3/8)+(((Q3/8)-(INT(Q3/8)))*0.8)
That calculates man days from total hours. I need a formula that will total man days and return the man day answer. Example:
weekending 12/25 = 16.2 which is 16 man days and 2 hours
weekending 1/1 = 24.7 which is 24 man days and 7 hours
Total = 41 man days and 1 hour
I have also been asked if there was a way for a number to be entered into a spreadsheet and automatically put in order without a macro or "pushing a button".
Any advice is greatly appreciated!
Happy New Year!
~ Brenda ~
I want to know how to copy part of a cell and paste it into a new cell.
Example:
a1= 8hAs7c
portion needed: 7c
I used this formula:
=RIGHT(A1,2)
This returns 7c like I want.
However in the next cell I want to source the middle two (As)
And in another cell I want the first two (8h)
How do I go about this?
I hope you can help me with what I would like to do. I've searched and searched and not come up with anything yet!!
I have one sheet which is my 'data' sheet with a number of columns, one of which is a date which may be repeated.
I then want to use another sheet with two cells where two dates can be entered and a button which allows you to copy the rows of data from the data sheet which have dates between the two specified originally. The list in the data sheet may not be in date order although I could update a macro to do this for me if needed. I would also like to do it so that if "ALL" is typed in the date fields, it pulls over ALL the data. (Or something similar)
Any help you can give would be much appreciated.
Cheers,
Kevin
Is there a formula so that when I drag C1 horizontally into D1, E1, F1, ..., the values placed in each cell will be =A2, =A3, =A4, ...
I do not want to transpose the values from column 1 into C1, D1,.... I want these cells to have a formula that links them up to column 1's values
Thanks
For example " If Cell A5 has been marked with ' X ', then copy what is in Cell A6 to
cell C25 "
My question is this - I have a large spreadsheet with multiple columns of information. I want to sort it by a certain column. I know how to do this. My question is, once I'm done with my work I want to sort it back to the original "sort" but I'm not sure how they have it sorted. I've tried to figure out how they have it sorted but can't. Is there some way to go back to the original sort?
Thank you for your anticipated help.
Jena
But it gets even more weird. When I click on the Column C cells and then it shows the formula up top in the formula bar and if I put my cursor anywhere in the formula bar and hit Return the formula does not change however the correct value then appears in the Column C cell. It is like the act of putting the cursor in the formula activates it to work properly but until it is activated that C cell shows the value of the cell which it was dragged down from.
This is quite bizarre. Has anyone ever seen this before? I have no idea what is going on. I ran a scan for viruses and none were found. I tried it on several new/different spreadsheets but it keeps happening.
Thanks for any tips on this.
E.g.
Manchester United 3 - 0 Arsenal
Prediction: 4 - 1
Points: 1
I've found a formula online that works for most scores; the correct score (e.g. 3 - 0), a correct win (e.g. 4 - 1), and in the case of a draw (e.g Man Utd 1 - 1 Arsenal, and the prediction 2 -2), correct draw (but not the correct score). The points are as follows: Correct draw (Prediction = result): 4 Correct win (Prediction = result): 3 Correct win, incorrect score (Winner correct, score not): 1 Correct draw, incorrect sco 1
The formula I found online for all but the correct draw is: =IF(A3&B3=C3&D3),3,IF(AND(A3=B3,C3=D3),1,IF(AND(A3>B3,C3>D3),1,IF(AND(A3<B3,C3<D3),1,0)))))
Could someone help me work out how I can write a formula to add before the above that will check the contents of four cells (the home score, away score, home prediction, away prediction), and if the number in all 4 cells are the same, allocate 4 points? I've tried using averages, but some combinations would void this. I've also used the SUM function, but the same happens, as with MULTIPLYING.
Something like =IF(A3:D3) are equal, 4,0
Below are a few examples of what I mean:
(Result) (Prediction) (Points)
A B C D E
3 - 3 3 - 3 4
2 - 1 3 - 1 1
1 - 0 1 - 0 3
3 - 3 2 - 2 1
Apologies in advance if I haven't explained this clearly enough.
Thanks
Will
triangle. I copied it to create a new data set and used find &
replace to change the worksheet references to the new ones.
The cells still contain the result of the old formula referring to the
previous worksheets. The only way I can get the formula to return the
correct result is to edit (F2) each cell and press enter. Calc now
(F9) does nothing.
I've seen this before, but this time, I need to calculate many
thousands of cells and don't have time for this workaround.
Any ideas?
Thanks.
Don S
The Geomean function doesn't work because it only uses positive numbers.
Any help would be greatly appreciated.
What is the origin of the extra plus sign at the beginning? To me it seems to be unproductive and is simply not needed. I have been using Excel for over 12 years and have never needed to enter any formulas like that. Does this originate back to a much older version of Excel where it was once needed?
Just curious.
Thanks.
Can somebody help me, i'm new to excel!