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

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



wrote in message
ups.com...
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

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

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(RI GHT(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

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)="<",VALU E(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

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.e. ...LEFT*(N25,1)="<"),"< ",

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