There are with them all Biff, but it would be nice if ...

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"T. Valko" wrote in message
...
The one that annoys me most is...WEEKNUM

For me, it's RANK (athough there are work-arounds).

Biff

"Bob Phillips" wrote in message
...

"Rick Rothstein (MVP - VB)" wrote in
message ...
=SUMPRODUCT(MOD(A1:A6,2),A1:A6)

That is for odd numbers... I guess you can use this for even
numbers...

=SUMPRODUCT(1-MOD(A1:A6,2),A1:A6)

Test it, more intuitive

=SUMPRODUCT(--(MOD(A1:A6,2)=0),A1:A6)

More intuitive? Well, marginally (at least for me). Since I had no
trouble seeing that Bernd's MOD(A1:A6,2) yields 1 when the processed
cell is odd and 0 when it is even, I find no difficulty seeing that
subtracting these values (0 or 1) from 1 reverses the values (they
become 1 or 0 respectively) and, hence, their odd/even-ness (it is
nothing more than the principal of toggling a value between 0 and 1
inside a program where the code line would be Value=1-Value).

But what if it were numbers divisible by 3? Try constructing the formula
with your functions, it ain't easy.

But with mine, it is simply

=SUMPRODUCT(--(MOD(A1:A6,3)=0),A1:A6)

Intuitive to get from one to another.



Pity about ISEVEN

I am newly returned to Excel after a very long absence and am puzzled by
this. Why is it that some functions (for example, MOD) can use array
ranges in this way and others (like ISEVEN) can't? Is there some "rule"
governing which function can and cannot?


I don't know for sure, but I guess it is just because that Excel is such
a big program that different parts were developed by different teams. One
team developed all of their functions to return arrays, one didn't. The
one that annoys me most is WEEKDAY/WEEKNUM. WEEKDAY does, WEEKNUM
doesn't.