# Solved: Divide by zero error (Excel)



## EvileYe (Aug 30, 2003)

I have been trying to work this out for a couple of hours now, and it's time to ask for help from an excel pro.

I am trying to get an average figure for a column of data that is in this format 00:00:00 , I need the result to display in the same format. If I use the normal average function, I get a divide by zero error.

I exported this data from SAP, and just need to know the average time taken to do a task.

Please look at the attached pic and see if you can show me a formula for averaging column 'M'

Thanks,
EvileYe


----------



## Rockn (Jul 29, 2001)

Because that time format cannot be averaged. You need to use a number format instead of time to do the average. What is the custom format you are using in the cells?


----------



## EvileYe (Aug 30, 2003)

I have tried using Number Formats, General Formats,Custom Formats I have tried a lot of different formatting in the cells, and it still gives me the same error.

If it was convenient I could remove the leading zero's, but I don't want to do this to over 700 cells every time I use it.

Any idea's on specific formats or anything else ?


----------



## Yello (Sep 20, 2004)

Hi,
Maybe this wil help:
Select the colum M, the one with the traveltimes.
In celproperties (I have a dutch version of excel so excuse me if get the the labels wrong ) choose special or custom or whatever it is called. The bottom option.
Then choose mm:ss. This is a notation for just the minutes and the seconds.
The numbers that display the hours wil be gone.
You can then selct the numbers and calculate the average.
Hope it wil do the trick
Yello


----------



## EvileYe (Aug 30, 2003)

I have tried that also, but the 0's stay there, it doesn't seem to matter what way I format the cells ???
I'm Lost for an explanation. PM me if you guys want to look at it first hand and I'll send it to you.


----------



## Zack Barresse (Jul 25, 2004)

Umm, not sure, but maybe ...

=SUM(A2:INDEX(A:A,MATCH(9.99999999E+307,A:A)))/COUNT(A:A)

If not, which cells are you wanting to average? It's a little unclear to me.


----------



## EvileYe (Aug 30, 2003)

Column M is the one I am trying to average, I tried your formula but it gave me a result of 0.
Not sure if I altered it correctly though.


----------



## Zack Barresse (Jul 25, 2004)

Maybe you could post your spreadsheet..

With some assumptions ..

=SUM(M2:M716)/COUNTIF(M2:M716,"<>0")


----------



## maxflia10 (Feb 25, 2003)

You'll need to convert the time, which is stored as text to a number. In a cell enter 1, click copy, go to Edit/Paste Special and tick Multiply. then you should be able to use the AVERAGE function. To test if the cell is text, enter =ISNUMBER(A1). If it's text, the answer will be FALSE.


----------



## EvileYe (Aug 30, 2003)

I have tried in vain to get this to work using the above suggestions.

I have included it as a zip attachment to this post for anyone interested in trying to solve this problem.

Column M is the one I need to average.

Thanks for all your help so far.


----------



## maxflia10 (Feb 25, 2003)

EvileYe said:


> I have tried in vain to get this to work using the above suggestions.
> 
> I have included it as a zip attachment to this post for anyone interested in trying to solve this problem.
> 
> ...


The times are stored as text. In an empty cell enter 1. Click copy, highlight the range to average, go to Edit/Paste Special/tick Multiply, click OK. Then go to Format/Cells/Custom and format as hh:mm:ss click OK. Then use the Average function on the range.

I downloaded your zip file and performed the above.


----------



## EvileYe (Aug 30, 2003)

Thank You ! You are a genius, I was about to write back and say that it wasn't working, when I realised that I hadn't formatted the cell performing the calculation, I did this and it works perfectly 

Thanks for your time everyone in helping me out with this.

Problem Solved


----------



## buck52 (Mar 9, 2001)

Howdy EvileYe

you can now mark your own threads as solved...click on *thread tools* at the right of your first post

pass the word

buck


----------



## EvileYe (Aug 30, 2003)

Thanks buck52,
I never saw that before.

Will save me reporting them to you mods.


----------



## buck52 (Mar 9, 2001)

Your welcome

It's a new feature that was added a week ago or so thanks to Dreamboat and Techguy

buck


----------



## Zack Barresse (Jul 25, 2004)

buck52 said:


> Your welcome
> 
> It's a new feature that was added a week ago or so thanks to Dreamboat and Techguy
> 
> buck


Thanks to Dreamboat and Mark007!  The man is a genius, what can I say.. :up:


----------

