Hi
I am trying to calculate the average of the lowest 6 values in 12
columns for a given row. I tried using this formula

=AVERAGE(SMALL(D1:O1,{1,2,3,4,5,6}))

This works fine when each column has a value.

My problem is that not all rows contain 12 values, some will contain
as few as 1 value. Since it would impossible to take the average of
the top half of an odd number of entries, I would like to use one less
entry to make it even (ie if there are 7 entries, only use the lowest
3).

Any help on this would be greatly appreciated.

Ezra

=IF(COUNT(D1:O1)=6,AVERAGE(SMALL(D1:O1,ROW(INDIRE CT("1:" &
6)))),AVERAGE(D1:O1))


" skrev:

Hi
I am trying to calculate the average of the lowest 6 values in 12
columns for a given row. I tried using this formula

=AVERAGE(SMALL(D1:O1,{1,2,3,4,5,6}))

This works fine when each column has a value.

My problem is that not all rows contain 12 values, some will contain
as few as 1 value. Since it would impossible to take the average of
the top half of an odd number of entries, I would like to use one less
entry to make it even (ie if there are 7 entries, only use the lowest
3).

Any help on this would be greatly appreciated.

Ezra