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

 

[Brian from Maui]

You need to enter using CRTL+SHIFT+ENTER,

=AVERAGE(IF(A1:A10,A1:A10))

 

[w8n4fri]

RE-

Does that work even when taking the average from lets say page 5 and entering that total into page one. I have tried but receive a #VALUE error.

 

[Brian from Maui]

Re: RE-

Does that work even when taking the average from lets say page 5 and entering that total into page one. I have tried but receive a #VALUE error.

Do you have a formula generated #VALUE error in your range to average?

 

[w8n4fri]

Yes...and it is frustrating becuase I can not figure out how to correct the error...

 

[Brian from Maui]

Yes...and it is frustrating becuase I can not figure out how to correct the error...

What is the formula that produces the #VALUE error?

 

[w8n4fri]

=AVERAGE(IF('CompanyA'!I7:I33<>0,I7:I33,""))
I am trying calculate the average of column I7 - I33 , but only calculate the rows that have a number in them... ex..
I7 - I10 have numbers in them the rest are blank...depending on the month more or less rows will be populated.

 

[Brian from Maui]

=AVERAGE(IF('CompanyA'!I7:I33<>0,I7:I33,""))
I am trying calculate the average of column I7 - I33 , but only calculate the rows that have a number in them... ex..
I7 - I10 have numbers in them the rest are blank...depending on the month more or less rows will be populated.

You need to enter the following formula with CTRL+SHIFT+ENTER, see the Help files for array formulas and how to enter array formulas.

=AVERAGE(IF(I7:I33,I7:I33))

This will produce a #DIV/0! is the range is blank

Entered with just Enter,

=SUM(I7:I33)/MAX(1,COUNT(I7:I33)-COUNTIF(I7:I33,0))

This will return 0 if the range is blank.

Don't forget to use the sheet name, as I'm lazy and omitted the sheet names from the formula.

 

[w8n4fri]

Brian...
thank you for the formula..but I still cant get it to work..this is the formula with the company name put in...did i do it correctly/
=sum('Company'!I7:I33)/MAX(1,count('Company'!(I7:I33)-CountIF('Company'!I7:I33,0))

 

[Brian from Maui]

Brian...
thank you for the formula..but I still cant get it to work..this is the formula with the company name put in...did i do it correctly/
=sum('Company'!I7:I33)/MAX(1,count('Company'!(I7:I33)-CountIF('Company'!I7:I33,0))

What do you get as a result of the above formula?

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

With this, entered with just Enter or

=AVERAGE(IF('Company'I7:I33,'Company'I7:I33))

entered by pressing CTRL+SHIFT+ENTER?