 # Formula to find largest value among nth-10 rows?

• Hi!

I have a excel sheet where data is continuously being added to the top of a column, after shifting the remaining rows/ cells down.
I want to find the largest value among the nth - 10 rows and result at the bottom of that column.

For example:
At 12:13 pm, the Range A would contain data in A2 to A31, but I want to find the maximum value among rows A2:A20 ignoring A21 to A31;
At 12:14 pm, the Range A would contain data in A2 to A32, but I want to find the maximum value among rows A2:A21 ignoring A22 to A32; and so on....

Please help with a formula that takes care of this dynamically expanding range.

PS:
1. Would appreciate a non-macro soluiton since they are volatile and remove the undo option;
2. Would appriciate a non-array formula since they can take a long time to process.

Would be OK, if I have to use multiple calculations in multiple cells to arrive at the result.

Sample file attached

Thanks

## Files

• Re: Formula to find largest value among nth-10 rows?

Put this formula in C34
=MAX(OFFSET(\$C2,0,0,COUNTA(\$C2:\$C33)-10,1))

Use the same formula for Columns D and E just change the column references in the formula

Dan

[SIZE=1]It's like asking a mechanic to fix your car, without actually taking your car to him. Post your code/file and you'll get much quicker and more accurate solutions to your problem.[/SIZE]

• Re: Formula to find largest value among nth-10 rows?

Quote from danerida;681490

Put this formula in C34
=MAX(OFFSET(\$C2,0,0,COUNTA(\$C2:\$C33)-10,1))

Above formula works if the cells/ rows are actually blank.
My cells contain formula the result of which is a blank cell, in which case the above formula fails.

Used Countif instead of CountA and that works.

Code
``=MAX(OFFSET(\$C2,0,0,COUNTIF(\$C2:\$C33,">0")-10,1))``
• Re: Formula to find largest value among nth-10 rows?

Quote from naira;681492

Above formula works if the cells/ rows are actually blank.
My cells contain formula the result of which is a blank cell, in which case the above formula fails.

That wasn't the case in your sample workbook. Otherwise COUNTIF would have been my response. Glad it works for you.

[SIZE=1]It's like asking a mechanic to fix your car, without actually taking your car to him. Post your code/file and you'll get much quicker and more accurate solutions to your problem.[/SIZE]

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!