Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Calculate averages not including zero values
I have a large array of data which has intermittent zero values in it where
the data has not been acquired. I want to create a generic formula which I can copy down one column that gives me the median average of the data in one row, but does not include zero values. I have been able to do this for the mean average by using the formula =(SUM(A1:Z1))/(COUNTIF(A1:Z1, "<0")), (if there is an easier way, let me know) but I cannot see a way of doing something similar for the median. BTW, i have tried deselecting 'zero values' in the options tab but it merely hides the zeroes, and still uses them in the calculations. Any thoughts gladly appreciated. |
#2
|
|||
|
|||
Try empting cels containing zero. Use Find / Replace. I know that
AVERAGE(), for example, will ignore empty cells, but include zeros. -- Gary's Student "rmellison" wrote: I have a large array of data which has intermittent zero values in it where the data has not been acquired. I want to create a generic formula which I can copy down one column that gives me the median average of the data in one row, but does not include zero values. I have been able to do this for the mean average by using the formula =(SUM(A1:Z1))/(COUNTIF(A1:Z1, "<0")), (if there is an easier way, let me know) but I cannot see a way of doing something similar for the median. BTW, i have tried deselecting 'zero values' in the options tab but it merely hides the zeroes, and still uses them in the calculations. Any thoughts gladly appreciated. |
#3
|
|||
|
|||
Hi
Hope this help... =Average(If(A1:Z1=0,"",A1:Z1)) Confirmed the formula by pressing Ctrl + Shift + Enter "rmellison" wrote in message ... I have a large array of data which has intermittent zero values in it where the data has not been acquired. I want to create a generic formula which I can copy down one column that gives me the median average of the data in one row, but does not include zero values. I have been able to do this for the mean average by using the formula =(SUM(A1:Z1))/(COUNTIF(A1:Z1, "<0")), (if there is an easier way, let me know) but I cannot see a way of doing something similar for the median. BTW, i have tried deselecting 'zero values' in the options tab but it merely hides the zeroes, and still uses them in the calculations. Any thoughts gladly appreciated. |
#4
|
|||
|
|||
That also works, thanks. But I'm curious, what does Ctrl+Shift+Enter do to
the formula? Does that just have the effect of ignoring zeroes or does it serve some other purpose? It may be useful again.... "kk" wrote: Hi Hope this help... =Average(If(A1:Z1=0,"",A1:Z1)) Confirmed the formula by pressing Ctrl + Shift + Enter "rmellison" wrote in message ... I have a large array of data which has intermittent zero values in it where the data has not been acquired. I want to create a generic formula which I can copy down one column that gives me the median average of the data in one row, but does not include zero values. I have been able to do this for the mean average by using the formula =(SUM(A1:Z1))/(COUNTIF(A1:Z1, "<0")), (if there is an easier way, let me know) but I cannot see a way of doing something similar for the median. BTW, i have tried deselecting 'zero values' in the options tab but it merely hides the zeroes, and still uses them in the calculations. Any thoughts gladly appreciated. |
#5
|
|||
|
|||
Hi,
This is an array formula. From Excel Help: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER. Take a look at he http://www.cpearson.com/excel/array.htm http://www.emailoffice.com/excel/arrays-bobumlas.html kk "rmellison" wrote in message ... That also works, thanks. But I'm curious, what does Ctrl+Shift+Enter do to the formula? Does that just have the effect of ignoring zeroes or does it serve some other purpose? It may be useful again.... "kk" wrote: Hi Hope this help... =Average(If(A1:Z1=0,"",A1:Z1)) Confirmed the formula by pressing Ctrl + Shift + Enter "rmellison" wrote in message ... I have a large array of data which has intermittent zero values in it where the data has not been acquired. I want to create a generic formula which I can copy down one column that gives me the median average of the data in one row, but does not include zero values. I have been able to do this for the mean average by using the formula =(SUM(A1:Z1))/(COUNTIF(A1:Z1, "<0")), (if there is an easier way, let me know) but I cannot see a way of doing something similar for the median. BTW, i have tried deselecting 'zero values' in the options tab but it merely hides the zeroes, and still uses them in the calculations. Any thoughts gladly appreciated. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate averages not including zero values
I'm trying to do the same thing.
Calculate averages while skipping certain rows and all zero values... (Example) MILEAGE TOTAL A1 100 A2 200 A3 300 A4 TOTAL: 600 A5 100 A6 0 A7 200 A8 TOTAL: 300 etc.. (down) All of these cells are linked to another workbook. For some reason it inserts a zero by default. The goal is to calculate the averge miles traveled. I'm trying to have the function ignore the zeros. I've tried both of the suggested formulas, but it looks like I have too many arguments? Can't do, A1:A3, A5:A7, etc...? Any help would be much appreciated... Thx! "kk" wrote: Hi, This is an array formula. From Excel Help: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER. Take a look at he http://www.cpearson.com/excel/array.htm http://www.emailoffice.com/excel/arrays-bobumlas.html kk "rmellison" wrote in message ... That also works, thanks. But I'm curious, what does Ctrl+Shift+Enter do to the formula? Does that just have the effect of ignoring zeroes or does it serve some other purpose? It may be useful again.... "kk" wrote: Hi Hope this help... =Average(If(A1:Z1=0,"",A1:Z1)) Confirmed the formula by pressing Ctrl + Shift + Enter "rmellison" wrote in message ... I have a large array of data which has intermittent zero values in it where the data has not been acquired. I want to create a generic formula which I can copy down one column that gives me the median average of the data in one row, but does not include zero values. I have been able to do this for the mean average by using the formula =(SUM(A1:Z1))/(COUNTIF(A1:Z1, "<0")), (if there is an easier way, let me know) but I cannot see a way of doing something similar for the median. BTW, i have tried deselecting 'zero values' in the options tab but it merely hides the zeroes, and still uses them in the calculations. Any thoughts gladly appreciated. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate averages not including zero values
Depending what and where totals appear =SUM(IF(a1:a10<0,IF(ISNUMBER(a1:a10),a1:a10,""))) Entered as an array shift ctrl enter Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=400698 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Zero values in a log chart | Charts and Charting in Excel | |||
How To Use Cells Without Values in a Formula | Excel Worksheet Functions | |||
Averages: Exclude Rows with 0 Values | Excel Worksheet Functions | |||
Second serie doesn't use X-as values | Charts and Charting in Excel | |||
How to calculate the data in excel 2002 including only the last 9. | Excel Worksheet Functions |