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.

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.

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??

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.

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.

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.

"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?

"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.

"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.

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.