software

Hi all,

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

I want the average to show lt;29.6 but when I do the average function
=average(a1:a5) excel ignores the two lt;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,
MikeTry

=AVERAGE(--(0amp;SUBSTITUTE(A1:A5,quot;lt;quot;,quot;quot;)))

if there can be a gt; as well then use

=AVERAGE(--(0amp;SUBSTITUTE(SUBSTITUTE(A1:A5,quot;lt;quot;,quot;quot;),quot;gt;quot;,quot;quot;)))

it's important that you enter both of these formulas with ctrl shift amp;
enter--

Regards,

Peo Sjoblom

nwexcelsolutions.com
gt; wrote in message ups.com...
gt; Hi all,
gt;
gt; Let's say I want to calculate the average of these numbers:
gt; 40, lt;14, 20, lt;14, 60
gt;
gt; I want the average to show lt;29.6 but when I do the average function
gt; =average(a1:a5) excel ignores the two lt;14 and gives the average of 40.
gt;
gt; Can anyone help please? I'm faced with this problem almost daily and
gt; forced to do manual calculations.
gt;
gt; Thanks in advance,
gt; Mike
gt;
The problem is that lt;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=quot;quot;,quot;quot;,IF(LEFT(A1:A5)=quot;lt;quot;,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 quot;almostquot; because it gives 29.6 for the numbers you supplied, but
I'm not sure how to put a quot;lt;quot; at the beginning of it.

You can define a named range to cover your data area, and then do Find
amp; Replace (CTRL-H) on the cell to change quot;A1:A5quot; to quot;your_namequot;. The
range does not have to be completed filled.

Hope this helps.

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

=IF(OR(LEFT(A1,1)=quot;lt;quot;,LEFT(A2,1)=quot;lt;quot;,LEFT(A3,1)=quot;lt; quot;,LEFT(A4,1)=quot;lt;quot;,LEFT(A5,1)=quot;lt;quot;),quot;lt;
quot;,quot;quot;)amp;
AVERAGE(IF(A1:A5=quot;quot;,quot;quot;,IF(LEFT(A1:A5)=quot;lt;quot;,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 quot;lt;quot;, then you will get quot;lt; quot; in
your answer, together with the numeric average. Though you could extend
it for more values, it would become unwieldy.

Hope this helps further.

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

Here is my data:
N17 14.58
N19 lt;14.58
N21 14.58
N23 87.46
N25 43.73=IF(OR(LEFT(N17,1)=quot;lt;quot;,LEFT(N19,1)=quot;lt;quot;,LEFT(N21,1) =quot;lt;quot;,LEFT(N23,1)=quot;lt;quot;,LEFT(N25,1)=quot;lt;quot;),quot;lt;
quot;,quot;quot;)amp;
AVERAGE(IF(N17:N25=quot;quot;,quot;quot;,IF(LEFT(N17:N25)=quot;lt;quot;,VALU E(RIGHT(N17:N25,LEN(N17:N25)-1)),N17:N25)))The results shows lt;[ ]34.9859....etc. Note the quot;boxquot; after the lt; sign
has lines on four sides.

Thanks,
MikeIf 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 quot;lt;quot;). This relates to the
symbol immediately after the OR( ... ) function,

i.e. ...LEFT*(N25,1)=quot;lt;quot;),quot;lt; quot;,

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
,quot;0.00quot;) after the amp; 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