I have a golf handicap sheet where I track scores for the league. I keep scores in rows, by dates in the columns. When a player misses a week I skip that cell. In order to calculate the handicap we use the last ten scores, but remove the 2 highest and average the remaining eight. I'd like to to list the highest in one column and the second highest score in the next column. Then I can subtract those two out and average the remaining. I have found the following array formula to find the highest score using Max, but can't figure out how to find the second highest score. In the attached sheet, the second highest value of the last ten scores would appear in column DF.
Here is the formula I'm using to find the Max score out of the last ten scores in the range from BD5 through DA5, while skipping the blank cells.
{=MAX(IF(COLUMN(BD5:DA5)>=LARGE(IF(ISNUMBER(BD5:DA5), COLUMN(BD5:DA5)),MIN(10,COUNT(BD5:DA5))), IF(ISNUMBER(BD5:DA5),BD5:DA5)))}
Thanks.
Bookmarks