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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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?
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,218,695
Messages
6,143,951
Members
450,517
Latest member
Rovex

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
Back
Top