I am an Idiot Woman

[Pepper]

Not a nice thing to say to a coworker, but was he right.
I had a discussion with a male collegue about the difference between and
empty cell and a cell with a zero. He called me a young, inexperienced,
idiot woman because I insist his workbook is wrong since he did not account
for empty cells that are awaiting data.
He uses, for example, =C14+C15+C16+C17)/4 to calculate average value, and
C16 and C17 dont have data yet - he insists his result is the G-d given
answer.
I suggest that =Average (C14:C17) is correct and that he is getting the
wrong answer.
It seems that his favorite feature, Conditional Formatting does not
distinguish between Null and 0.
Please settle the argument - I should complain to HR but will get far better
satisfaction if I can site some reference and call him a #$&^ IDIOT!
Thanks

 

[Gary''s Student]

I assume that you are a woman. Your idiocy can only be verified with an IQ
test.

Using =AVERAGE() is better because it ignores missing data.

(b.t.w. - hiding a 0 with conditional formatting does not make it blank, it
is still a 0)

 

[Don Guillett]

If he wants to divide by 4 regardless then
=(C14+C15+C16+C17)/4
is the same as
=sum(c14:c17)/4
is the same as
=average(c14:c17) if there is a 0 in one cell. But if one blank it will
divide by 3 instead, etc

 

[Pepper]

My point is, his formula always divides by 4. If the range indicates four
fiscal quarters, and the last quarter has not yet been entered, then
averaging by his formula divides the sum of three quarters by 4 - and that is
not the average for the first three quarters, it should be divided by 3 (like
the Average function does)

 

[T. Valko]

He called me a young, inexperienced, idiot woman

I think you have grounds for a sexual harassment lawsuit!

 

[Don Guillett]

So.... Ask him which he wants.

 

[Pepper]

Firstly, Biff I do have grounds, but am not that thin skinned. He is just
crotchedy older "gentleman".

Secondly, the sum is supposed to be annual-to-date (in my bosses words)
therefore the Average should be divided by the number of quarters that data
exists.

If we have 100, 200 and 250 for the first three quarters, then the total to
date is 550 and the average is 183.333.

Thank you all for your feedback.

 

[Gord Dibben]

Very good response.

The epitome of tact and diplomacy.


Gord Dibben MS Excel MVP

 

[JLatham]

First, you should ask him to differentiate between an "Idiot Woman" and an
"Idiot Man" - perhaps there is some basis in gender for a difference between
the two, although as far as I know, generally accepted IQ tests make no such
distinction.

Now, as to how the formula should work - I think Don Guillett's first post
in the thread covers it nicely - either you count zeros/missing entries as
zero with a division by 4 always, or you take the sum of non-zero entries and
divide by the number of non-zero entries to obtain a true average for data
that you actually have in hand.

As for the crotchedy older gentleman, please tell him for me that I
sincerely hope he is happily married at the moment, because with his
attitude, if he ain't already, he probably is never going to be . P.S. I'm
grumpy, I'm older, and hopefully most would consider me a gentleman; and I'd
never make such a statement to a member of the alternate gender [assuming
female is the alternative to male, without regard for other potential
alternatives ]. He would appear to be a moron, studying to become an
idiot, and failing miserably!

 

[Don Guillett]

Thanks for the kind words.
I like the part about the crotchety older gentleman which fits this grumpy
72 yr old.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

First, you should ask him to differentiate between an "Idiot Woman" and an
"Idiot Man" - perhaps there is some basis in gender for a difference
between
the two, although as far as I know, generally accepted IQ tests make no
such
distinction.

Now, as to how the formula should work - I think Don Guillett's first post
in the thread covers it nicely - either you count zeros/missing entries as
zero with a division by 4 always, or you take the sum of non-zero entries
and
divide by the number of non-zero entries to obtain a true average for data
that you actually have in hand.

As for the crotchedy older gentleman, please tell him for me that I
sincerely hope he is happily married at the moment, because with his
attitude, if he ain't already, he probably is never going to be . P.S.
I'm
grumpy, I'm older, and hopefully most would consider me a gentleman; and
I'd
never make such a statement to a member of the alternate gender [assuming
female is the alternative to male, without regard for other potential
alternatives ]. He would appear to be a moron, studying to become an
idiot, and failing miserably!

Firstly, Biff I do have grounds, but am not that thin skinned. He is
just
crotchedy older "gentleman".

Secondly, the sum is supposed to be annual-to-date (in my bosses words)
therefore the Average should be divided by the number of quarters that
data
exists.

If we have 100, 200 and 250 for the first three quarters, then the total
to
date is 550 and the average is 183.333.

Thank you all for your feedback.

 

[Sheeloo]

I could not resist adding to the discussion...

Well, he probably got two out of three right... you must be young (since you
did not dispute this) and inexperienced (arguing with old men and not sure
about your stand)

Also if not being an expert on some aspect of Excel makes one an idiot then
I would guess more than 95% of the world's population would qualify
(including me.)

If I were in your place I would have given him three numbers and asked him
to calculate the average on paper. I would have walked away if he divided by
four . If he divided by three then I would have asked him to enter the three
numbers on his spreadsheet and compare the result with his manual calucation.

No one should call any one else an idiot under any circumstances.

Now my two cents;
You were right and he was wrong.

HAPPY NEW YEAR.

PS: Can you guess my gender from my response? My age?

This reminds me of an incident when an young woman told an old man that he
did not have to open the door just because she was a woman. His response - "I
am doing it because I am a gentleman"

 

[JLatham]

BTW: I don't think anyone directly addressed the issue of a difference
between a cell with a zero in it and a cell than actually has not had any
entry (or formula) entered into it:
YES, Virginia, er Pepper, there IS a difference, and Excel treats empty
cells differently at times than cells with either a zero length string ( "" )
or a zero in them.

Here's one simple proof:
In cell A1 put "abc" (as abc)
skip over A2, never putting anything in it
in A3 put the formula = ""
in A4 type the value 42
now, in A5 put this formula: =CountA(A1:A4)
and the result should be 3 even though only cells A1 and A4 have a visible
value in them, the zero length string in A3 is counted, but the EMPTY cell at
A2 is not.

For that matter, put a 0 into A1, A3 and A4 and formula = COUNT(A1:A4) in A5
and again the result is 3, showing that an EMPTY cell is not treated as a
zero for this purpose.

There is a similar difference when programming in VBA, where we are given
specific tests for an empty cell vs one with a zero length string or a zero
value, as
IF IsEmpty(Range("A2")) Then MsgBox"Cell A2 is actually EMPTY"
and
if Range("A2")=Empty then MsgBox"Cell A2 is still actually EMPTY, not zero!"

 

[Don Guillett]

Not so for AVERAGE.

However, I fully subscribe to that and take it a step further
if(len(trim(a1))>0,1,2)

and I even use in code where vba trim is not as good as
if len(application.trim(range("a1")))>0 then

 

[JLatham]

No argument from me on any of that, with the AVERAGE() function being one of
the exceptions to the general rule.

 

[T. Valko]

AVERAGE doesn't *always* ignore text.

Try this:

A1 = 0
B2 = 5
C3 = 5

Average those cells that are <>0.

Here's one way that you might do it:

=AVERAGE(IF(A1=0,"",A1),IF(B2=0,"",B2),IF(C3=0,"",C3))

You'll get a #VALUE! error.

That evaluates to:

=AVERAGE("",B2,C3)

You would think the empty text string "" should be ignored since AVERAGE is
supposed to ignore text. Not exactly true!

Now try the formula like this:

=AVERAGE(IF(A1=0,{""},A1),IF(B2=0,{""},B2),IF(C3=0,{""},C3))

This returns the correct result.

The text is only ignored when it's *part of an array or a cell reference*.
For example:

A1 = =""
B2 = 5
C3 = 5

=AVERAGE(A1,B2,C3) returns the correct result.

In the above formula I've forced the empty text string to be an array using
the squiggly brackets { }.

So, you might think, yeah, well I'll just omit the empty text string and let
the formula return a boolean which AVERAGE is also supposed to ignore. Not
exactly true!

=AVERAGE(IF(A1<>0,A1),IF(B2<>0,B2),IF(C3<>0,C3))

That returns 3.3 and is incorrect. It evaluates to:

=AVERAGE(FALSE,B2,C3)

The same holds true for logical values, they're only ignored when they're
*part of an array or a cell reference*.

This is noted in the help for AVERAGE but it's not entirely clear that what
I've described above is what you'll run into if you're not careful.

 

[Shane Devenshire]

Hi JB,

She wasn't going to ask HR who was correct, she was going to file a sexual
harasement complaint.

 

[Shane Devenshire]

Hi Sheeloo,

Your numbers are wrong - "if not being an expert on some aspect of Excel
makes one an idiot then I would guess more than 95% of the world's population
would qualify" It is not 95% but 100%, even if you include every Excel
engineer at Microsoft.

Cheers, and happy new year.