desparately need help simplifying this formula

[albinorhino]

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!!

 

[RalphA]

Assuming your formula is correct, you could simplify it, somewhat, by using an empty cell, say C6, as follows:
In C6, enter, =C4+C5*C3

Now, rewrite your formula, thus:
=(((INDIRECT("$C$"&ROW()+$C$4):INDIRECT("$C$"&ROW()+$C$6-1))/(INDIRECT("$C$"&ROW()+1+$C$4):INDIRECT("$C$"&ROW()+$C$6))-1)-AVERAGE(((INDIRECT("$C$"&ROW()+$C$4):INDIRECT("$C$"&ROW()+$C$6-1))/(INDIRECT("$C$"&ROW()+1+$C$4):INDIRECT("$C$"&ROW()+$C$6))-1)))

Does this satisfy your requirements?

 

[albinorhino]

Thanks. That's about all I could see, too. I guess the formula is just destined to be way long.

Thanks again

 

[RalphA]

Another possible approach would be to add a few more cells on each row, say, for row 10, if your formula is in cell D10, and cells to the right ofare not used, you could, say:
In cell E10, enter: =INDIRECT("$C$"&ROW()+$C$4)
In cell F10, enter: =INDIRECT("$C$"&ROW()+$$C$6-1)
In cell G10, enter: =INDIRECT("$C$"&ROW()+1+$C$4)
In cell H10, enter: =INDIRECT("$C$"&ROW()+$$C$6)
Now, your main formula for row 10 can be re-written as:
=(E10:F10)/(G10:H10-1)-AVERAGE(etc., do you get the drift?

How about this?

 

[albinorhino]

Yes, thank you both. The formula is meant to condense multiple different operations into one cell, with adaptive ranges based on the inputs. Unfortunately, to farm out things to other columns is not going to help. I had played around with relocating the indirects, but I think I'll just have to make the thing mammoth and hope to never have to edit it. (what I've posted is only the first operation of three)

Thanks for the help!