How do I automatically average the lowest 3 of the last 6 numbers in a row of 22 that adds one number per week? Obviously I need at least 7 numbers to begin using this formula. Also, some cells in some rows may be blank.
-
An example might help.– Daniel Beck ♦May 11, 2013 at 15:51
-
It's for LibreOffice Calc. I need to calculate our league golfers' handicaps by averaging the lowest three of their last 6 scores. Not everyone (subs, for example) will have 6 scores and some members miss each week so there will be calculations with missing scores in the list of 22; that's why I need to have the lowest 3 of the last 6. In some cases I'll have to go back 8 or 9 weeks just to get 6 scores. The majority of members will have 5 scores next week so I have a bit of time to figure it out.– BartMay 11, 2013 at 16:35
3 Answers
Use a helper column with this formula
=IF(A2,ROW(),0)
Copy down.
Then use this array formula, confirmed with Ctrl - Shift - Enter
=AVERAGE(SMALL(IF((A2:A15<>0)*(B2:B15=LARGE(B2:B15,{1,2,3,4,5,6})),A2:A15),{1,2,3}))
The Large function pins down the six largest numbers in column B. The If function returns the respective numbers in column A. The Small function returns the three smallest from that set. And Average does the average of these.
That's how it works in Excel. Not sure about LibreOffice Calc.
Edit: here is a variant that works without a helper column:
=AVERAGE(SMALL(IF((A2:A15<>0)*(IF(ISNUMBER(A2:A15),ROW(A2:A15))=LARGE(IF(ISNUMBER(A2:A15),ROW(A2:A15)),{1,2,3,4,5,6})),A2:A15),{1,2,3}))
Also confirmed with Ctrl - Shift - Enter
-
Can a spreadsheet be "forced" to count entries in a row backwards; i.e., if Count(c33:c5)=6 and then go on to average 3 smallest?– BartMay 12, 2013 at 10:27
-
No. Although you can build a range address like A5:A1, Excel will turn it into A1:A5 before processing it. Look at this formula in the Evaluate Formula tool: =SUM(INDEX(A:A,5):INDEX(A:A,1))– teylynMay 12, 2013 at 21:47
-
I simplified your second version a little:
=AVERAGE(SMALL(IF((IF(ISNUMBER(A2:A15),ROW(A2:A15))=LARGE(IF(ISNUMBER(A2:A15),ROW(A2:A15)),{1,2,3,4,5,6})),A2:A15),{1,2,3}))
. Jun 6, 2013 at 1:05
One way to do this would be to use the "small" function. I'm not sure if you can return the lowest three values altogether, but you could use three different cells with varying "ranks" of "small" and then take the average of those together:
[Let's say you have a column of numerical data B3:B80...]
[Sample data from B column: 19, 23, 23, 24, 24, 27, 27, 27, 28, 29, 29, 29...]
[cell on left, cell contents in quotes, output on right of =>]
C3 '=SMALL(B3:B80;3)' => 23 (third lowest number)
C4 '=SMALL(B3:B80;2)' => 23 (second lowest number)
C5 '=SMALL(B3:B80;1)' => 19 (first lowest number; you could also used "MIN")
C6 '=AVERAGE(C3:C5)' => 21.67
-
OK, that's what I do now. Lowest 3 of all scores. I need lowest 3 of last 6 scores (of 7-22: a new number is added weekly and a new average calculated weekly also) in the order entered and with possible blanks (so I can't just select the last 6 columns for all my rows of scores, for example)– BartMay 11, 2013 at 20:30
-
It's conceptually simple: find the last six numbers in a row of more than 6 numbers and average the 3 smallest of those 6.– BartMay 12, 2013 at 2:01
-
Hmm. If you're changing the data by inserting rows and you want the formula to know only to take six rows, I'm not sure how to produce that result. It might taken some programming, but this is the point where I would usually give up on using a spreadsheet.– erewokMay 12, 2013 at 2:22
-
Actually, you could probably use another column that has dates, date data was taken or date recorded, then you could have a formula that looks for the most recent six dates and output those in a new range down below. With those six dates you could then find the equivalent values you're looking for, then take the three smallest average. I believe that would work.– erewokMay 12, 2013 at 2:24
I realize that this is not a practical answer, but maybe somebody can clean it up (maybe with a clever array formula?):
=IF(COUNT(B75:B80)=6, AVERAGE(SMALL(B75:B80,1),SMALL(B75:B80,2),SMALL(B75:B80,3)), IF(COUNT(B74:B80)=6, AVERAGE(SMALL(B74:B80,1),SMALL(B74:B80,2),SMALL(B74:B80,3)), IF(COUNT(B73:B80)=6, AVERAGE(SMALL(B73:B80,1),SMALL(B73:B80,2),SMALL(B73:B80,3)), IF(COUNT(B72:B80)=6, AVERAGE(SMALL(B72:B80,1),SMALL(B72:B80,2),SMALL(B72:B80,3)), ...))))))))