Calculating an AVERAGE formula

[w8n4fri]

Hello,
I need some assistance in regards to calculating an AVerage of a workshet where the results have to go onto a different worksheet. Here is the formula that I have tried using but does not work...

=AVERAGE(IF('CompanyA'!I7:I33<>0,I7:I33,""))

what i am doing is trying to calculate the average of column I row 7 -33 but if there is a "0" in the columns do not calculate that row..
Any help would be great..

 

[w8n4fri]

Hi Brian...
no I dont have negative numbers..they are time in minutes
ex..
00:30
00:15
00:10
00:00
00:00

so the average would be 18:33 minutes...
Hope this helps...I so appreciate evryones help...I know we will figure this out...
thank you..

 

[NBVC]

w8n4fri,

Did you try my proposed formula: It work with the times you supplied. I get an average of 00:18 minutes. (just format the cell to Time 13:30.


Try
=SUM('Company'!I7:I33)/COUNTIF('Company'!I7:I33,">0")

 

[w8n4fri]

NVBC...
I beleive your formula works...I am going to test it still but from the couple of test so far it looks great...

Thank you to everyone that spent their time helping me......I am sure I will be back frequently as excel isnt a strong point for me..'

Thanks again..

 

[w8n4fri]

NBVC...
I beleive your formula works...I am going to test it still but from the couple of test so far it looks great...

Thank you to everyone that spent their time helping me......I am sure I will be back frequently as excel isnt a strong point for me..'

Thanks again..

 

[w8n4fri]

NBVC..
I ran in to one issue with your formula....
I am using the formula for multiple Companys that are on different sheets.
The problem is that some times there are no values that need to be entered for a specific client those leaving the worksheet blank.. Using you formula causes me to get a DIV/0 error if no time are entered for that sheet...Is there away to get around the error so that on my reporting sheet I dont get the DIV/0 error?

 

[w8n4fri]

Thank you to NBVC & Brian from Maui

A thanks to both of you..I was able to get the formula to work and produce the numbers that I needed.
Brian your formula worked..I had mistyped it in excel. Once I found the error it works perfect..