Getting Div/0 Error with time average on a range

M

michael.b

New Member

Joined

Apr 23, 2009

Messages

5

• Apr 23, 2009

• #2

I've also tried this:
=AVERAGE(IF(ISNUMBER(K174:K345),K174:K345))
using CTRL-SHIFT-ENTER

Same error

 

Upvote 0

K

Kelbo

Board Regular

Joined

Jun 16, 2007

Messages

224

• Apr 23, 2009

• #3

I assume that you must have at least one time in your range, therfore in your first formula the count must be true and the "" will be returned.

If your times are in the format 07:30, 01:24 etc then why not use =average(K174:K345) which in my tests has produced the corect answer. Null and zeros should have no effect.
Remembering that a zeo is a value and is used in the calculation. A null is ignored.

Kelbo

 

Upvote 0

M

michael.b

New Member

Joined

Apr 23, 2009

Messages

5

• Apr 23, 2009

• #4

Everything I was doing was good, it was just that the application from where the data was coming from was somehow inserting a blank character and I cannot figure out how to account for it.

Once I copied the data and put it into a fresh spreadsheet, and ran the same formula, everything worked fine....

Hmmmmm

 

Upvote 0

M

michael.b

New Member

Joined

Apr 23, 2009

Messages

5

• Apr 23, 2009

• #5

I spoke too soon. I'm still getting the error. It doesn't make any sense to me. The 1st 143 rows have some columns that are empty and the same formula works with this range, but the next range (144-286) always gives me an error.

 

Upvote 0

shemayisroel

Well-known Member

Joined

Sep 11, 2008

Messages

1,867

• Apr 23, 2009

• #6

Would it be possible to post some sample data...

Try using excel jeanie

http://www.excel-jeanie-html.de/index.php?f=1

 

Upvote 0

Devin

Board Regular

Joined

Jan 21, 2009

Messages

105

• Apr 23, 2009

• #7

Try this:

=SUMPRODUCT(1-ISERROR(G174:G345*1),G174:G345)/COUNT(G174:G345)

 

Last edited: Apr 23, 2009

Upvote 0