Counting half or 50% of a fluctuating range of cells

[czipodrive]

So, I'm trying to make a sheet that will calculate handicaps.

I need no more than 20 cells across per person. But I need the formula to calculate the average of the lowest 50%.

10 numbers in a range, I need the formula to grab the lowest 5 values then divide them by the cell count.
Example: Grids.png


But as numbers are added in the row that may be lower I need it to keep calculating.

Example: 12 numbers will then take the lowest 6 values and find the average.
17 numbers, will take the lowest 8 values.

I think it's going to required 2 pages as a reference. In my mind I have a Find lowest n value in a range, but I need an IF formula to somehow choose how many cells to add together.
Like if there are 10 cells, add cells 1 to 5... I don't even know it that's possible..

any help would be great!

[AliGW]

There are instructions in the banner up top about how to attach your workbook.

[Pepe Le Mokko]

And while you are at it, are you still using XL2003? Perhaps update your profile as answers are tailored to the XL version

[AliGW]

Try this:

=SUM(SMALL(A1:J1,{1,2,3,4,5}))/5

[PaulM100]

Try: =SUM(SMALL(A1:H1,COLUMN(INDIRECT("1:"&INT(COUNTIF(A1:H1,"<>")/2)-1))))/COUNTIF(A1:H1,"<>")

[czipodrive]

Sorry, I didn't think to attach the actual file I'm working on. See attached. the first 20 rows have all the available cell count options that I'm testing. But any row at all might have 1 to 20 cells values. So I'm trying to make a formula that will adjust as the cells are filled in each week..

[JeteMc]

This proposal employs 11 helper columns which may be moved and/or hidden for aesthetic purposes.
The first helper column (X) gets a count of the number of values to be averaged using: =ROUND(COUNT(C3:V3)/2,0)
The next ten helper columns (Y:AH) get the lowest n values using: =IF(COLUMNS($A$1:A$1)>$X3,"",SMALL($C3:$V3,COLUMNS($A$1:A$1)))
The Handicap column is populated using: =AVERAGE(Y3:AH3)
Let us know if you have any questions.

[FlameRetired]

The attached interprets similarly to Ali's suggestion.

Please Login or Register  to view this content.

[FlameRetired]

Or perhaps this. It's returns are rounded up.

Please Login or Register  to view this content.