|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
October 17th, 2009, 07:19 AM
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 zero’s. 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:I170, I6:I17,""))…which works fine…. But some of the columns have ALL zero’s….and of course I get the #DIV/0 error, so I tried the =SUM(I6:I17)/COUNTIF(I6:I17,"0") on all the columns, didn’t 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…I’d be truly grateful…. 
|
|
#2
October 17th, 2009, 08:28 AM
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 zero’s. 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:I170, I6:I17,""))…which works fine…. But some of the columns have ALL zero’s….and of course I get the #DIV/0 error, so I tried the =SUM(I6:I17)/COUNTIF(I6:I17,"0") on all the columns, didn’t 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…I’d be truly grateful….
|
|
#3
October 18th, 2009, 10:15 AM
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:I170, I6:I17,""))… which works fine…. But some of the columns have ALL zero’s….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:I170, I6:I17))) More generally: =if(countif(I6:I17,0)=count(I6:I17), 0, average(if(I6:I170, 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 zero’s. 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:I170, I6:I17,""))…which works fine…. But some of the columns have ALL zero’s….and of course I get the #DIV/0 error, so I tried the =SUM(I6:I17)/COUNTIF(I6:I17,"0") on all the columns, didn’t 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…I’d be truly grateful….
|
|
#4
October 18th, 2009, 06:09 PM
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 zero’s. 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:I170, I6:I17,""))…which works fine…. But some of the columns have ALL zero’s….and of course I get the #DIV/0 error, so I tried the =SUM(I6:I17)/COUNTIF(I6:I17,"0") on all the columns, didn’t 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…I’d be truly grateful….
|
|
#5
October 19th, 2009, 04:13 PM
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 zero’s. 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:I170, I6:I17,""))…which works fine…. But some of the columns have ALL zero’s….and of course I get the #DIV/0 error, so I tried the =SUM(I6:I17)/COUNTIF(I6:I17,"0") on all the columns, didn’t 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…I’d be truly grateful….
|
|
#6
October 19th, 2009, 05:00 PM
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 zero’s. 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:I170, I6:I17,""))…which works fine…. But some of the columns have ALL zero’s….and of course I get the #DIV/0 error, so I tried the =SUM(I6:I17)/COUNTIF(I6:I17,"0") on all the columns, didn’t 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…I’d be truly grateful…. -- Dave Peterson
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
