Count odd/even numbers in range with difference of X

and what about 45 & 47 and 43 & 45?

@ Bishonen,

Hello Bishonen,

Although the numbers 45 & 47 and 43 & 45 are odd numbers, the difference between them is less than 4,
and sans only want to count odd and even numbers with a difference of 4.

lol - I'm blind.


thanks for the clarification.

Please Login or Register  to view this content.

@ Bishonen,

Hello Bishonen,

No problem, you are welcome.

And here I am trying to concoct some formula.LOL.

But WOW! what you came up is fantastic! Well done!

Dear Winon,

you're too flattering ^^ I'm pretty sure that the guru's around here could come up with a twice as efficient code twice as fast as I did. Thank you for the reputation as well. Let's just hope that OP will be satisfied with the code. And in the meantime :

Please Login or Register  to view this content.

Hello sans,

Thank you for clarifying things for us by supplying us with a sample WorkBook.

The attached WorkBook is the best solution that I can come up with. In Cell M1 You my enter any value as a difference, and the formulas will respond accordingly. To the right of your data I had to make use of helper columns which you may hide.

Hope that helps.

or you can add a couple lines to the last code:

Please Login or Register  to view this content.

Perhaps I'm mistaken once again, but it seems to me that your formula, Winon, compares only the adjacent cells. You would have to add 56 of such comparisons for it to work if I'm not mistaken.

@ Bishonen,

Hello Bishonen,

I tried your Code and the results don't look right. I am not sure if sans is actually comparing Pairs of Columns, for instance Column A & B, C & D, E and F, etc. Or should any two adjacent columns be compared?

@Winon,

Hi Winon!

As I'm not 100% sure either, I already mentioned that it might be that it's me who's wrong once again. The reason why I thought that we're looking for any match, not only adjacent cells is Sans' first post:

in range 20,24,37,[b]43,45,47[/b,51],89 (A1:H1),

there is 1 pair of even numbers with a difference of 4 ( numbers 20 & 24) and 2 pairs of Odd numbers with a difference of 4 (43 & 47) & (47 & 51). So the result would be 1 for Even and 2 for Odd

Sans states that 43 & 47 should be considered as a pair. Those numbers are separated by 45 however. By your logic, those shouldn't count but yet the end-result in OP's post is 2 for odd.

But as it's almost 2:00am over here, it might be that my mind is already asleep and I'm terribly wrong:D I'm going to take a nap and check out this thread tomorrow.

@ Bishonen,

Hello Bishonen,

We seem to be on the same time Zone. Also About 2:00 am over here as well. We will take this further tomorrow after a god nap. G'night.

Hello sans,

I could not "switch off" and go to sleep. I think I finally got it right.

I am pretty sure the attached WorkBook returns the answer you are looking for. If you would prefer it in VBA, I'm sure Bishonen could sort that out for you.

Hi sans,

You are welcome.

Glad we could help in some way.

but also found a solution to a formula I was trying to come up with when comparing only adjacent numbers for a difference of 4.

Mind sharing that solution to a formula with us?

Hi guys again,

I was wondering if the above macro can be turned into a UDF as I would like to receive the result for odd/even numbers with a difference of 4,5,6,7,8,9,10 etc. Is it possible to easily "convert" the macro into a UDF as this will save me the time of having to create/run multiple macros upon adding new data?

Many thanks

Here's the function for odds. As proof that you're not a lazy bum, its You who's gonna post the code for evens :-)

Please Login or Register  to view this content.

No UDF is needed, Native Excel functions will suffice, be faster and use fewer resources.

The CSE formula
=SUM((A1:H1-TRANSPOSE(A1:H1)=4)*(MOD(A1:H1,2)=0))
will return the value for even, while
=SUM((A1:H1-TRANSPOSE(A1:H1)=4)*(MOD(A1:H1,2)=1)) works for odd.

The 4 can be altered or replaced with a cell reference to suit your needs.

Both these formulas need to be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)