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..

 

[babycody]

Is there a sheet actually named CompanyA because your newest array doesn't use the same sheet reference you started your post with. Thought you might be typing your arrays instead of selecting the range using the mouse pointer since a sheet name in a formula only requires apostrophes when the sheet name has a space somewhere in it. i.e. CompanyA with space 'Company A' . OK through butting in just thought it might be something simple.

 

[w8n4fri]

to answer both questions...
yes..there is a sheet called COMPANYA... I have subsituted the companys real name with just "COMPANY..

Excel is telling me that it is not a vaild formula..

 

[Brian from Maui]

to answer both questions...
yes..there is a sheet called COMPANYA... I have subsituted the companys real name with just "COMPANY..

Excel is telling me that it is not a vaild formula..

Just copy and paste both formulae I provided in the workbook that houses company and post back the results.

 

[NBVC]

Brian,

Your formulas don't have the ! after the sheet name in each reference. Perhaps that is why the error message?

Should be: =SUM(Company!I7:I33)/MAX(1,COUNT(Company!I7:I33-COUNTIF(Company!I7:I33,0)))

 

[w8n4fri]

I think we have got it....

=AVERAGE('CompanyA'!I7:I33)/MAX(1,COUNT(CompanyA!I7:I33)-COUNTIF('CompanyA'!I7:I33,0))

now the only problem is that it calculates the average of the whole column and I only want it calculate the average of the columms that have a number greater then 0.... Can this be done..
ex.
2
0
4
5
0
0

I want only Row 1, 4, and 5 to be averaged...so it would be 12:3

 

[Brian from Maui]

I don't know how you get your average answer, and why are you changing the formula I provided, it does calculate an average without 0's,

Book1
	A	B	C	D
1	2	3.666667	3.666667
2	0	3.666667
3	4
4	5
5	0
6	0
Sheet1

 

[w8n4fri]

Brian..
your formula works but it gives me a total not an average...I need the average of the column that have a number greater then 0.

 

[w8n4fri]

sorry I probably should have mentioned this before... I am trying to take an average of time...I dont know if this will affect the way the formula is written..

 

[Brian from Maui]

Brian..
your formula works but it gives me a total not an average...I need the average of the column that have a number greater then 0.

My formula averages the range, not summing. And it ignores 0's. Do you have negative numbers?

Edit,

Post some sample data of times in your range and expected answer.

 

[NBVC]

Perhaps

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