CODEWITHSUNDEEP
excelvbauser-defined-functions
Shah
Shah

Reputation: 11

Finding Next Odd or Next Even Value in a list of numbers

Given a list of numbers I am trying to find the next odd or even number from the list given a specified number. The specified number doesn’t have to be in the range. For ex:

I am given number 406, I am suppose to find the next even or odd value from it from the given list of numbers:

380
381
382
383
385
400
401
403
407
410
411
0
0
0
0

In this case the next even returned should be 410 and the next odd returned should be 407.

In the case that specified number is in that list then it should return the next even or odd number from it.

I am doing this in excel and trying to create a UDF that can perform this task but nothing to any avail. Any help would be greatly appreciated.

Upvotes: 1

Views: 138

Answers (1)

BigBen
BigBen

Reputation: 50007

There's no need for VBA here:

If your version of Excel supports FILTER, for even use:

=@FILTER(A1:A15,(A1:A15>406)*(MOD(A1:A15,2)=0))

and change the 0 to 1 for odd.

If you don't have FILTER, then for even:

=AGGREGATE(15,7,A1:A15/((A1:A15>C1)*(MOD(A1:A15,2)=0)),1)

where the comparison number is stored in C1. Similarly change 0 to 1 for odd.

Upvotes: 2

Related Questions