albinorhino
New Member
- Joined
- Sep 20, 2006
- Messages
- 3
The following formula employs a mixture of array formulas and a bunch of indirects to feed inputs into a rolling financial return calculation based on a column of numbers in column C. It's going to get even more inputs, but before it does, I'm having a tough time fitting it on the screen to even edit.
Can someone please help me? The indirects form rolling ranges and I want to throw them in separate cells becuase the text is always the same, I just can't get the combined syntax correct or something.
Please help?
Here is the ugly beast:
=(((INDIRECT("$C$"&ROW()+$C$4):INDIRECT("$C$"&ROW()+$C$4+$C$5*$C$3-1))/(INDIRECT("$C$"&ROW()+1+$C$4):INDIRECT("$C$"&ROW()+$C$4+$C$5*$C$3))-1)-AVERAGE(((INDIRECT("$C$"&ROW()+$C$4):INDIRECT("$C$"&ROW()+$C$4+$C$5*$C$3-1))/(INDIRECT("$C$"&ROW()+1+$C$4):INDIRECT("$C$"&ROW()+$C$4+$C$5*$C$3))-1)))
Like I said, it's an array formula so if you try it in a spreadsheet without hitting cntrl-shift-enter you'll get an error.
Thanks in advance!!
Can someone please help me? The indirects form rolling ranges and I want to throw them in separate cells becuase the text is always the same, I just can't get the combined syntax correct or something.
Please help?
Here is the ugly beast:
=(((INDIRECT("$C$"&ROW()+$C$4):INDIRECT("$C$"&ROW()+$C$4+$C$5*$C$3-1))/(INDIRECT("$C$"&ROW()+1+$C$4):INDIRECT("$C$"&ROW()+$C$4+$C$5*$C$3))-1)-AVERAGE(((INDIRECT("$C$"&ROW()+$C$4):INDIRECT("$C$"&ROW()+$C$4+$C$5*$C$3-1))/(INDIRECT("$C$"&ROW()+1+$C$4):INDIRECT("$C$"&ROW()+$C$4+$C$5*$C$3))-1)))
Like I said, it's an array formula so if you try it in a spreadsheet without hitting cntrl-shift-enter you'll get an error.
Thanks in advance!!