Calculating an AVERAGE formula

w8n4fri

New Member
Joined
Dec 5, 2005
Messages
14
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..
 

Some videos you may like

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

w8n4fri

New Member
Joined
Dec 5, 2005
Messages
14
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
Joined
Feb 16, 2002
Messages
8,458
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
Joined
Dec 5, 2005
Messages
14

ADVERTISEMENT

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

w8n4fri

New Member
Joined
Dec 5, 2005
Messages
14

ADVERTISEMENT

=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
Joined
Feb 16, 2002
Messages
8,458
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
Joined
Dec 5, 2005
Messages
14
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
Joined
Feb 16, 2002
Messages
8,458
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,070
Messages
5,570,018
Members
412,304
Latest member
citrus
Top