If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed.
I am comparing the returns of several fixed-income funds and since their inception (founding) dates differ, I have columns where some have over 300 values while others have a mere 30.
I need to do the following equation for every column (from A2:A300:
= (1+(A2/100))*(1+(A3/100))...etc...(1+(A299/100))*(1+(A300/100))
I tried to do the first 50 manually, but the forumula ended up being too long.
Would the fixed-income funds that were only recently established (i.e. only have values for A260:A300) screw up any equation because of blank fields?
Please see the excel file for the problems I am having (marked in orange)
Any help is greatly appreciated - just spent 2 hrs googling and it seems most people have problems with basics and not 'complex' equations such as the ones above.
the first question, I think, you should use UDF in VBA
the second question, I do not understand clearly, you may use IF() function for BLANK condition
1 - Could you please elaborate on the UDF in VBA approach?
2 - The 'constant' refers to a value that remains constant e.g. 5, 7 or 11
With regards to the 'blank fields' - I mean that some columns begin at A230 or A250 while others begin at A2 - so ideally the formula would take that into account when e.g. averaging. (the AVG (A2:A300))
2 - The 'constant' refers to a value that remains constant e.g. 5, 7 or 11
With regards to the 'blank fields' - I mean that some columns begin at A230 or A250 while others begin at A2 - so ideally the formula would take that into account when e.g. averaging. (the AVG (A2:A300))
YOU should post a demo file that others can help you
Plz, see the attached file that I solved it and left the detail explanation and also shortcomings (in Geometric Mean Return - try to see again your formula)
and post your ideas so that others can help to solve the shortcomming
Linear Mode
