| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
aauugghhh...#div/o problems & various average formula problems
Help...I'm confusing myself...
Trying to determine an average per month, based on 12 months of numerical data€¦but some of the cells have zeros. Now€¦I need to divide the sum of those 12 cells by 12 IF they ALL have numbers greater than 0. If any of the 12 cells has a 0€¦then I need to divide the sum of the 12 cells by the number of cells that actually have a whole number. I.E. 13 13 2 2 3 0 4 4 5 = 27/5 = 5.4 5 = 24/4 =6 (This is the answer I need) Been using the array formula: =AVERAGE(IF(I6:I17<0, I6:I17,""))€¦which works fine€¦. But some of the columns have ALL zeros€¦.and of course I get the #DIV/0 error, so I tried the =SUM(I6:I17)/COUNTIF(I6:I17,"0") on all the columns, didnt work (divides the sum of the cells by ALLS including the zero cells)€¦so I need to use ONE array formula, plus something??? to give me a zero total on the zero column. Because it affects the formula I use later to calculate Quarterly averages€¦.(maybe I need to do a format change???) Yep...I am confused€¦ Whew€¦.anyone can help me with this€¦Id be truly grateful€¦. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
aauugghhh...#div/o problems & various average formula problems
acbel40,
Try this solution (it worked for me): In the cell that is for the average, put this formula: "=(SUMIF(A1:A5,"0")/COUNTIF(A1:A5,"0"))" A1 = 13 A2 = 2 A3 = 3 A4 = 5 A5 = 5 A6 will be 5.4 (format cell to 1 decimal place). Both halves of the formula only adds up the numbers larger than 0. hth "acbel40" wrote: Help...I'm confusing myself... Trying to determine an average per month, based on 12 months of numerical data€¦but some of the cells have zeros. Now€¦I need to divide the sum of those 12 cells by 12 IF they ALL have numbers greater than 0. If any of the 12 cells has a 0€¦then I need to divide the sum of the 12 cells by the number of cells that actually have a whole number. I.E. 13 13 2 2 3 0 4 4 5 = 27/5 = 5.4 5 = 24/4 =6 (This is the answer I need) Been using the array formula: =AVERAGE(IF(I6:I17<0, I6:I17,""))€¦which works fine€¦. But some of the columns have ALL zeros€¦.and of course I get the #DIV/0 error, so I tried the =SUM(I6:I17)/COUNTIF(I6:I17,"0") on all the columns, didnt work (divides the sum of the cells by ALLS including the zero cells)€¦so I need to use ONE array formula, plus something??? to give me a zero total on the zero column. Because it affects the formula I use later to calculate Quarterly averages€¦.(maybe I need to do a format change???) Yep...I am confused€¦ Whew€¦.anyone can help me with this€¦Id be truly grateful€¦. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
aauugghhh...#div/o problems & various average formula problems
Thank you....but I'm averaging the columns...will this formula work
(substituting SUMIF with AVERAGEIF? "FloMM2" wrote: acbel40, Try this solution (it worked for me): In the cell that is for the average, put this formula: "=(SUMIF(A1:A5,"0")/COUNTIF(A1:A5,"0"))" A1 = 13 A2 = 2 A3 = 3 A4 = 5 A5 = 5 A6 will be 5.4 (format cell to 1 decimal place). Both halves of the formula only adds up the numbers larger than 0. hth "acbel40" wrote: Help...I'm confusing myself... Trying to determine an average per month, based on 12 months of numerical data€¦but some of the cells have zeros. Now€¦I need to divide the sum of those 12 cells by 12 IF they ALL have numbers greater than 0. If any of the 12 cells has a 0€¦then I need to divide the sum of the 12 cells by the number of cells that actually have a whole number. I.E. 13 13 2 2 3 0 4 4 5 = 27/5 = 5.4 5 = 24/4 =6 (This is the answer I need) Been using the array formula: =AVERAGE(IF(I6:I17<0, I6:I17,""))€¦which works fine€¦. But some of the columns have ALL zeros€¦.and of course I get the #DIV/0 error, so I tried the =SUM(I6:I17)/COUNTIF(I6:I17,"0") on all the columns, didnt work (divides the sum of the cells by ALLS including the zero cells)€¦so I need to use ONE array formula, plus something??? to give me a zero total on the zero column. Because it affects the formula I use later to calculate Quarterly averages€¦.(maybe I need to do a format change???) Yep...I am confused€¦ Whew€¦.anyone can help me with this€¦Id be truly grateful€¦. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
aauugghhh...#div/o problems & various average formula problems
That was a dumb question...I tried it...but in the column with all zeros...it
gives me the #div/0 error now (works fine on columns with whole numbers) "FloMM2" wrote: acbel40, Try this solution (it worked for me): In the cell that is for the average, put this formula: "=(SUMIF(A1:A5,"0")/COUNTIF(A1:A5,"0"))" A1 = 13 A2 = 2 A3 = 3 A4 = 5 A5 = 5 A6 will be 5.4 (format cell to 1 decimal place). Both halves of the formula only adds up the numbers larger than 0. hth "acbel40" wrote: Help...I'm confusing myself... Trying to determine an average per month, based on 12 months of numerical data€¦but some of the cells have zeros. Now€¦I need to divide the sum of those 12 cells by 12 IF they ALL have numbers greater than 0. If any of the 12 cells has a 0€¦then I need to divide the sum of the 12 cells by the number of cells that actually have a whole number. I.E. 13 13 2 2 3 0 4 4 5 = 27/5 = 5.4 5 = 24/4 =6 (This is the answer I need) Been using the array formula: =AVERAGE(IF(I6:I17<0, I6:I17,""))€¦which works fine€¦. But some of the columns have ALL zeros€¦.and of course I get the #DIV/0 error, so I tried the =SUM(I6:I17)/COUNTIF(I6:I17,"0") on all the columns, didnt work (divides the sum of the cells by ALLS including the zero cells)€¦so I need to use ONE array formula, plus something??? to give me a zero total on the zero column. Because it affects the formula I use later to calculate Quarterly averages€¦.(maybe I need to do a format change???) Yep...I am confused€¦ Whew€¦.anyone can help me with this€¦Id be truly grateful€¦. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
aauugghhh...#div/o problems & various average formula problems
Check first...
=if(countif(a1:a5,""&0)=0,"No numbers 0",sumif(...)/countif(...)) acbel40 wrote: That was a dumb question...I tried it...but in the column with all zeros...it gives me the #div/0 error now (works fine on columns with whole numbers) "FloMM2" wrote: acbel40, Try this solution (it worked for me): In the cell that is for the average, put this formula: "=(SUMIF(A1:A5,"0")/COUNTIF(A1:A5,"0"))" A1 = 13 A2 = 2 A3 = 3 A4 = 5 A5 = 5 A6 will be 5.4 (format cell to 1 decimal place). Both halves of the formula only adds up the numbers larger than 0. hth "acbel40" wrote: Help...I'm confusing myself... Trying to determine an average per month, based on 12 months of numerical data€¦but some of the cells have zeros. Now€¦I need to divide the sum of those 12 cells by 12 IF they ALL have numbers greater than 0. If any of the 12 cells has a 0€¦then I need to divide the sum of the 12 cells by the number of cells that actually have a whole number. I.E. 13 13 2 2 3 0 4 4 5 = 27/5 = 5.4 5 = 24/4 =6 (This is the answer I need) Been using the array formula: =AVERAGE(IF(I6:I17<0, I6:I17,""))€¦which works fine€¦. But some of the columns have ALL zeros€¦.and of course I get the #DIV/0 error, so I tried the =SUM(I6:I17)/COUNTIF(I6:I17,"0") on all the columns, didnt work (divides the sum of the cells by ALLS including the zero cells)€¦so I need to use ONE array formula, plus something??? to give me a zero total on the zero column. Because it affects the formula I use later to calculate Quarterly averages€¦.(maybe I need to do a format change???) Yep...I am confused€¦ Whew€¦.anyone can help me with this€¦Id be truly grateful€¦. -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
aauugghhh...#div/o problems & various average formula problems
"acbel40" wrote:
Been using the array formula: =AVERAGE(IF(I6:I17<0, I6:I17,""))€¦ which works fine€¦. But some of the columns have ALL zeros€¦.and of course I get the #DIV/0 error If you know that the size of the range is 12, and all cells contain numeric values, then: =if(countif(I6:I17,0)=12, 0, average(if(I6:I17<0, I6:I17))) More generally: =if(countif(I6:I17,0)=count(I6:I17), 0, average(if(I6:I17<0, I6:I17))) Both should be entered as an array formula. That is, commit with ctrl+shift+Enter instead of Enter. ----- original message ----- "acbel40" wrote in message ... Help...I'm confusing myself... Trying to determine an average per month, based on 12 months of numerical data€¦but some of the cells have zeros. Now€¦I need to divide the sum of those 12 cells by 12 IF they ALL have numbers greater than 0. If any of the 12 cells has a 0€¦then I need to divide the sum of the 12 cells by the number of cells that actually have a whole number. I.E. 13 13 2 2 3 0 4 4 5 = 27/5 = 5.4 5 = 24/4 =6 (This is the answer I need) Been using the array formula: =AVERAGE(IF(I6:I17<0, I6:I17,""))€¦which works fine€¦. But some of the columns have ALL zeros€¦.and of course I get the #DIV/0 error, so I tried the =SUM(I6:I17)/COUNTIF(I6:I17,"0") on all the columns, didnt work (divides the sum of the cells by ALLS including the zero cells)€¦so I need to use ONE array formula, plus something??? to give me a zero total on the zero column. Because it affects the formula I use later to calculate Quarterly averages€¦.(maybe I need to do a format change???) Yep...I am confused€¦ Whew€¦.anyone can help me with this€¦Id be truly grateful€¦. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula problems help please. | Excel Discussion (Misc queries) | |||
| Still trying to average 4 worksheets and having formula problems | Excel Discussion (Misc queries) | |||
| Problems with offset/average formula | Excel Worksheet Functions | |||
| Formula Problems | Excel Worksheet Functions | |||
| AVERAGE problems | Excel Worksheet Functions | |||
Hybrid Mode
