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.
Lowest 3 of last 7



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}))
arrayentered (Ctrl+Shift+Enter) into S4, then fill down.

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}))
arrayentered (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 arrayentered (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.

Ok, I did some googling and found out how the Ctrl + Shift + Enter worked and got the formula entered. It worked for most rows but not for some. I have attached the file and highlighted the rows with errors in them. Any suggestions?


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

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!