# Calculating an AVERAGE formula

#### w8n4fri

##### New Member
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..

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### Brian from Maui

##### MrExcel MVP
You need to enter using CRTL+SHIFT+ENTER,

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

#### w8n4fri

##### New Member
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

##### MrExcel MVP
Re: RE-

w8n4fri said:
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

##### New Member

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

#### Brian from Maui

##### MrExcel MVP
w8n4fri said:
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

##### New Member

=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

##### MrExcel MVP
w8n4fri said:
=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

##### New Member
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

##### MrExcel MVP
w8n4fri said:
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?

Replies
5
Views
112
Replies
1
Views
92
Replies
0
Views
54
Replies
7
Views
135
Replies
2
Views
130