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
|
|||
|
|||
help on averaging function
Is there a way to not have a blank or zero value not be counted in an average
function. Example: I have a spreadsheet that has a year's worth of data. Currently, it is only filled in through June. At the very end, I want it to average some of the data. The formula has 12 cells that I want averaged but if one of those cells is blank or zero, I want it ignored. 6 of the 12 cells have data. Right now, I want it to average the 6 cells that have data. Next month, it will average 7, so on and so forth. Right now, it is taking the 6 values and dividing by the 12 cells, even though 6 of them contain nothing. I guess I want the calculation to change as data is entered without having to change the formula every month and add the new cell. Is this possible?? |
#2
|
|||
|
|||
help on averaging function
Yes. Assuming your data is in A1 thru A12 use an array formula:
=AVERAGE(IF(A1:A120,A1:A12,FALSE)) After entering the formula in the cell press CTRL+SHIFT+ENTER This formula creates an in memory array and replaces blanks and 0's with the value false. It puts any numbers in A1:A12 in the array. Then averages the numbers in the array. The average function ignores logical values. "Treesy" wrote in message ... Is there a way to not have a blank or zero value not be counted in an average function. Example: I have a spreadsheet that has a year's worth of data. Currently, it is only filled in through June. At the very end, I want it to average some of the data. The formula has 12 cells that I want averaged but if one of those cells is blank or zero, I want it ignored. 6 of the 12 cells have data. Right now, I want it to average the 6 cells that have data. Next month, it will average 7, so on and so forth. Right now, it is taking the 6 values and dividing by the 12 cells, even though 6 of them contain nothing. I guess I want the calculation to change as data is entered without having to change the formula every month and add the new cell. Is this possible?? |
#3
|
|||
|
|||
help on averaging function
Hi Treesy,
Not so sure what you're looking for but i give you a starter.. {=average(if(range0,range))} array entered, C+S+E, remove brackets from above... -- regards, driller ***** - dive with Jonathan Seagull "Treesy" wrote: Is there a way to not have a blank or zero value not be counted in an average function. Example: I have a spreadsheet that has a year's worth of data. Currently, it is only filled in through June. At the very end, I want it to average some of the data. The formula has 12 cells that I want averaged but if one of those cells is blank or zero, I want it ignored. 6 of the 12 cells have data. Right now, I want it to average the 6 cells that have data. Next month, it will average 7, so on and so forth. Right now, it is taking the 6 values and dividing by the 12 cells, even though 6 of them contain nothing. I guess I want the calculation to change as data is entered without having to change the formula every month and add the new cell. Is this possible?? |
#4
|
|||
|
|||
help on averaging function
I should further explain that your array formula will have { and }
surrounding it when you look at it in the formula bar. These identify an array formula. Do not type these in. Pressing CTRL+SHIFT+ENTER places the {} around the formula. So if in A1:A5 you had the values 2, 0, 4, blank, 6 the in-memory array would look like: 2, false, 4, false, 6. The array function would ignore the false values and average the 3 numbers 2, 4 and 6 for an average of 4. If you averaged these in the spread sheet column, the average function would average 2, 0, 4 , 6 for an average of 3. "Treesy" wrote in message ... Is there a way to not have a blank or zero value not be counted in an average function. Example: I have a spreadsheet that has a year's worth of data. Currently, it is only filled in through June. At the very end, I want it to average some of the data. The formula has 12 cells that I want averaged but if one of those cells is blank or zero, I want it ignored. 6 of the 12 cells have data. Right now, I want it to average the 6 cells that have data. Next month, it will average 7, so on and so forth. Right now, it is taking the 6 values and dividing by the 12 cells, even though 6 of them contain nothing. I guess I want the calculation to change as data is entered without having to change the formula every month and add the new cell. Is this possible?? |
#5
|
|||
|
|||
help on averaging function
Hi Treesy,
The average function will ignore blanks but not zero values. So if the formula in those cells is returning 0 alter it to return "" Alternatively this formula may help. =SUM(A1:A12)/COUNTIF(A1:A12,"0") adjust the ranges to suit. HTH Martin "Treesy" wrote in message ... Is there a way to not have a blank or zero value not be counted in an average function. Example: I have a spreadsheet that has a year's worth of data. Currently, it is only filled in through June. At the very end, I want it to average some of the data. The formula has 12 cells that I want averaged but if one of those cells is blank or zero, I want it ignored. 6 of the 12 cells have data. Right now, I want it to average the 6 cells that have data. Next month, it will average 7, so on and so forth. Right now, it is taking the 6 values and dividing by the 12 cells, even though 6 of them contain nothing. I guess I want the calculation to change as data is entered without having to change the formula every month and add the new cell. Is this possible?? |
#6
|
|||
|
|||
help on averaging function
now u can use Dave Thomas solution,,,
kinda weird of me why i use the "0", rather than "0" for your worth of data.. -- regards, driller ***** - dive with Jonathan Seagull "Treesy" wrote: Is there a way to not have a blank or zero value not be counted in an average function. Example: I have a spreadsheet that has a year's worth of data. Currently, it is only filled in through June. At the very end, I want it to average some of the data. The formula has 12 cells that I want averaged but if one of those cells is blank or zero, I want it ignored. 6 of the 12 cells have data. Right now, I want it to average the 6 cells that have data. Next month, it will average 7, so on and so forth. Right now, it is taking the 6 values and dividing by the 12 cells, even though 6 of them contain nothing. I guess I want the calculation to change as data is entered without having to change the formula every month and add the new cell. Is this possible?? |
#7
|
|||
|
|||
help on averaging function
excuse me Martin,
i send a late post, not seeing your one.. -- regards, driller ***** - dive with Jonathan Seagull "MartinW" wrote: Hi Treesy, The average function will ignore blanks but not zero values. So if the formula in those cells is returning 0 alter it to return "" Alternatively this formula may help. =SUM(A1:A12)/COUNTIF(A1:A12,"0") adjust the ranges to suit. HTH Martin "Treesy" wrote in message ... Is there a way to not have a blank or zero value not be counted in an average function. Example: I have a spreadsheet that has a year's worth of data. Currently, it is only filled in through June. At the very end, I want it to average some of the data. The formula has 12 cells that I want averaged but if one of those cells is blank or zero, I want it ignored. 6 of the 12 cells have data. Right now, I want it to average the 6 cells that have data. Next month, it will average 7, so on and so forth. Right now, it is taking the 6 values and dividing by the 12 cells, even though 6 of them contain nothing. I guess I want the calculation to change as data is entered without having to change the formula every month and add the new cell. Is this possible?? |
#8
|
|||
|
|||
help on averaging function
Treesy,
I use that exact need as the example on my Array Formulas web page. The formula you want is =AVERAGE(IF(A1:A120,A1:A12,FALSE)) Since this is an array formula, you must press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula enclosed in curly braces { }. Change the range A1:A12 to the range containing your data. See www.cpearson.com/Excel/ArrayFormulas.aspx -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Treesy" wrote in message ... Is there a way to not have a blank or zero value not be counted in an average function. Example: I have a spreadsheet that has a year's worth of data. Currently, it is only filled in through June. At the very end, I want it to average some of the data. The formula has 12 cells that I want averaged but if one of those cells is blank or zero, I want it ignored. 6 of the 12 cells have data. Right now, I want it to average the 6 cells that have data. Next month, it will average 7, so on and so forth. Right now, it is taking the 6 values and dividing by the 12 cells, even though 6 of them contain nothing. I guess I want the calculation to change as data is entered without having to change the formula every month and add the new cell. Is this possible?? |
#9
|
|||
|
|||
help on averaging function
Are you assuming there are no negative numbers? Treesy doesn't state that
there aren't. Treesy just wanted to eliminate 0's and blanks. "Chip Pearson" wrote in message ... Treesy, I use that exact need as the example on my Array Formulas web page. The formula you want is =AVERAGE(IF(A1:A120,A1:A12,FALSE)) Since this is an array formula, you must press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula enclosed in curly braces { }. Change the range A1:A12 to the range containing your data. See www.cpearson.com/Excel/ArrayFormulas.aspx -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Treesy" wrote in message ... Is there a way to not have a blank or zero value not be counted in an average function. Example: I have a spreadsheet that has a year's worth of data. Currently, it is only filled in through June. At the very end, I want it to average some of the data. The formula has 12 cells that I want averaged but if one of those cells is blank or zero, I want it ignored. 6 of the 12 cells have data. Right now, I want it to average the 6 cells that have data. Next month, it will average 7, so on and so forth. Right now, it is taking the 6 values and dividing by the 12 cells, even though 6 of them contain nothing. I guess I want the calculation to change as data is entered without having to change the formula every month and add the new cell. Is this possible?? |
#10
|
|||
|
|||
help on averaging function
Are you assuming there are no negative numbers? Treesy doesn't state that
there aren't. Treesy just wanted to eliminate 0's and blanks. "MartinW" wrote in message ... Hi Treesy, The average function will ignore blanks but not zero values. So if the formula in those cells is returning 0 alter it to return "" Alternatively this formula may help. =SUM(A1:A12)/COUNTIF(A1:A12,"0") adjust the ranges to suit. HTH Martin "Treesy" wrote in message ... Is there a way to not have a blank or zero value not be counted in an average function. Example: I have a spreadsheet that has a year's worth of data. Currently, it is only filled in through June. At the very end, I want it to average some of the data. The formula has 12 cells that I want averaged but if one of those cells is blank or zero, I want it ignored. 6 of the 12 cells have data. Right now, I want it to average the 6 cells that have data. Next month, it will average 7, so on and so forth. Right now, it is taking the 6 values and dividing by the 12 cells, even though 6 of them contain nothing. I guess I want the calculation to change as data is entered without having to change the formula every month and add the new cell. Is this possible?? |
Thread Tools | |
Display Modes | |
|
|