Help with large amounts of data and formulas
 

I need some help with an excel spreedsheet. I have a large amount of
data, 30 columns with over 1000 rows. I need to take an average of
each row and place it into a new column. I have used the average
formula from the drop down menu and then copied this to the rest of
the rows. My problem is that through out the data there are cells with
the number 0 in them. I want to remove these cells from the average
calculation. Is there anyway to include this in the average formula so
that I can just copy and paste the new formula without having to go
through and manually removing the cells that contain 0 from each row.
Because they are not always in the same place in each row. I would
appreciate anyones help as soon as possible.

Thanks!

Hi
change the formula to something like the following array formula
(entered with CTRL+SHIFT+ENTER):
=AVERAGE(IF(A1:X1<0,A1:X1))

and copy down

--
Regards
Frank Kabel
Frankfurt, Germany

"Aimoore" schrieb im Newsbeitrag
om...
I need some help with an excel spreedsheet. I have a large amount of
data, 30 columns with over 1000 rows. I need to take an average of
each row and place it into a new column. I have used the average
formula from the drop down menu and then copied this to the rest of
the rows. My problem is that through out the data there are cells
with
the number 0 in them. I want to remove these cells from the average
calculation. Is there anyway to include this in the average formula
so
that I can just copy and paste the new formula without having to go
through and manually removing the cells that contain 0 from each row.
Because they are not always in the same place in each row. I would
appreciate anyones help as soon as possible.

Thanks!

In A1 enter & copy down:

=SUM(B1:Z1)/(COUNT(B1:Z1)-COUNTIF(B1:Z1,0))

Adjust to suit.

Aimoore Wrote:
I need some help with an excel spreedsheet. I have a large amount of
data, 30 columns with over 1000 rows. I need to take an average of
each row and place it into a new column. I have used the average
formula from the drop down menu and then copied this to the rest of
the rows. My problem is that through out the data there are cells with
the number 0 in them. I want to remove these cells from the average
calculation. Is there anyway to include this in the average formula so
that I can just copy and paste the new formula without having to go
through and manually removing the cells that contain 0 from each row.
Because they are not always in the same place in each row. I would
appreciate anyones help as soon as possible.

Thanks!


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=318913