help on averaging function

[driller]

for the first time!!<g>
--
regards,
driller

*****
- dive with Jonathan Seagull

 

[Dave Thomas]

To extend your example:

Month 1. $200 Loss
Month 2. $100 Loss
Month 3. $0 Broke even
Month 4. $200 Profit
Month 5. $400 Profit.

Average Profit (including 0), $60, (excluding 0), $75.

 

[Meebers]

I average patients from the time they are seen in relationship to their
appointments. Some times they are taken in early, some times late, however
the goal is the average of 0. meaning they are taken in on time. ;0)

 

[driller]

Dave,
Which one of the similar sample average deems to fit the OP,,timewise...?
just curious really!

--
regards,
driller

*****
- dive with Jonathan Seagull

 

[driller]

Meebers,
u mean timewise..isnt it? but with 0.
--
regards,
driller

*****
- dive with Jonathan Seagull

 

[MartinW]

Yeah.
The only formula to work correctly in this situation is a simple
=AVERAGE(A1:A12)
With a caveat that zero values MUST be shown as zero and not blank
and blank values MUST be shown as blank and not zero.

Month 1. $200 Loss
Month 2. $100 Loss
Month 3. $0 Broke even
Month 4. $200 Profit
Month 5. $400 Profit.
Month 6. ""
Month 7. ""
Month 8. ""
etc.

Regards
Martin

 

[Meebers]

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.

 

[Harlan Grove]

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.

 

[Harlan Grove]

His data could be all negative numbers for all I know. He said he just
wanted to eliminate 0's and blanks.

....

In that case,

=AVERAGE(IF(dataset<0,dataset))

or getting really pedantic,

=CHOOSE(1+SIGN(COUNTIF(dataset,"<0"))+2*SIGN(COUNTIF(dataset,">0")),
0+N("no positivess or negatives, so live with 0"),
AVERAGE(IF(dataset<0,dataset)),AVERAGE(IF(dataset>0,dataset)),
AVERAGE(dataset)+N("both positives and negatives, so should include 0s"))

 

[Harlan Grove]

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.

 

[Harlan Grove]

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.

 

[Dave Thomas]

You missed the point entirely. You should have looked at the original
message.

 

[Dave Thomas]

Does this crap go on all the time in these newsgroups?

 

[Dave Thomas]

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.

 

[Dave Thomas]

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.

 

[Dave Thomas]

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]

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?

 

[Harlan Grove]

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.

 

[Harlan Grove]

Does this crap go on all the time in these newsgroups?

....

Only until the obtuse party finally realizes how obtuse they're being.

 

[Dave Thomas]

Then address your concerns to Treesy, not to me!

You sound like the kind of person who blames the messenger for the message.