Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extend data range format and formulas | Excel Worksheet Functions | |||
how do I make a copy of a worksheet and retain formulas but not data | Setting up and Configuration of Excel | |||
extend data range formats and formulas | Setting up and Configuration of Excel | |||
Formulas in source data | Charts and Charting in Excel | |||
formulas for transferring data validation information in excel 200 | Excel Worksheet Functions |