Help with AVERAGE function

[[email protected]]

Hi all,

Let's say I want to calculate the average of these numbers:
40, <14, 20, <14, 60

I want the average to show <29.6 but when I do the average function
=average(a1:a5) excel ignores the two <14 and gives the average of 40.

Can anyone help please? I'm faced with this problem almost daily and
forced to do manual calculations.

Thanks in advance,
Mike

[Peo Sjoblom]

Try

=AVERAGE(--(0&SUBSTITUTE(A1:A5,"<","")))

if there can be a > as well then use

=AVERAGE(--(0&SUBSTITUTE(SUBSTITUTE(A1:A5,"<",""),">","")))

it's important that you enter both of these formulas with ctrl + shift &
enter


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



<[email protected]> wrote in message
news:[email protected]...
> Hi all,
>
> Let's say I want to calculate the average of these numbers:
> 40, <14, 20, <14, 60
>
> I want the average to show <29.6 but when I do the average function
> =average(a1:a5) excel ignores the two <14 and gives the average of 40.
>
> Can anyone help please? I'm faced with this problem almost daily and
> forced to do manual calculations.
>
> Thanks in advance,
> Mike
>

[Pete_UK]

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(RIGHT(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

[Pete_UK]

If you only have five items (or so) to work with each time, then here's
a way to get your output showing "< 29.6" (NOTE, this is a text value,
so you couldn't do any further arithmetic with it):

=IF(OR(LEFT(A1,1)="<",LEFT(A2,1)="<",LEFT(A3,1)="<",LEFT(A4,1)="<",LEFT(A5,1)="<"),"<
","")&
AVERAGE(IF(A1:A5="","",IF(LEFT(A1:A5)="<",VALUE(RIGHT(A1:A5,LEN(A1:A5)-1)),A1:A5)))

This is all one array formula, so commit with CSE as already advised.

If any of the cells A1 to A5 start with "<", then you will get "< " in
your answer, together with the numeric average. Though you could extend
it for more values, it would become unwieldy.

Hope this helps further.

Pete

[[email protected]]

Pete, Thanks very much for your help. It works but now there are two
problems:
There is a box symbol after the < sign and the result has 12 decimal
places -- I want only 2.

Here is my data:
N17 14.58
N19 <14.58
N21 14.58
N23 87.46
N25 43.73


=IF(OR(LEFT(N17,1)="<",LEFT(N19,1)="<",LEFT(N21,1)="<",LEFT(N23,1)="<",LEFT(N25,1)="<"),"<
","")&
AVERAGE(IF(N17:N25="","",IF(LEFT(N17:N25)="<",VALUE(RIGHT(N17:N25,LEN(N17:N25)-1)),N17:N25)))


The results shows <[ ]34.9859....etc. Note the "box" after the < sign
has lines on four sides.

Thanks,
Mike

[Pete_UK]

If you copied the formula from the news groups, then you probably have
the character 10 (line feed) - in the formula just edit this and
replace it with a space (or leave it as "<"). This relates to the
symbol immediately after the OR( ... ) function,

i=2Ee. ...LEFT=AD(N25,1)=3D"<"),"< ",

I have Xl2000 and just tried your numbers - I get 3 decimal places. If
you want to restrict these to 2, you would have to use TEXT( xyz
,"0.00") after the & symbol, where xyz is the part of the formula from
AVERAGE to the end.

Don't forget CSE after you edit the formula.

Hope this helps.

Pete