#REF!

  • Thread starter Thread starter Naomi
  • Start date Start date
N

Naomi

=AVERAGE(IF(COUNT(C9:AA9)<25,"",LARGE(C9:AA9,ROW(INDIRECT("1:"&COUNTIF(C9:AA9,">0")-1)))))

I got help on here getting this formula to work and it does fine. The only
problem is that unless there is data in at least two cells i get a #REF!
error. Once i place data in a minimum of two cells it works fine. Is there
a way to hide this message?

=AVERAGE(IF(AG9:AG28<>0,AG9:AG28)) I also get the #REF! error. When adding
the column from the above referenced. Is there a way around this or again
possibly hiding it?
 
You cannot say AVERAGE(IF.....? Surely, you should start of with
=IF(COUNT(C9:AA9)<25,"", and then do your average formula.
 
I can't duplicate your problem, I get a VALUE error with no items in the
range. I'm also not clear on what you are trying to do with the formula. It
looks to me as though you want the formula to dispay nothing onless there are
25 or more items, but that is not what the formula states?

If you are trying to do the average only if there are at least 25 items
change the formula to read:

=IF(COUNT(C9:AA9)<25,"",AVERAGE(LARGE(C9:AA9,ROW(INDIRECT("1:"&COUNTIF(C9:AA9,">0")-1)))))

and make sure you enter it as an array (press Shift+Ctrl+Enter to enter it.)
 
Back
Top