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 |
#11
|
|||
|
|||
help on averaging function
sorry Sir Chip Pearson,
not aware of that, yet the starter solution i gave seems the same considering that (just guessing its the positive), my logic is if 0 is out of the numeric range, then either the positive or negative range will be a selected criteria *and not necessarily both of it...* unless deem logical...in any sense. maybe treesy has to specify now which range of data he/she prefer "the positive or the negative" cordially, -- regards, driller ***** - dive with Jonathan Seagull "Chip Pearson" wrote: 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?? |
#12
|
|||
|
|||
help on averaging function
You're right Dave, I didn't allow for negatives.
So if the remaining cells are all blanks =AVERAGE(A1:A12) if the remaining cells are all zeroes =SUM(A1:A12)/COUNTIF(A1:A12,"0") if the remaining cells are a mixture of blanks and zeroes Then you will need Dave's array formula. Regards Martin "Dave Thomas" wrote in message t... 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?? |
#13
|
|||
|
|||
help on averaging function
Are you assuming there are no negative numbers?
Actually, yes I am assuming that. In practical terms, it wouldn't make sense to average both positive and negative numbers and omit zero. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Dave Thomas" wrote in message t... 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?? |
#14
|
|||
|
|||
help on averaging function
His data could be all negative numbers for all I know. He said he just
wanted to eliminate 0's and blanks. "Chip Pearson" wrote in message ... Are you assuming there are no negative numbers? Actually, yes I am assuming that. In practical terms, it wouldn't make sense to average both positive and negative numbers and omit zero. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Dave Thomas" wrote in message t... 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?? |
#15
|
|||
|
|||
help on averaging function
thanks all,
outta here! -- regards, driller ***** - dive with Jonathan Seagull "driller" wrote: sorry Sir Chip Pearson, not aware of that, yet the starter solution i gave seems the same considering that (just guessing its the positive), my logic is if 0 is out of the numeric range, then either the positive or negative range will be a selected criteria *and not necessarily both of it...* unless deem logical...in any sense. maybe treesy has to specify now which range of data he/she prefer "the positive or the negative" cordially, -- regards, driller ***** - dive with Jonathan Seagull "Chip Pearson" wrote: 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?? |
#16
|
|||
|
|||
help on averaging function
Actually, yes I am assuming that. In practical terms, it wouldn't make
sense to average both positive and negative numbers and omit zero. Hi Chip, Where would zero come into the following? 1st Month: $100 loss 2nd Month: $100 loss 3rd Month: $500 profit 3 monthly average profit would be $100 profit. Regards Martin |
#17
|
|||
|
|||
help on averaging function
hehe, have fun!
"driller" wrote in message ... thanks all, outta here! -- regards, driller ***** - dive with Jonathan Seagull "driller" wrote: sorry Sir Chip Pearson, not aware of that, yet the starter solution i gave seems the same considering that (just guessing its the positive), my logic is if 0 is out of the numeric range, then either the positive or negative range will be a selected criteria *and not necessarily both of it...* unless deem logical...in any sense. maybe treesy has to specify now which range of data he/she prefer "the positive or the negative" cordially, -- regards, driller ***** - dive with Jonathan Seagull "Chip Pearson" wrote: 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?? |
#18
|
|||
|
|||
help on averaging function
Actually the average would be 100, but your point is well taken. You can
have a mixture of positive and negative with no zeroes. "MartinW" wrote in message ... Actually, yes I am assuming that. In practical terms, it wouldn't make sense to average both positive and negative numbers and omit zero. Hi Chip, Where would zero come into the following? 1st Month: $100 loss 2nd Month: $100 loss 3rd Month: $500 profit 3 monthly average profit would be $100 profit. Regards Martin |
#19
|
|||
|
|||
help on averaging function
I misread your average. You are correct. But the point stands: it's quite
possible to have positive numbers and negative numbers in an average. "MartinW" wrote in message ... Actually, yes I am assuming that. In practical terms, it wouldn't make sense to average both positive and negative numbers and omit zero. Hi Chip, Where would zero come into the following? 1st Month: $100 loss 2nd Month: $100 loss 3rd Month: $500 profit 3 monthly average profit would be $100 profit. Regards Martin |
#20
|
|||
|
|||
help on averaging function
Hi Martin,
the sample is great, yet if there is no category for a *0* (b/w loss and profit), then there will be be no existing *0* as a Data... kinda llike..give me *0* but its not categorized in the Data list***then what is the *0* data stands for? g -- regards, driller ***** - dive with Jonathan Seagull "MartinW" wrote: Actually, yes I am assuming that. In practical terms, it wouldn't make sense to average both positive and negative numbers and omit zero. Hi Chip, Where would zero come into the following? 1st Month: $100 loss 2nd Month: $100 loss 3rd Month: $500 profit 3 monthly average profit would be $100 profit. Regards Martin |
Thread Tools | |
Display Modes | |
|
|