Average numbers and ignore text

JaretAleb · Jul 16, 2019

Hi there,

I'm trying to average column that only contain number (and ignoring zero) but I still dont get the right formula.

I tried with (=average(if(ISANUMBER(A1:A10000),A1:A10000))) but it doesn't work.

Here is the table.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]12-Dec-18
[/TD]
[TD]X
[/TD]
[TD]A
[/TD]
[TD]11-Mar-19
[/TD]
[TD]ABC
[/TD]
[TD]89
[/TD]
[TD]0
[/TD]
[TD]B
[/TD]
[TD]5-Apr-19
[/TD]
[TD]X
[/TD]
[TD]B
[/TD]
[TD]14-May-19
[/TD]
[TD]B
[/TD]
[TD]39
[/TD]
[/TR]
</tbody>[/TABLE]

I hope there is a way!!
PLEASE HELP!!

Regards,
JaretAleb

RoryA · Jul 16, 2019

AVERAGE ignores text anyway, but if you need to ignore 0 values too, you need averageif:

=AVERAGEIF(A1:A10000,"<>0",A1:A10000)

but if you actually have dates in the column too, they will be included since dates are stored as numbers.

JaretAleb · Jul 16, 2019

RoryA said:
AVERAGE ignores text anyway, but if you need to ignore 0 values too, you need averageif:

=AVERAGEIF(A1:A10000,"<>0",A1:A10000)

but if you actually have dates in the column too, they will be included since dates are stored as numbers.

yes I forgot to mention about the date as well. It’s always included the dates everytime I use the formula above. I still get error everytime I used “ISNUMBER”

kweaver · Jul 16, 2019

To the best of my knowledge, there's no function to determine which cells in a range are (or aren't) dates.
If you KNOW that all of your legit numbers are smaller than the date number for January 1, 2019, you could use this:

Code:

=AVERAGEIFS(A1:A10000,A1:A10000,"<>0",A1:A10000,"<43466")

kweaver · Jul 16, 2019

If you don't mind a helper column and a UDF:

Code:

Function idate(rng As Range) As Boolean
idate = IsDate(rng)
End Function

(using it in column D in my example)

Then,

Code:

=AVERAGEIFS(A1:A10000,A1:A10000,"<>0",D1:D10000,"<>TRUE")

JaretAleb · Jul 16, 2019

kweaver said:
To the best of my knowledge, there's no function to determine which cells in a range are (or aren't) dates.
If you KNOW that all of your legit numbers are smaller than the date number for January 1, 2019, you could use this:

Code:

=AVERAGEIFS(A1:A10000,A1:A10000,"<>0",A1:A10000,"<43466")

OMG it works!!! Thankyou so much!

kweaver · Jul 16, 2019

You're welcome. However, I caution you about this because you're testing it with really a "fake" number using Jan. 1, 2019 as the number.
A much more reliable way would be to use the UDF and a helper column (which you could hide if you want).

Excel 2010

A

B

C

D

E

1

90

46

FALSE

46

2

20

FALSE

3

30

FALSE

4

0

FALSE

5

40

FALSE

6

0

FALSE

7

7/16/2019

TRUE

8

50

FALSE

9

7/1/2019

TRUE

10

7/16/2019

TRUE

Sheet2

Cell Formulas
Range	Formula
A7	=TODAY()
C1	=AVERAGEIFS(A1:A10000,A1:A10000,"<>0",A1:A10000,"<43466")
D1	=idate(A1)
E1	=AVERAGEIFS(A1:A10,A1:A10,"<>0",D1:D10,"<>TRUE")

Average numbers and ignore text

JaretAleb

New Member

Excel Facts

RoryA

MrExcel MVP, Moderator

JaretAleb

New Member

kweaver

Well-known Member

kweaver

Well-known Member

JaretAleb

New Member

kweaver

Well-known Member

Similar threads

Forum statistics

Share this page

Average numbers and ignore text

JaretAleb

New Member

Excel Facts

RoryA

MrExcel MVP, Moderator

JaretAleb

New Member

kweaver

Well-known Member

kweaver

Well-known Member

JaretAleb

New Member

kweaver

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock