averaging less than values

J

JD

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
 
M

Max

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
 
M

Max

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
---
 
J

JD

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

Max

JD said:
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
---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top