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

  • 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!