Average of Smallest 6 values returning error

[uhlabomber]

Hello All, I can't seem to find a solution via the search so I thought I'd post my issue.
I've exhausted all my knowledge plus what I could gather on google.

Lets say I have a range of cells (C2:Q2) that are populated with a value via a formula. I'm then trying to take the average of the 6 smallest numbers in that range (if there are at least 3 values present). The formula works if there are 3 or less values present or 6 or more values present but it's struggling with 4 or 5 values present.
My formula looks like this: =IF(COUNT($C2:$Q2)>3,AVERAGE(SMALL($C2:$Q2,{1,2,3,4,5,6})),$R2)
If there are more than 3 values present then average the 6 lowest values, if 3 or less values than display the value in R2.
The issue I'm running into is excel (version 2010) views the blanks (not empty) as #NUM.
So my question is, how can I get it to calculate the average of the 6 smallest numbers ignoring the blanks or #NUM.
Note: The range of cells C2:Q2 are blank... populated with a "" in the formula that fills those cells.

Hope that makes sense and appreciate any assistance you can provide.

[Ochimus]

Not tried this, but If the cells are not actually blank, and you only want to calculate values greater that three, use something like this?

=if(COUNTIF(c2:q2,">0"))>3,average(small(COUNTIF(c2:q2,">0"),{1,2,3,4,5,6})),$R2)

Ochimus

[Tony Valko]

If you want to average the 6 smallest values then why don't you test and make sure there are 6 values to average:

=IF(COUNT($C2:$Q2)>3

=IF(COUNT($C2:$Q2)>=6

[TMS]

Surely the problem is self-explanatory. You cannot get the average of the 6 smallest numbers if you only have 4 or 5. 3 or less numbers is not a problem as you ask it return R2. 6 or more numbers is also not a problem because you have at least 6 numbers to choose from. It doesn't matter which cells are blank. If you only have 4 or 5 non-blank cells, the formula won't work.

[uhlabomber]

I certainly realize what the problem is, hence my question of how to generate a formula that gets around it.
I've solved the issue by inserting the following formula.
IF(COUNT($C2:$Q2)>3,
IF(COUNT($C2:$Q2)=4, AVERAGE(SMALL($C2:$Q2,{1,2,3,4})),
IF(COUNT($C2:$Q2)=5, AVERAGE(SMALL($C2:$Q2,{1,2,3,4,5})),
AVERAGE(SMALL($C2:$Q2,{1,2,3,4,5,6})))),
$R2)

If anyone can think of an alternative that's a little cleaner, shorter and or better let me know.

[Tony Valko]

This array formula** is a few keystrokes shorter:

=IF(COUNT($C2:$Q2)>3,AVERAGE(SMALL($C2:$Q2,ROW(INDIRECT("1:"&IF(COUNT($C2:$Q2)=4,4,IF(COUNT($C2:$Q2)=5,5,6)))))),$R2)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[shg]

Or

=IF(COUNT($C2:$Q2)>=3,AVERAGE(IFERROR(SMALL($C2:$Q2,{1,2,3,4,5,6}),FALSE)),$R2)

EDIT: That also requires Ctrl+Shift+Enter