|
averages
Hi, I have a column with a line of numbers and I have set an average at the
bottom using Autosum but it is dividing the zeros as well is there any way around this?. Thanks in advance, Barry. |
averages
Assuming the numbers are always positive...
Try one of these: =SUM(A1:A10)/COUNTIF(A1:A10,"0") This one is an array formula** : =AVERAGE(IF(A1:A100,A1:A10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "tommy" wrote in message ... Hi, I have a column with a line of numbers and I have set an average at the bottom using Autosum but it is dividing the zeros as well is there any way around this?. Thanks in advance, Barry. |
averages
Hi,
If you are going to use the last suggestion it would be safer to use SUM(A1:A10)/COUNTIF(A1:A10,"<0") Alternatively in 2007 you should consider this: =AVERAGEIF(A1:A10,"<0") In all versions you could also use =AVERAGE(IF(A1:A10<0,A1:A10,"")) This formula is array entered (press Shift+Ctrl+Enter to enter it rather than Enter) -- Thanks, Shane Devenshire "tommy" wrote: Hi, I have a column with a line of numbers and I have set an average at the bottom using Autosum but it is dividing the zeros as well is there any way around this?. Thanks in advance, Barry. |
averages
If you are going to use the last suggestion it would be safer to use
SUM(A1:A10)/COUNTIF(A1:A10,"<0") If the numbers are always positive how is that safer? It's actually less safe than using 0. The COUNTIF will include text, empty cells, and Booleans. -- Biff Microsoft Excel MVP "ShaneDevenshire" wrote in message ... Hi, If you are going to use the last suggestion it would be safer to use SUM(A1:A10)/COUNTIF(A1:A10,"<0") Alternatively in 2007 you should consider this: =AVERAGEIF(A1:A10,"<0") In all versions you could also use =AVERAGE(IF(A1:A10<0,A1:A10,"")) This formula is array entered (press Shift+Ctrl+Enter to enter it rather than Enter) -- Thanks, Shane Devenshire "tommy" wrote: Hi, I have a column with a line of numbers and I have set an average at the bottom using Autosum but it is dividing the zeros as well is there any way around this?. Thanks in advance, Barry. |
averages
What if I dont' want the error sign if all my #'s are 0. The formula worked
great for me too, but sometimes on a weekend when we don't run a line, there are 0's for each shift. When this happens, I get the error #DIC/0!... -- Thank you for your time! John "T. Valko" wrote: If you are going to use the last suggestion it would be safer to use SUM(A1:A10)/COUNTIF(A1:A10,"<0") If the numbers are always positive how is that safer? It's actually less safe than using 0. The COUNTIF will include text, empty cells, and Booleans. -- Biff Microsoft Excel MVP "ShaneDevenshire" wrote in message ... Hi, If you are going to use the last suggestion it would be safer to use SUM(A1:A10)/COUNTIF(A1:A10,"<0") Alternatively in 2007 you should consider this: =AVERAGEIF(A1:A10,"<0") In all versions you could also use =AVERAGE(IF(A1:A10<0,A1:A10,"")) This formula is array entered (press Shift+Ctrl+Enter to enter it rather than Enter) -- Thanks, Shane Devenshire "tommy" wrote: Hi, I have a column with a line of numbers and I have set an average at the bottom using Autosum but it is dividing the zeros as well is there any way around this?. Thanks in advance, Barry. |
averages
Replace formula with
IF(COUNTIF(A1:A10,"<0"), SUM(A1:A10)/COUNTIF(A1:A10,"<0"),"") or if you have Excel 2007 IFERROR(SUM(A1:A10)/COUNTIF(A1:A10,"<0"),"") best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Very Basic User" wrote in message ... What if I dont' want the error sign if all my #'s are 0. The formula worked great for me too, but sometimes on a weekend when we don't run a line, there are 0's for each shift. When this happens, I get the error #DIC/0!... -- Thank you for your time! John "T. Valko" wrote: If you are going to use the last suggestion it would be safer to use SUM(A1:A10)/COUNTIF(A1:A10,"<0") If the numbers are always positive how is that safer? It's actually less safe than using 0. The COUNTIF will include text, empty cells, and Booleans. -- Biff Microsoft Excel MVP "ShaneDevenshire" wrote in message ... Hi, If you are going to use the last suggestion it would be safer to use SUM(A1:A10)/COUNTIF(A1:A10,"<0") Alternatively in 2007 you should consider this: =AVERAGEIF(A1:A10,"<0") In all versions you could also use =AVERAGE(IF(A1:A10<0,A1:A10,"")) This formula is array entered (press Shift+Ctrl+Enter to enter it rather than Enter) -- Thanks, Shane Devenshire "tommy" wrote: Hi, I have a column with a line of numbers and I have set an average at the bottom using Autosum but it is dividing the zeros as well is there any way around this?. Thanks in advance, Barry. |
averages
Thank you, works great. May I ask two more while your here.
1. Instead of Not 0 or <0 what if I want it to not count anything greater unless greater than 2. We have a standar error of + 2, so I don't want to factor these into the averages either. 2. How can I get this same thing to work with cells that are not together. My current line that does not work is =AverageIf(A1,A3,A5,"2") I can't seem to get this to work. Thanks! John -- Thank you for your time! John "Bernard Liengme" wrote: Replace formula with IF(COUNTIF(A1:A10,"<0"), SUM(A1:A10)/COUNTIF(A1:A10,"<0"),"") or if you have Excel 2007 IFERROR(SUM(A1:A10)/COUNTIF(A1:A10,"<0"),"") best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Very Basic User" wrote in message ... What if I dont' want the error sign if all my #'s are 0. The formula worked great for me too, but sometimes on a weekend when we don't run a line, there are 0's for each shift. When this happens, I get the error #DIC/0!... -- Thank you for your time! John "T. Valko" wrote: If you are going to use the last suggestion it would be safer to use SUM(A1:A10)/COUNTIF(A1:A10,"<0") If the numbers are always positive how is that safer? It's actually less safe than using 0. The COUNTIF will include text, empty cells, and Booleans. -- Biff Microsoft Excel MVP "ShaneDevenshire" wrote in message ... Hi, If you are going to use the last suggestion it would be safer to use SUM(A1:A10)/COUNTIF(A1:A10,"<0") Alternatively in 2007 you should consider this: =AVERAGEIF(A1:A10,"<0") In all versions you could also use =AVERAGE(IF(A1:A10<0,A1:A10,"")) This formula is array entered (press Shift+Ctrl+Enter to enter it rather than Enter) -- Thanks, Shane Devenshire "tommy" wrote: Hi, I have a column with a line of numbers and I have set an average at the bottom using Autosum but it is dividing the zeros as well is there any way around this?. Thanks in advance, Barry. . |
averages
Hello T. Valko,
I also tried the Array formula with good results less all 0's. Actually I changed it slightly to the following due to a consistent +2 error in our input. Meaning that if we actually have a value of 0 it can sometimes read up to 2. So below works great until I have all cells between 0 and 2 then I get the error. Is there a way to say (If not then "") so if all cells are between 0 and 2 leave the formula cell blank. =AVERAGE(IF(A1:A102,A1:A10)) -- Thank you for your time! John "T. Valko" wrote: Assuming the numbers are always positive... Try one of these: =SUM(A1:A10)/COUNTIF(A1:A10,"0") This one is an array formula** : =AVERAGE(IF(A1:A100,A1:A10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "tommy" wrote in message ... Hi, I have a column with a line of numbers and I have set an average at the bottom using Autosum but it is dividing the zeros as well is there any way around this?. Thanks in advance, Barry. |
averages
This =AverageIf(A1,A3,A5,"2") will not work as the syntax requires a range
not a list of cells This =AVERAGEIF(A1:A5,"2") will work if cells A2 and A4 hold non-numeric data (or are empty) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Very Basic User" wrote in message ... Thank you, works great. May I ask two more while your here. 1. Instead of Not 0 or <0 what if I want it to not count anything greater unless greater than 2. We have a standar error of + 2, so I don't want to factor these into the averages either. 2. How can I get this same thing to work with cells that are not together. My current line that does not work is =AverageIf(A1,A3,A5,"2") I can't seem to get this to work. Thanks! John -- Thank you for your time! John "Bernard Liengme" wrote: Replace formula with IF(COUNTIF(A1:A10,"<0"), SUM(A1:A10)/COUNTIF(A1:A10,"<0"),"") or if you have Excel 2007 IFERROR(SUM(A1:A10)/COUNTIF(A1:A10,"<0"),"") best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Very Basic User" wrote in message ... What if I dont' want the error sign if all my #'s are 0. The formula worked great for me too, but sometimes on a weekend when we don't run a line, there are 0's for each shift. When this happens, I get the error #DIC/0!... -- Thank you for your time! John "T. Valko" wrote: If you are going to use the last suggestion it would be safer to use SUM(A1:A10)/COUNTIF(A1:A10,"<0") If the numbers are always positive how is that safer? It's actually less safe than using 0. The COUNTIF will include text, empty cells, and Booleans. -- Biff Microsoft Excel MVP "ShaneDevenshire" wrote in message ... Hi, If you are going to use the last suggestion it would be safer to use SUM(A1:A10)/COUNTIF(A1:A10,"<0") Alternatively in 2007 you should consider this: =AVERAGEIF(A1:A10,"<0") In all versions you could also use =AVERAGE(IF(A1:A10<0,A1:A10,"")) This formula is array entered (press Shift+Ctrl+Enter to enter it rather than Enter) -- Thanks, Shane Devenshire "tommy" wrote: Hi, I have a column with a line of numbers and I have set an average at the bottom using Autosum but it is dividing the zeros as well is there any way around this?. Thanks in advance, Barry. . |
| All times are GMT +1. The time now is 09:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com