# desparately need help simplifying this formula

#### albinorhino

##### New Member
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.

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.

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

=(((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)))

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

Thanks again

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?

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!

Replies
2
Views
298
Replies
3
Views
243
Replies
1
Views
157
Replies
2
Views
300
Replies
5
Views
332

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.

### Which adblocker are you using?

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

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