Help with Function

E

ezranicholson

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
 
G

Guest

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


"(e-mail address removed)" skrev:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help With A Golf League 4
Average cells based on column header 6
Average with multiple conditions 4
Offset and Small functions with blank rows? 0
Trying to find additional ranges outside of min, max, and average 2
Return cell reference rather than value 4
Average value only for the filtered area.. 4
Average function 9

Top