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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

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?

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,995
Messages
5,834,779
Members
430,321
Latest member
yemisimi11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back