how to define the range of cells for a formula that changes over time

**portanalyst** · 08-14-2011, 11:26 AM

This is hard to explain. I want to know if there's a way to write an excel formula where the range within the formula SUM will change over time. I've got an investment index with all the constituents listed in excel. I'm organizing them into price-to-earnings quintiles and analyzing their performance. It's complicated by the fact that every month there are varying # of stocks whose P/E is "#N/A" because their earnings are negative. I've set up formulas to automatically calculate the range of rows I will need to group them by excluding the negative earnings stocks. So my question is how do I get that row # together with a column letter in a formula that uses a range of cells so that every month I don't have to manually adjust it?

**JBeaucaire** · 08-14-2011, 11:48 AM

If you wanted a formula that would look at the number you placed in A1 and display the value of that row from column B, a couple of ways to do it would be:

=INDEX(B:B, A1)

=INDIRECT("B" & A1)

**portanalyst** · 08-14-2011, 12:19 PM

I just found what I was looking for. I searched for keywords "dynamic cell ranges" and came up with the OFFSET function which worked for me. Thank you anyway. i'll take a look at those 2 functions you mentioned regardless.

**JBeaucaire** · 08-14-2011, 01:47 PM

Ha! I actually debated adding the OFFSET() as a third option and decided against it, considering unnecessarily complex for this. Good job, if you got that working, you'll get the other two even quicker.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.