Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average formula only works partially.....
Whew.anyone can help me with thisId be truly grateful.
My spreadsheet has several columns that need to averaged on time spent on that particular project per month. (example) Project 1 Project 2 Jan 0 0 Feb 0 0 Mar 0 0 Apr 0 0 May 0 0 Jun 0 0 Jul 0 0 Aug 538 0 Sep 593 0 Oct 0 0 Nov 0 0 Dec 0 0 Aver Total 566 Blank s/b 0 Average Total for Year I'm using this formula =IF(ISERROR(AVERAGE(IF(M6:M17<0, M6:M17))),"",AVERAGE(IF(M6:M17<0, M6:M17))) on a multiple column spreadsheet....it works great....however, if one column has all zero's...then it returns a blank (because it initially returned a #div/0 error)....I tried to change the formula to =IF(ISERROR(AVERAGE(IF(M6:M17<0,I6:M17))),"0",AVE RAGE(IF(M6:M17<0, M6:M17))) inserting the 0 between the quotation marks; however, the column totals with whole numbers end up with a zero as the total. Something is not quite right about the formula...or I need to add more to the formula? Basically my end result needs to divide the sum of those 12 cells in the column by 12 IF they ALL have numbers greater than 0. If any of the 12 cells has a 0then I need to divide the sum of the 12 cells by the number of cells that actually have a whole number. If the column is all zerosit needs to have a zero in the total I hope that ALL makes sense.... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average formula only works partially.....
Try this:
=IF(SUM(M6:M17)=0,0,SUMIF(M6:M17,"0")/COUNTIF(M6:M17,"0")) -- Biff Microsoft Excel MVP "acbel40" wrote in message ... Whew..anyone can help me with this.I'd be truly grateful.. My spreadsheet has several columns that need to averaged on time spent on that particular project per month. (example) Project 1 Project 2 Jan 0 0 Feb 0 0 Mar 0 0 Apr 0 0 May 0 0 Jun 0 0 Jul 0 0 Aug 538 0 Sep 593 0 Oct 0 0 Nov 0 0 Dec 0 0 Aver Total 566 Blank s/b 0 Average Total for Year I'm using this formula =IF(ISERROR(AVERAGE(IF(M6:M17<0, M6:M17))),"",AVERAGE(IF(M6:M17<0, M6:M17))) on a multiple column spreadsheet....it works great....however, if one column has all zero's...then it returns a blank (because it initially returned a #div/0 error)....I tried to change the formula to =IF(ISERROR(AVERAGE(IF(M6:M17<0,I6:M17))),"0",AVE RAGE(IF(M6:M17<0, M6:M17))) inserting the 0 between the quotation marks; however, the column totals with whole numbers end up with a zero as the total. Something is not quite right about the formula...or I need to add more to the formula? Basically my end result needs to divide the sum of those 12 cells in the column 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. If the column is all zero's.it needs to have a zero in the total I hope that ALL makes sense.... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average formula only works partially.....
I would do something like this:
=IF(ISERROR(AVERAGE(IF($B$2:$B$101<0,$B$2:$B$101) )),"",AVERAGE(IF($B$2:$B$101<0,$B$2:$B$101))) Enter as Ctrl+Shift+Enter, not just enter. Also, check this out: http://www.ozgrid.com/Excel/average-without-zero.htm HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "acbel40" wrote: Whew.anyone can help me with thisId be truly grateful. My spreadsheet has several columns that need to averaged on time spent on that particular project per month. (example) Project 1 Project 2 Jan 0 0 Feb 0 0 Mar 0 0 Apr 0 0 May 0 0 Jun 0 0 Jul 0 0 Aug 538 0 Sep 593 0 Oct 0 0 Nov 0 0 Dec 0 0 Aver Total 566 Blank s/b 0 Average Total for Year I'm using this formula =IF(ISERROR(AVERAGE(IF(M6:M17<0, M6:M17))),"",AVERAGE(IF(M6:M17<0, M6:M17))) on a multiple column spreadsheet....it works great....however, if one column has all zero's...then it returns a blank (because it initially returned a #div/0 error)....I tried to change the formula to =IF(ISERROR(AVERAGE(IF(M6:M17<0,I6:M17))),"0",AVE RAGE(IF(M6:M17<0, M6:M17))) inserting the 0 between the quotation marks; however, the column totals with whole numbers end up with a zero as the total. Something is not quite right about the formula...or I need to add more to the formula? Basically my end result needs to divide the sum of those 12 cells in the column by 12 IF they ALL have numbers greater than 0. If any of the 12 cells has a 0then I need to divide the sum of the 12 cells by the number of cells that actually have a whole number. If the column is all zerosit needs to have a zero in the total I hope that ALL makes sense.... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average formula only works partially.....
Please note that this is an array formula. you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =IF(SUM(M6:M17),AVERAGE(IF(M6:M170,M6:M17)),0) If this post helps click Yes --------------- Jacob Skaria "acbel40" wrote: Whew.anyone can help me with thisId be truly grateful. My spreadsheet has several columns that need to averaged on time spent on that particular project per month. (example) Project 1 Project 2 Jan 0 0 Feb 0 0 Mar 0 0 Apr 0 0 May 0 0 Jun 0 0 Jul 0 0 Aug 538 0 Sep 593 0 Oct 0 0 Nov 0 0 Dec 0 0 Aver Total 566 Blank s/b 0 Average Total for Year I'm using this formula =IF(ISERROR(AVERAGE(IF(M6:M17<0, M6:M17))),"",AVERAGE(IF(M6:M17<0, M6:M17))) on a multiple column spreadsheet....it works great....however, if one column has all zero's...then it returns a blank (because it initially returned a #div/0 error)....I tried to change the formula to =IF(ISERROR(AVERAGE(IF(M6:M17<0,I6:M17))),"0",AVE RAGE(IF(M6:M17<0, M6:M17))) inserting the 0 between the quotation marks; however, the column totals with whole numbers end up with a zero as the total. Something is not quite right about the formula...or I need to add more to the formula? Basically my end result needs to divide the sum of those 12 cells in the column by 12 IF they ALL have numbers greater than 0. If any of the 12 cells has a 0then I need to divide the sum of the 12 cells by the number of cells that actually have a whole number. If the column is all zerosit needs to have a zero in the total I hope that ALL makes sense.... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average formula only works partially.....
Didn't work (I entered it as an array formula)....gave 0 answer in the column
that had whole numbers in it....and #value error on the zero column "Jacob Skaria" wrote: Please note that this is an array formula. you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =IF(SUM(M6:M17),AVERAGE(IF(M6:M170,M6:M17)),0) If this post helps click Yes --------------- Jacob Skaria "acbel40" wrote: Whew.anyone can help me with thisId be truly grateful. My spreadsheet has several columns that need to averaged on time spent on that particular project per month. (example) Project 1 Project 2 Jan 0 0 Feb 0 0 Mar 0 0 Apr 0 0 May 0 0 Jun 0 0 Jul 0 0 Aug 538 0 Sep 593 0 Oct 0 0 Nov 0 0 Dec 0 0 Aver Total 566 Blank s/b 0 Average Total for Year I'm using this formula =IF(ISERROR(AVERAGE(IF(M6:M17<0, M6:M17))),"",AVERAGE(IF(M6:M17<0, M6:M17))) on a multiple column spreadsheet....it works great....however, if one column has all zero's...then it returns a blank (because it initially returned a #div/0 error)....I tried to change the formula to =IF(ISERROR(AVERAGE(IF(M6:M17<0,I6:M17))),"0",AVE RAGE(IF(M6:M17<0, M6:M17))) inserting the 0 between the quotation marks; however, the column totals with whole numbers end up with a zero as the total. Something is not quite right about the formula...or I need to add more to the formula? Basically my end result needs to divide the sum of those 12 cells in the column by 12 IF they ALL have numbers greater than 0. If any of the 12 cells has a 0then I need to divide the sum of the 12 cells by the number of cells that actually have a whole number. If the column is all zerosit needs to have a zero in the total I hope that ALL makes sense.... |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average formula only works partially.....
In a fresh workbook enter the values in the range M6:M17 and try the
below...I have tested this.. If this post helps click Yes --------------- Jacob Skaria "acbel40" wrote: Didn't work (I entered it as an array formula)....gave 0 answer in the column that had whole numbers in it....and #value error on the zero column "Jacob Skaria" wrote: Please note that this is an array formula. you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =IF(SUM(M6:M17),AVERAGE(IF(M6:M170,M6:M17)),0) If this post helps click Yes --------------- Jacob Skaria "acbel40" wrote: Whew.anyone can help me with thisId be truly grateful. My spreadsheet has several columns that need to averaged on time spent on that particular project per month. (example) Project 1 Project 2 Jan 0 0 Feb 0 0 Mar 0 0 Apr 0 0 May 0 0 Jun 0 0 Jul 0 0 Aug 538 0 Sep 593 0 Oct 0 0 Nov 0 0 Dec 0 0 Aver Total 566 Blank s/b 0 Average Total for Year I'm using this formula =IF(ISERROR(AVERAGE(IF(M6:M17<0, M6:M17))),"",AVERAGE(IF(M6:M17<0, M6:M17))) on a multiple column spreadsheet....it works great....however, if one column has all zero's...then it returns a blank (because it initially returned a #div/0 error)....I tried to change the formula to =IF(ISERROR(AVERAGE(IF(M6:M17<0,I6:M17))),"0",AVE RAGE(IF(M6:M17<0, M6:M17))) inserting the 0 between the quotation marks; however, the column totals with whole numbers end up with a zero as the total. Something is not quite right about the formula...or I need to add more to the formula? Basically my end result needs to divide the sum of those 12 cells in the column by 12 IF they ALL have numbers greater than 0. If any of the 12 cells has a 0then I need to divide the sum of the 12 cells by the number of cells that actually have a whole number. If the column is all zerosit needs to have a zero in the total I hope that ALL makes sense.... |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average formula only works partially.....
I apologize...it DID work....!!! THANK YOU very much!!!
"Jacob Skaria" wrote: Please note that this is an array formula. you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =IF(SUM(M6:M17),AVERAGE(IF(M6:M170,M6:M17)),0) If this post helps click Yes --------------- Jacob Skaria "acbel40" wrote: Whew.anyone can help me with thisId be truly grateful. My spreadsheet has several columns that need to averaged on time spent on that particular project per month. (example) Project 1 Project 2 Jan 0 0 Feb 0 0 Mar 0 0 Apr 0 0 May 0 0 Jun 0 0 Jul 0 0 Aug 538 0 Sep 593 0 Oct 0 0 Nov 0 0 Dec 0 0 Aver Total 566 Blank s/b 0 Average Total for Year I'm using this formula =IF(ISERROR(AVERAGE(IF(M6:M17<0, M6:M17))),"",AVERAGE(IF(M6:M17<0, M6:M17))) on a multiple column spreadsheet....it works great....however, if one column has all zero's...then it returns a blank (because it initially returned a #div/0 error)....I tried to change the formula to =IF(ISERROR(AVERAGE(IF(M6:M17<0,I6:M17))),"0",AVE RAGE(IF(M6:M17<0, M6:M17))) inserting the 0 between the quotation marks; however, the column totals with whole numbers end up with a zero as the total. Something is not quite right about the formula...or I need to add more to the formula? Basically my end result needs to divide the sum of those 12 cells in the column by 12 IF they ALL have numbers greater than 0. If any of the 12 cells has a 0then I need to divide the sum of the 12 cells by the number of cells that actually have a whole number. If the column is all zerosit needs to have a zero in the total I hope that ALL makes sense.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average formula works but.... | Excel Worksheet Functions | |||
A formula to AVERAGE IF but only average a set number of values | Excel Worksheet Functions | |||
partially replace formula problem. Please help! | Excel Discussion (Misc queries) | |||
Formula Partially Works, Please Help | Excel Worksheet Functions | |||
how does one convert text to a formula "average(A:A)" to =average( | Excel Worksheet Functions |