Use cell value to define a range

**SueWithQuestion** · 08-31-2010, 04:11 PM

I want to be able to enter a value into say cell B2. Let's say that value is 30 to start.

I then want to count how many times "Not" appears in the 30 cells from E9 and going to E9+30.

If I change that value in cell B2 to 40, then I want to count how many times "Not" appears in the 40 cells from E9 and going to E9+40.

I tried making cell B2 be a Defined Name, but couldn't get that to work.

I'm thinking of something that is conceptually like: =COUNTIF(E9:E9+value of B2, "Not").

How can I do this?

**romperstomper** · 08-31-2010, 04:15 PM

=COUNTIF(OFFSET(E9,0,0,B2,1), "Not")

**SueWithQuestion** · 08-31-2010, 04:23 PM

Wonderful - thank you!

**NBVC** · 08-31-2010, 04:43 PM

We all know that DO will come here to tell you to avoid volatile OFFSET....

so he might offer: =COUNTIF(E9:INDEX(E:E,8+B2), "Not") or similar as a non-volatile alternative.

**SueWithQuestion** · 09-01-2010, 06:26 AM

Originally Posted by NBVC

We all know that DO will come here to tell you to avoid volatile OFFSET....

so he might offer: =COUNTIF(E9:INDEX(E:E,8+B2), "Not") or similar as a non-volatile alternative.

Thank you for your post.

What do you mean by "volatile OFFSET"?

**romperstomper** · 09-01-2010, 06:40 AM

Volatile formulas are formulas that recalculate whenever the workbook calculates, even if none of their direct input values have changed. OFFSET is one of those - it has to be because the range you are using is not actually passed directly to the formula.
In point of fact, the INDEX formula NBVC is semi-volatile anyway (it recalculates whenever the workbook is opened.
If you don't have many of these formulas, or a large complex workbook, there is no real need to avoid volatile formulas as the impact on performance will be negligible.