Returning An Integer From A List Of Fractional Numbers

I have searched relentlessly and, incredibly, have not been able to solve what seems to be an almost childish formula dilemma. To keep it simple:

A column of numbers all having fractional values, example:

D
1 13.8
2 3.3
3 24.1
4 13.2
5 16.1
6 28.1
7 16.2


The list can be phrased either as D1:D7 or as a defined table.

I want to return a true statement if the integer: 13, only (no fractional values), is found in this list. Of course, it can be seen to appear twice, but once is enough to do the job.

I've tried applying TRUNC and INT functions to various convolutions of MATCH and INDEX functions, but no success.

=NOT(ISERROR(MATCH(13,D1:D7,0)))

excel-2007

2 Answers
2

You can use the following formula:

=and(countifs(a:a, ">=13", a:a, "<14"))
'for non-countifs versions
=sumproduct((d1:d7>=13)*(d1:d7<14))


While AND is typically used to combine boolean operations, here it is used to convert any non-zero count to TRUE and a zero count to FALSE.

You can see how this plays out for individual items in the following graphic, where each b cell only looks at its equivalent a cell:

b

a

Try the following array formula. Once you type it and you want to enter it, you have to do Ctrl-Shift-Enter.

=OR (INT(D1:D7)=13)

If you are doing negative numbers, you should change INT for TRUNC.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.