Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Countif function
I am trying to do an average of values in a range of cells across colunms,
where only values other than zero are counted in the divisor. For example, I have 4000, 0, 0 in my range and I want it to average the sum by 1, and not by 3 since the other two values are zero. If I then add a value in the string, as 4000, 2000, 0, my formula should then automatically average the sum by 2. My formula is as follows: =IF(ISERROR(+SUM(Z54:AD54)/(COUNTIF(Z54:AD54,"<0"))),0,(+SUM(Z54:AD54)/ (COUNTIF(Z54:AD54,"<0")))) I have done this type of formula before using only positive values as in "0" and it has worked. However, this time I have both positive and negative values and I want the formula to count all numbers other than zero in the denominator. The problem is that even though I have specified not equal to zero, i.e. "<0", is still counts zero as a number and divides by 3 as in the case of 4000, 0, 0. I have tried every variation that I can think of to solve this problem: "<0<", "0", "<"&"0", "<"&B4 (where the value in cell B4 is zero) and nothing works. Can someone help me out on this? |
#2
|
|||
|
|||
Try...
=IF(COUNT(Z54:AD54)-COUNTIF(Z54:AD54,0),SUM(Z54:AD54)/(COUNT(Z54:AD54)-CO UNTIF(Z54:AD54,0)),0) or =IF(COUNT(Z54:AD54)-COUNTIF(Z54:AD54,0),AVERAGE(IF(Z54:AD54,Z54:AD54)) ,0) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article <551c6af0b1c5e@uwe, "ponygirl via OfficeKB.com" <u14421@uwe wrote: I am trying to do an average of values in a range of cells across colunms, where only values other than zero are counted in the divisor. For example, I have 4000, 0, 0 in my range and I want it to average the sum by 1, and not by 3 since the other two values are zero. If I then add a value in the string, as 4000, 2000, 0, my formula should then automatically average the sum by 2. My formula is as follows: =IF(ISERROR(+SUM(Z54:AD54)/(COUNTIF(Z54:AD54,"<0"))),0,(+SUM(Z54:AD54)/ (COUNTIF(Z54:AD54,"<0")))) I have done this type of formula before using only positive values as in "0" and it has worked. However, this time I have both positive and negative values and I want the formula to count all numbers other than zero in the denominator. The problem is that even though I have specified not equal to zero, i.e. "<0", is still counts zero as a number and divides by 3 as in the case of 4000, 0, 0. I have tried every variation that I can think of to solve this problem: "<0<", "0", "<"&"0", "<"&B4 (where the value in cell B4 is zero) and nothing works. Can someone help me out on this? |
#3
|
|||
|
|||
Does this *array* formula work for you:
=AVERAGE(IF(Z54:AD54<0,Z54:AD54)) ? Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "ponygirl via OfficeKB.com" <u14421@uwe wrote in message news:551c6af0b1c5e@uwe... I am trying to do an average of values in a range of cells across colunms, where only values other than zero are counted in the divisor. For example, I have 4000, 0, 0 in my range and I want it to average the sum by 1, and not by 3 since the other two values are zero. If I then add a value in the string, as 4000, 2000, 0, my formula should then automatically average the sum by 2. My formula is as follows: =IF(ISERROR(+SUM(Z54:AD54)/(COUNTIF(Z54:AD54,"<0"))),0,(+SUM(Z54:AD54)/ (COUNTIF(Z54:AD54,"<0")))) I have done this type of formula before using only positive values as in "0" and it has worked. However, this time I have both positive and negative values and I want the formula to count all numbers other than zero in the denominator. The problem is that even though I have specified not equal to zero, i.e. "<0", is still counts zero as a number and divides by 3 as in the case of 4000, 0, 0. I have tried every variation that I can think of to solve this problem: "<0<", "0", "<"&"0", "<"&B4 (where the value in cell B4 is zero) and nothing works. Can someone help me out on this? |
#4
|
|||
|
|||
Thanks! This works, except that I need to incorporate the ISERROR function
into the formula so that I don't get the #DIV0! error. Can you offer any suggestions for that? PG RagDyer wrote: Does this *array* formula work for you: =AVERAGE(IF(Z54:AD54<0,Z54:AD54)) ? Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. I am trying to do an average of values in a range of cells across colunms, where only values other than zero are counted in the divisor. For example, I [quoted text clipped - 14 lines] this problem: "<0<", "0", "<"&"0", "<"&B4 (where the value in cell B4 is zero) and nothing works. Can someone help me out on this? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200509/1 |
#5
|
|||
|
|||
Works great! Thanks!
PG Domenic wrote: Try... =IF(COUNT(Z54:AD54)-COUNTIF(Z54:AD54,0),SUM(Z54:AD54)/(COUNT(Z54:AD54)-CO UNTIF(Z54:AD54,0)),0) or =IF(COUNT(Z54:AD54)-COUNTIF(Z54:AD54,0),AVERAGE(IF(Z54:AD54,Z54:AD54)) ,0) ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! I am trying to do an average of values in a range of cells across colunms, where only values other than zero are counted in the divisor. For example, I [quoted text clipped - 14 lines] this problem: "<0<", "0", "<"&"0", "<"&B4 (where the value in cell B4 is zero) and nothing works. Can someone help me out on this? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200509/1 |
#6
|
|||
|
|||
This will give you an empty ("") cell.
If you would prefer a zero, just replace the"" in the formula with a 0. =IF(ISERR(AVERAGE(Z54:AD54)),"",AVERAGE(IF(Z54:AD5 4<0,Z54:AD54))) Also array entered - CSE -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "ponygirl via OfficeKB.com" <u14421@uwe wrote in message news:5525e1b75936d@uwe... Thanks! This works, except that I need to incorporate the ISERROR function into the formula so that I don't get the #DIV0! error. Can you offer any suggestions for that? PG RagDyer wrote: Does this *array* formula work for you: =AVERAGE(IF(Z54:AD54<0,Z54:AD54)) ? Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. I am trying to do an average of values in a range of cells across colunms, where only values other than zero are counted in the divisor. For example, I [quoted text clipped - 14 lines] this problem: "<0<", "0", "<"&"0", "<"&B4 (where the value in cell B4 is zero) and nothing works. Can someone help me out on this? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200509/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CountIf function | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
COUNTIF and then SUM in Same Function | Excel Worksheet Functions | |||
A COUNTIF function Challenge | Excel Worksheet Functions | |||
hOW TO USE THE AND OPERATOR WITH THE COUNTIF FUNCTION | Excel Worksheet Functions |