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 |
#31
|
|||
|
|||
help on averaging function
"Meebers" wrote...
The goal is to see the patients on time. If we are late, it is a negative amount, early it is a positive. Yes the differences are in times, results are formated as a number since negative #'s will not format as hh:mm. .... TIf you saw 60 patients, 50 on time, 8 5 minutes early and 2 30 minutes late, would you want the average to be 20 seconds late or 2 miutes late? The first includes the 50 on time (0) values, the latter doesn't. |
#32
|
|||
|
|||
help on averaging function
You missed the point entirely. You should have looked at the original
message. "Harlan Grove" wrote in message ... "Dave Thomas" wrote... Actually the average would be 100, but your point is well taken. You can have a mixture of positive and negative with no zeroes. ... Actually the example completely misses the point. It'd be necessary to use the column containing profit or loss in order to change signs. And the lovely example provides a very nearly meaningless average - cumulative return divided by number of periods is +100, but 2 out of 3 periods generated negatives. Further, there are no zeros. If there are no zeros, then just use AVERAGE. If, however, there are zeros, e.g., Month 1 100 loss Month 2 100 loss Month 3 500 profit Month 4 0 no return Month 5 0 no return is the average monthly return still 100 or is it now 60? This has everything to do with real world processes. Averages of all negatives, or averages of all positives, or averages of all numbers (positive, negative AND zero) make sense. Averages of positives and negatives but not zero never make sense. |
#33
|
|||
|
|||
help on averaging function - Yet another example of much ado about nothing.
Does this crap go on all the time in these newsgroups?
"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?? |
#34
|
|||
|
|||
help on averaging function
Once again, read the original message. In my response to it, I gave Treesy
what Treesy asked for. I don't care about your world of negatives, positives and zeros. So calm down, read the original message and you'll see that my response provided the answer sought. I do not know what Treesy's data is used for nor do I care. "Harlan Grove" wrote in message ... "Dave Thomas" wrote... 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. ... Name a physical, financial or not entirely esoteric process that could produce positive and negative values but not zero values. The point is that if a set of numbers can include positive and negative values, they very likely could also contain legitimate zero values that shouldn't be ignored. As for blanks, they're always ignored unless you mean "" and are (mis)using AVERAGEA instead of AVERAGE or are averaging values through an external reference to a closed workbook. |
#35
|
|||
|
|||
help on averaging function
Once again, read the original message. In my response to it, I gave Treesy
what Treesy asked for. I don't care about your world of negatives, positives and zeros. So calm down, read the original message and you'll see that my response provided the answer sought. I do not know what Treesy's data is used for nor do I care. "Harlan Grove" wrote in message ... "Dave Thomas" wrote... 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. ... Name a physical, financial or not entirely esoteric process that could produce positive and negative values but not zero values. The point is that if a set of numbers can include positive and negative values, they very likely could also contain legitimate zero values that shouldn't be ignored. As for blanks, they're always ignored unless you mean "" and are (mis)using AVERAGEA instead of AVERAGE or are averaging values through an external reference to a closed workbook. |
#36
|
|||
|
|||
help on averaging function
Once again, read the original message. In my response to it, I gave Treesy
what Treesy asked for. I don't care about your world of negatives, positives and zeros. So calm down, read the original message and you'll see that my response provided the answer sought. I do not know what Treesy's data is used for nor do I care. "Harlan Grove" wrote in message ... "Dave Thomas" wrote... Actually the average would be 100, but your point is well taken. You can have a mixture of positive and negative with no zeroes. ... Actually the example completely misses the point. It'd be necessary to use the column containing profit or loss in order to change signs. And the lovely example provides a very nearly meaningless average - cumulative return divided by number of periods is +100, but 2 out of 3 periods generated negatives. Further, there are no zeros. If there are no zeros, then just use AVERAGE. If, however, there are zeros, e.g., Month 1 100 loss Month 2 100 loss Month 3 500 profit Month 4 0 no return Month 5 0 no return is the average monthly return still 100 or is it now 60? This has everything to do with real world processes. Averages of all negatives, or averages of all positives, or averages of all numbers (positive, negative AND zero) make sense. Averages of positives and negatives but not zero never make sense. |
#37
|
|||
|
|||
help on averaging function
"Dave Thomas" wrote...
You missed the point entirely. You should have looked at the original message. .... I did. The OP's process is flawed if future months contain 0s rather than are blank. That should be corrected by making those cells contain nothing or "", which AVERAGE automatically skips. So what's the correct answer if any of the OP's HISTORICAL months contain 0s? |
#38
|
|||
|
|||
help on averaging function
"Dave Thomas" wrote...
Once again, read the original message. In my response to it, I gave Treesy what Treesy asked for. . . . Not always the best thing to do. Some of us share our own experience, which includes advice for avoiding common unforseen errors. I accept the fact that you don't understand that averaging positive and negative values but not zeros is almost always an error, but not everyone who responds in this newsgroup has experience (or learned from it). . . . I don't care about your world of negatives, positives and zeros. . . And you're obviously proud of missing the point. . . . So calm down, read the original message and you'll see that my response provided the answer sought. I do not know what Treesy's data is used for nor do I care. Which is why your response should be ignored. |
#39
|
|||
|
|||
help on averaging function - Yet another example of much ado about nothing.
"Dave Thomas" wrote...
Does this crap go on all the time in these newsgroups? .... Only until the obtuse party finally realizes how obtuse they're being. |
#40
|
|||
|
|||
help on averaging function
Then address your concerns to Treesy, not to me!
You sound like the kind of person who blames the messenger for the message. "Harlan Grove" wrote in message ... "Dave Thomas" wrote... Once again, read the original message. In my response to it, I gave Treesy what Treesy asked for. . . . Not always the best thing to do. Some of us share our own experience, which includes advice for avoiding common unforseen errors. I accept the fact that you don't understand that averaging positive and negative values but not zeros is almost always an error, but not everyone who responds in this newsgroup has experience (or learned from it). . . . I don't care about your world of negatives, positives and zeros. . . And you're obviously proud of missing the point. . . . So calm down, read the original message and you'll see that my response provided the answer sought. I do not know what Treesy's data is used for nor do I care. Which is why your response should be ignored. |
Thread Tools | |
Display Modes | |
|
|