Lowest 3 of last 7

    Hi, I have another formula I need help with. I need to average the lowest 3 scores of the last 7 played. The formula in S4 works when there is a score in all boxes. When some boxes are empty the range has to change to count the last 7 with scores in them. Is there a way to adjust the formula to account for this? Thanks.

    One way:

    =AVERAGE(SMALL(INDEX(B4:R4,LARGE(IF(B4:R4>0,COLUMN(B4:R4)-1),MIN(7,COUNT(B4:R4)))):R4,{1,2,3}))

    array-entered (Ctrl+Shift+Enter) into S4, then fill down.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Quote from rory

    One way:

    =AVERAGE(SMALL(INDEX(B4:R4,LARGE(IF(B4:R4>0,COLUMN(B4:R4)-1),MIN(7,COUNT(B4:R4)))):R4,{1,2,3}))

    array-entered (Ctrl+Shift+Enter) into S4, then fill down.

    Thanks Rory for your help. I am quite new to excel. I tried copying and pasting the formula but that came up with a value error. I am not sure what you mean by array-entered (Ctrl+Shift+Enter). Is it possible you could attach the file with formula entered. Thanks again.

    If you have the formula in the cell, select the cell, press f2 to enter edit mode, then press Ctrl+Shift+Enter together to array enter it. Then copy it down.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Your cells that look blank in those rows actually have spaces in - is there a reason for that?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Like 1
    Quote from rory

    Your cells that look blank in those rows actually have spaces in - is there a reason for that?

    Actually no, I didn't realize there were spaces in them. ( And don't know how you knew it) but when I clear the blank cells it works perfectly. Thanks again for this. Much appreciated.

Participate now!

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

Register Yourself Login