Average function

[Karen]

I am trying to average time values over several weeks.
However, I would like to exclude time values that are 0.
Below is an example:
Wk 1 Wk 2 Wk 3 Wk 4 Wk 5 Month
0:03:00 0:02:30 0:02:45 0:00:00 0:00:00 (AVERAGE A12:E12)

How can I exclude 0 values so mnthly average is correct
during the 3rd week of the month?

Thanks!

 

[vtisix]

Try:

=average(if(A12:E12<>0,A12:E12))

You have to execute the formula with Ctrl+Shift+Enter because this is an
array formula.

 

[Karen]

Thank you - that worked. However, now I have another
twist: How can I do the same thing with the following
formula?
=AVERAGE(AP43,AG43,X43,O43,F43)
The fields aren't in a row.....

Thanks for your assistance! You've saved me hours!

 

[Harlan Grove]

I am trying to average time values over several weeks.
However, I would like to exclude time values that are 0.
Below is an example:
Wk 1 Wk 2 Wk 3 Wk 4 Wk 5 Month
0:03:00 0:02:30 0:02:45 0:00:00 0:00:00 (AVERAGE A12:E12)

How can I exclude 0 values so mnthly average is correct
during the 3rd week of the month?

The ideal way to make sure your average was correct would be to have week 4
and 5 formulas return "" rather than 0 when they haven't yet been reached.
Then the simple AVERAGE formula would work.

 

[Harlan Grove]

Thank you - that worked. However, now I have another
twist: How can I do the same thing with the following
formula?
=AVERAGE(AP43,AG43,X43,O43,F43)
The fields aren't in a row.....

....

No, the fields aren't in a single range, but they're all separated by 9
columns in the same row. You could use the array formula

=AVERAGE(IF(N(OFFSET(F43,0,{0,9,18,27,36},1,1))>0,
N(OFFSET(F43,0,{0,9,18,27,36},1,1))))

If there were no pattern to the cells, you could use

=SUM((AP43,AG43,X43,O43,F43))/
INDEX(FREQUENCY((AP43,AG43,X43,O43,F43),{0}),2)

 

[Ron Rosenfeld]

If there were no pattern to the cells, you could use

=SUM((AP43,AG43,X43,O43,F43))/
INDEX(FREQUENCY((AP43,AG43,X43,O43,F43),{0}),2)

Harlan,

Very neat use of the FREQUENCY function!

Are the braces required around bins_array in the above function? Simple
testing here seems to suggest it works without it.

Also, it seems that this technique will only work for an array of positive
numbers.


--ron

 

[Leo Heuser]

If there were no pattern to the cells, you could use

=SUM((AP43,AG43,X43,O43,F43))/
INDEX(FREQUENCY((AP43,AG43,X43,O43,F43),{0}),2)

The OP doesn't state if the time is overtime or working time,
although the example seems to suggest overtime, so if a single
week has undertime, your formula won't work since

FREQUENCY((AP43,AG43,X43,O43,F43),N)

wil return the number of instances <=N.

LeoH

 

[Harlan Grove]

FREQUENCY((AP43,AG43,X43,O43,F43),N)

wil return the number of instances <=N.

See my response to Ron since (1) you haven't seen my previous posts that
address that possibility, or (2) didn't understand them, and (3) don't seem
inclined to figure out how to handle this yourself.

 

[Harlan Grove]

The OP doesn't state if the time is overtime or working time,
although the example seems to suggest overtime, so if a single
week has undertime, . . .

....

If 'undertime' were an issue, then the OP must avoid showing zeros in all
weeks not yet completed since if undertime and overtime were both
possibilities, then exact time (zero) would also be a possibility, so
averages should include zero or they'd be meaningless. In other words, OP
should follow my advice in my original response in this thread.

 

[Leo Heuser]

See my response to Ron since (1) you haven't seen my previous posts that
address that possibility, or (2) didn't understand them, and (3) don't seem
inclined to figure out how to handle this yourself.

No I haven't seen your response. It wasn't at the mailserver, when I
responded,
and it isn't now (13:17 or 1:17 or whatever the time shows around).
No need to open up the sewer. I was just pointing out a situation, where
your formula didn't work. No big deal.
As for (3) I actually answered at 8:58 to-day in Karen's other thread
"Averaging time values excluding zeroes"

LeoH

 

[Harlan Grove]

Are the braces required around bins_array in the above function?
Simple testing here seems to suggest it works without it.

Maybe not. I thought I had trouble with scalar 2nd args in XL97, but I don't
have it on this machine to test.

Also, it seems that this technique will only work for an array of
positive numbers.

Fine. If an arbitrary range could contain positive and negative values and
zeros should be excluded (though doing so is mathematically and
statistically obtuse), use

=SUM((arbitrary range))/
SUMPRODUCT(FREQUENCY((arbitrary range),{-1E-300,0}),{1;0;1})

 

[Harlan Grove]

No I haven't seen your response. It wasn't at the mailserver, when I
responded, and it isn't now (13:17 or 1:17 or whatever the time shows
around).

....

It should be there now.

As for (3) I actually answered at 8:58 to-day in Karen's other thread
"Averaging time values excluding zeroes"

Elegant & general it ain't, but there's some value in showing brute force if
only to remind OPs it's an alternative.

BTW, where in either thread did the OP mention overtime? The word 'over' did
appear once, but not 'overtime', so how did you dream up undertime?

 

[Leo Heuser]

...

It should be there now.


Elegant & general it ain't, but there's some value in showing brute force
if only to remind OPs it's an alternative.

I didn't ask for your opinion, so apparently it's necessary to quote your
original statement:

(3) don't seem inclined to figure out how to handle this yourself

and my answer:

As for (3) I actually answered at 8:58 to-day in Karen's other thread
"Averaging time values excluding zeroes"

So I *did* handle it myself. My solution may be inferior to yours (it is),
but that had nothing to do with your (3).

Man, why is it so hard for you to be friendly?

BTW, where in either thread did the OP mention overtime? The word 'over' did
appear once, but not 'overtime', so how did you dream up undertime?

The OP didn't. I assumed, that it had to do with working time and the amount
of time used every week indicated overtime. I may be wrong, but that's what
I assumed.

LeoH

 

[Harlan Grove]

Man, why is it so hard for you to be friendly?

Not my purpose here.

. . . I assumed, that it had to do with working time and the amount
of time used every week indicated overtime. I may be wrong, but that's
what I assumed.

OP was also showing times formatted as times, so unless OP uses 1904 date
system, times would need to be nonnegative. Also, sample times shown were in
minutes and seconds with all hours portions zero. I've yet to come across
overtime procedures that track seconds.

Admit it: you wanted to be picky (fine with me, see my response to Ron), but
you got cute ('undertime'). You get cute while getting picky with me, this
is what you can expect in return.

 

[Ron Rosenfeld]

Maybe not. I thought I had trouble with scalar 2nd args in XL97, but I don't
have it on this machine to test.


Fine. If an arbitrary range could contain positive and negative values and
zeros should be excluded (though doing so is mathematically and
statistically obtuse), use

=SUM((arbitrary range))/
SUMPRODUCT(FREQUENCY((arbitrary range),{-1E-300,0}),{1;0;1})

Very nice. Those are keepers.


--ron

 

[Frank Kabel]

Are the braces required around bins_array in the above
function?

Maybe not. I thought I had trouble with scalar 2nd args in XL97, but I don't
have it on this machine to test.

Hi Harlan
works fine without bracktes under Excel 97 (English)
Frank

 

[Leo Heuser]

Not my purpose here.

Of course it's not your purpose, but your purpose hardly prevents
you from being it.

OP was also showing times formatted as times, so unless OP uses 1904 date
system, times would need to be nonnegative.

Not necessarily. Excel can handle negative times in 1900 date system, it
just can't display them.

Also, sample times shown were in
minutes and seconds with all hours portions zero. I've yet to come across
overtime procedures that track seconds.

Good point! I didn't notice that.

Admit it: you wanted to be picky (fine with me, see my response to Ron), but
you got cute ('undertime'). You get cute while getting picky with me, this
is what you can expect in return.

I'm afraid some vital points must have been lost in the tranlation (my
answer translated to English). I simply don't understand, what you're saying
here!
I had no intention of being "picky" or "cute". Does "undertime" has some
hidden meaning. I don't know, if the word exists in English at all, but I
was confident, that my meaning was clear, yet there's always a risk, when
one is trying to communicate in a foreign language.

BTW it's the first time, I have experienced, that my posting is not on the
server, only your answer!

LeoH

 

[Harlan Grove]

Of course it's not your purpose, but your purpose hardly prevents
you from being it.

....

Why would I want to? Are you ascribing to me your own preferences?

I had no intention of being "picky" or "cute". . . .

Clearly my mistake.

 

[Leo Heuser]

...

Why would I want to?

Why not?

Are you ascribing to me your own preferences?

I'm not quite sure, what you mean here, but probably "yes".
I believe, that a softer tone would create a more comfortable
and relaxed atmosphere.
I, for one, use a lot of time and energy to get the wording right in my
answers to you because I feel, that the slightest error will result in a
poisenous response.
I know, that a typical answer from you to that would be: "Then don't
answer.", and I might do that some day, but I would greatly prefer the
relaxed model.

LeoH

 

[hgrove]

Leo Heuser wrote...

Harlan Grove wrote... ...

I'm not quite sure, what you mean here, but probably "yes".[/QUOTE]
...

Then too damn bad 'cause I don't want to be like you.

I believe, that a softer tone would create a more comfortable
and relaxed atmosphere. I, for one, use a lot of time and energy
to get the wording right in my answers to you because I feel,
that the slightest error will result in a poisenous response.
I know, that a typical answer from you to that would be: "Then
don't answer.", and I might do that some day, but I would
greatly prefer the relaxed model.

Ain't gonna happen. If you feel like responding to me, be careful o
just rethink your urges because I have no intention of treating you th
way you want to be treated.

Ain't USENET great! Ain't freedom of speach great! [Yes, it *is* th
freedom to be a jerk, and some of us will strenuously defend tha
right.