averaging less than values
 

How do I average a column of numbers where less than values are calculated as
that number?

2
<2
3
4
<2
5

In the average I want the<2 to = 2. So the answer should be 3

-- JD

averaging less than values
 

Assuming source data as posted in A2:A7

Paste this in say, B2,
then press CTRL+SHIFT+ENTER to array-enter the formula:
=AVERAGE(SUBSTITUTE(A2:A7,"<","")+0)

Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---
"JD" wrote:
How do I average a column of numbers where less than values are calculated as
that number?

2
<2
3
4
<2
5

In the average I want the<2 to = 2. So the answer should be 3

-- JD

averaging less than values
 

Paste this in say, B2,

should have read as:
Paste this in say, B2's formula bar

If you did the CTRL+SHIFT+ENTER confirmation (the "array-enter") properly,
you should see Excel wrap curly braces around the formula in the formula bar,
viz.:
{=AVERAGE(SUBSTITUTE(A2:A7,"<","")+0)}

If you don't see the curlies, just re-click inside the formula bar,
re-do the CTRL+SHIFT+ENTER confirmation
Then look again that the curlies are there

If the formula is not array-entered, it will not return the correct result
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---

averaging less than values
 

It works if there are values in every cell of the column (A2:A7). What if
some cells do not have values to average?
--
JD


"Max" wrote:

Paste this in say, B2,

should have read as:
Paste this in say, B2's formula bar

If you did the CTRL+SHIFT+ENTER confirmation (the "array-enter") properly,
you should see Excel wrap curly braces around the formula in the formula bar,
viz.:
{=AVERAGE(SUBSTITUTE(A2:A7,"<","")+0)}

If you don't see the curlies, just re-click inside the formula bar,
re-do the CTRL+SHIFT+ENTER confirmation
Then look again that the curlies are there

If the formula is not array-entered, it will not return the correct result
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---

averaging less than values
 

"JD" wrote:
It works if there are values in every cell of the column (A2:A7)

Ah, but wasn't that exactly what you showed in your posting?
Take a moment to press the "Yes" button in that response
(like the ones below)

What if some cells do not have values to average?

This should work, array-entered:
=AVERAGE(IF(A2:A7<"",SUBSTITUTE(A2:A7,"<","")+0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---