desparately need help simplifying this formula

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

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
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

New Member
Joined
Sep 20, 2006
Messages
3
Thanks. That's about all I could see, too. I guess the formula is just destined to be way long.

Thanks again
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
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

New Member
Joined
Sep 20, 2006
Messages
3
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!
 

Forum statistics

Threads
1,136,651
Messages
5,677,004
Members
419,667
Latest member
MegEri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top