Help with AVERAGE function
The problem is that <14 is text and not a number. Assuming your values
are in A1 to A5, though, this array formula* almost gives you what you
want:
=AVERAGE(IF(A1:A5="","",IF(LEFT(A1:A5)="<",VALUE(R IGHT(A1:A5,LEN(A1:A5)-1)),A1:A5)))
*As this is an array formula, once you have typed it in (or
subsequently edit it) you should use CTRL-SHIFT-ENTER instead of just
ENTER. If you do this correctly then Excel will wrap curly braces { }
around the formula - you should not type these yourself.
I say "almost" because it gives 29.6 for the numbers you supplied, but
I'm not sure how to put a "<" at the beginning of it.
You can define a named range to cover your data area, and then do Find
& Replace (CTRL-H) on the cell to change "A1:A5" to "your_name". The
range does not have to be completed filled.
Hope this helps.
Pete
|