CODEWITHSUNDEEP
exceltimeexcel-formula
Gordon_
Gordon_

Reputation: 65

Divide by Zero on Average Time Excel

I'm getting a #DIV/0 when calculating the average time from a range of cells.

A copy of the file can be found here;

http://www44.zippyshare.com/v/5ZlxD44N/file.html

I have literally no idea why this is happening, nor can I seem to find a way around this.

I have checked the formatting of the cells, tried to re save the file, pull the data on to a new page. Nothing seems to work.

Any help is much appreciated.

Upvotes: 0

Views: 6757

Answers (1)

BrakNicku
BrakNicku

Reputation: 5990

All the data in A1:C10 is stored as text (by default text is left alligned and cell's display doesn't change when you change formatting). To convert it to time values:

  • enter 1 in any empty cell
  • copy this cell
  • select A1:C10, choose Paste special->Multiply
  • format the range back to time format

Your sheet is set to manual calculation, so make sure to recalculate the sheet with F9 or switch it to automatic.

Alternatively you can convert text to time inside array formula (confirmed with Ctrl+Shift+Enter):

=AVERAGE(A1:A10*1)

Upvotes: 4

Related Questions