Countif function for Even and Odd numbers

[T. Valko]

Biff (T. Valko) can defend himself. Besides, I didn't think

I was being condescending to him. I may have been
pedantic and overengineering, but not condescending.

What do I need to defend myself from?

I don't think Harlan was being condescending towards me in his reply. He
simply noted some possible valid issues and I do the same at times.

However, I stand by my suggested formulas based on the information provided
by the OP. It's also true, as Harlan noted, that posters often oversimplify
their requests and this can lead to numerous follow-ups but I don't mind
follow-ups.

I'm all for *efficient robustness* but at some point unnecessary robustness
crosses the line into overkill. If we wanted to take this example to the
extreme none of us accounted for the possibility of there being text entries
in the range. Reading the OP one would not be led to believe that there were
text entries so accounting for that when not needed woud be overkill.

 

[John C]

Your right Biff, and I apologize to you. My ire gets up when I see something
like his post. The point I took offense to was this:

=SUMPRODUCT(--(MOD(A1:A10,2)>0))
....
This isn't.

Your formula worked perfectly fine according to the OP specs, and with the
sample data showing only whole numbers (no blanks, decimals, or text), your
formula is OK.
I think I would have remained completely silent if instead he had said
something like:
This could be improved by. But to say flat out that your formula was not
going to do it, well, just got to me, far more than it should have.
I respect both your knowledge, and Harlan's knowledge, and as I stated
several times, you 2 have been on here for a long time, and have helped
countless people along the line. For that, I commend you both. There are
times, in my opinion, though that a responder oversteps the bounds of
courtesy, and telling people they are stupid, (or in this case, telling you
that you are wrong, when in fact you aren't), and I get tired of it.

 

[Harlan Grove]

If a column is set up with error trapping so that it only contains integers,

....

You haven't written many workbooks for other people to use.

Set up whatever data validation rules you want to ENSURE only integers
are valid entries, type (but don't press enter) 0.125, press [F2],
[Shift]+[Home], [Ctrl]+C, [Esc], [Ctrl]+V.

Try to use event handlers - open the workbook with macros disabled.

 

[John C]

...You haven't written many workbooks for other people to use....

I have. It is why I automate processes that would otherwise require
copy/paste, or use macros to define the copy/paste. It is why I create macros
with my digital signature, and then I ensure my digital signature is in the
user's trusted sources.

 

[Jezzy]

Hi All,

I am sorry if I cause an uproar here. It's my first time in this forum. I
also have very basic knowledge of Excel. I am self-employed and trying to
get myself familiar with the program. Maybe in future if I come across any
difficulties I will provide more details and proper questions. My apology.
I do appreciate the generous help given by all you people.

 

[T. Valko]

You did not cause an uproar and there is no need to apologize!

This forum is here to provide peer to peer help. It seems that you got more
help than you probably expected but the bottom line is that your question
was answered and it was answered correctly. The thing to understand about
Excel is that there are almost always *many* different ways to do something
and if you get exposed to more than one of those ways then you're able to
evaluate each and choose which is best for your application.

Please don't hesitate to ask questions in the future.

 

[Harlan Grove]

. . . It is why I automate processes that would otherwise require
copy/paste, or use macros to define the copy/paste. It is why I create macros
with my digital signature, and then I ensure my digital signature is in the
user's trusted sources.

And how many OPs do you suppose do this?

Maybe you could spend hours implementing data transfer and validation
procedures that would render it possible to use =SUMPRODUCT(--(MOD(x,
2)>0)) to return the correct count of odd numbers. But unless there
were other reasons for it, I'd save the time and effort and use
=SUMPRODUCT(--(MOD(x,2)=1)) and get the same result.

 

[Harlan Grove]

I am sorry if I cause an uproar here. . . .

You didn't. Please don't believe that you caused the fuss. It was a
tempest among the respondents only. That said, more details never hurt.

 

[John C]

You go your way, I'll go my way. Biff's answer was a good solution, if you
don't like it, that's on you.

In regards to your comment, I notice you keep flip-flopping back and forth.
One moment, you criticize the formula, then you criticize the question, then
you criticize me(...You haven't written many workbooks for other people to
use...). Make up your mind which it is going to be.

And, as has been pointed out extensively throughout the community, no matter
what protections, error trapping, passwords, redundant calculations, etc. are
done in a workbook, any user can break anything. So I ask you, why bother
with error trapping at all?

 

[John C]

I concur with both Harlan and Biff. Do not feel blamed, or feel sorry for it.
Use the forum extensively. Both Harlan and Biff are very knowledgeable
responders, and there are many other responders who have a vast wealth of
knowledge who are here to help others. Never hesitate to use the community.

 

[Harlan Grove]

And, as has been pointed out extensively throughout the community, no matter
what protections, error trapping, passwords, redundant calculations, etc. are
done in a workbook, any user can break anything. So I ask you, why bother
with error trapping at all?

You're the one who suggested validation checking, which is an implicit
requirement when using the formula

=SUMPRODUCT(--(MOD(x,2)>0))

I didn't suggest anything other than using a formula that ALWAYS
returns the count of odd numbers no matter what the cell contents may
be (unless the cells evaluate to error values). The formula I
proposed,

=SUMPRODUCT(--(MOD(x,2)=1))

doesn't need error trapping. It always works unless there are error
values or numbers with integer parts greater than 2^28-1. Handling
that last case requires the other formula I gave,

=SUMPRODUCT(--(x-2*INT(x/2)=1))

This truly requires no error trapping and/or data validation, though
it propagates error values in x. IOW, it'll either work (return the
count of odd numbers in x) or return an error value. It'll NEVER
return a wrong numeric result no matter how users torture or misuse
the workbook. Since you claim to know something about spreadsheet
development, this level of robustness isn't useful? You do understand
the meanings of the words 'robust' and 'robustness', don't you?

You may believe I've been flip-flopping, but only because I've been
responding to all the nonsense and blather you've been spewing. In
order, Biff's formula could be improved upon, you don't understand the
concept of robustness, and you're willing to wander off into pastures
like this 'If a column is set up with error trapping so that it only
contains integers'. Assume there's no data problems, and the universe
of workable formulas may increase, but it begs the question whether
it's better to assume perfect data or dirty data. I'll assume the
latter.

And evidently the fact that I thought Biff's formula could be improved
upon was/is 'on you'. That or your continued responses would serve as
proof of your irrationality. So which is it: you're continuing this
argument because, contrary to your assertions, this does matter to you
(but you'll continue to refuse either to comprehend the robustness
issue or acknowledge comprehending it) or because you're irrational?

 

[John C]

I am quite literate, and know the words of robust, and robustness.
Ooops, I deleted your formula, now it gives no answer.
But, according to you
....It'll NEVER return a wrong numeric result no matter how users torture or
misuse
the workbook...

And of course, the best one you said:
....It always works unless ...

Well, another flip flop

....This isn't (OK up to MOD's standars)... referring to Biff's original
formula
....And evidently the fact that I thought Biff's formula could be improved
upon...

You first state that the formula isn't OK. Which, in fact, it is OK. I agree
that it isn't as robust as other solutions, but it IS OK. Now, you say that
the formula could be improved upon. My whole reason of responding, the entire
thread, was kicked off by this tidbit. If you had stated, correctly, that the
formulas could be improved upon, hey, more power to you, perfectly
understandable. But to say the formula didn't work / meet the OPs required
needs was obviously wrong on 'your' part. No, not irrational, and I notice
you keep posting to this one as well, so obviously it matters somewhat to you.

 

[T. Valko]

How about leaving me out of this?

 

[T. Valko]

How about leaving me out of this?

 

[Harlan Grove]

I am quite literate, and know the words of robust, and robustness.
Ooops, I deleted your formula, now it gives no answer.
But, according to you
...It'll NEVER return a wrong numeric result no matter how users torture or misuse
the workbook...

Yup, so it doesn't give the WRONG answer. Apparently the concept that
NO answer is equal to NEITHER a right answer nor a wrong answer has
yet to enter your oh, so literate mind. Literate perhaps, but still
ignorant.

To repeat in the vain hope of penetrating your simple mind, no answer
isn't a wrong answer. It's no answer.

And of course, the best one you said:
...It always works unless ...

Well, another flip flop

Ah, logic! Rather the lack of it.

You're now explicitly resorting to 3-year-old's arguments. I could say
the sun will rise tomorrow, and you will say either that the sun could
explode or the earth stop rotating before that. Do I really need to
preface my statement that IF ANY OF US SURVIVE TO TEST THIS, the sun
will rise tomorrow? Knowing you, you'd then posit the earth
transported into some region between galaxies with billions of alien
spaceships pointing heat lamps at earth in order to construct a
scenario in which some of us do survive but the sun doesn't rise.

I wonder what you'll come up with next.

...This isn't (OK up to MOD's standars)...

OK, if you require it, up to MOD's standards *AND* assuming users
don't deliberately or accidentally clear or delete cells containing
formulas, or change their system fonts, or delete necessary DLLs, or
corrupt their registries, or remove necessary subsystems like video
cards/chips, or damage hardware, or gouge out their eyes or chop off
their fingers.

You first state that the formula isn't OK. Which, in fact, it is OK. I agree
that it isn't as robust as other solutions, but it IS OK. . . .

It's OK if the range contains no numbers with fractional parts.
Probably OK if the data in the range comes from an outside system that
only records integers. Less likely OK if the range contains formulas.

Now, you say that the formula could be improved upon. . . .

As in made more general - yes. It could be improved upon by being made
more general. OTOH, someone else who will remain nameless improved
upon it under the assumptions the range would only contain integers,
namely,

=SUMPRODUCT(MOD(x,2))

. . . But to say the formula didn't work / meet the OPs required
needs was obviously wrong on 'your' part. . . .

The original formula works when all entries are integers. That
satisfies the OP's specs. However, as I mentioned before, newsgroup
archives serve as FAQs, and the original formula doesn't work in
general. Neither did the other formula, count of even numbers, if
there could be text or blank cells in the range.

So if the range in question consisted of integers only < 2^28, then
both original formulas in this thread give the correct results. But if
there were anything else in the range, both formulas could return
incorrect numeric results. Generalizing the count of even numbers was
dealt with in a different branch, so I didn't mention it.

you keep posting to this one as well, so obviously it matters somewhat to you.

Unlike you, I wasn't foolish enough to state, 'and that's pretty much
all I'm gonna waste on this'. You lack self-discipline too? Or just
enjoy affecting insincere faux-principled positions?