variable-length Min/Max functions in Excel? 1

[Speaker]

I have a worksheet with about 8000 rosws of data, and I want to find the Min and Max values for a few columns. The problem is I'd like to be able to change how far back it looks without changing the formula manually every time. For example, if the cells in column D get the Max of the last 25 cells in column C, I'd like to be able to change that to 50, 75, etc... maybe by having the cells in C reference cell C1 to see how far back to look.

Is this possible?
TIA

 

[SkipVought]

Speaker,

Take a look at the OFFSET function.

You can enter your variable number in C1.

Then if your list of numbers in in column A...
[tt]
=MIN(OFFSET(A1,COUNT(A:A)-C1,0,C1,1))
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Speaker]

Skip

Thanks for the reply. I couldn't get it to work with the count function, and using C1 in the offset was returning an error. But with a little tweaking I finally got the offset working. I ended up nesting an offset within the offset to reference the correct cell. (=MAX(OFFSET(A58650,-1*(OFFSET($C$4,0,0)),0,OFFSET($C$4,0,0),1))

This probably isn't the most elegant way to express it, but it's working. Thanks for pointing me in the right direction.

 

[SkipVought]

You must have been using it incorrectly. I pasted it into my sheet and it worked perfectly. I have

1. list of numbers in column A starting in row 1

2. a value in C1 that is the LAST number of rows you want the formula to evaluate: 25, 50 etc.

3. my formula is in C2

Caveat for the COUNT function: the range being counted must have a value in EACH cell!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244