CODEWITHSUNDEEP
excelexcel-formulainteger
Guy Thatsneaky
Guy Thatsneaky

Reputation: 13

How to create a formula in excel to check a range for integers

As stated I want to create a formula in excel to check a range of cells if any of them are not integers.

I have a range of cells, B2:J2, and I want to put a formula in a separate box that will display "True" if all the cells are integers, and "False" if not.

I have tried using INT function but was unable to apply it to the range. I also created a nested if statement based on this formula:

=IF(INT(J2)=J2,TRUE,FALSE)

I can't post the entire formula from my phone but the formula got very long. It seems to work but I was curious to see if there was a better way to do it. Thanks in advance.

Upvotes: 1

Views: 478

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34220

Also

=SUMPRODUCT(MOD(B2:J2,1))=0

The objection here is that it might not work for negative numbers. In fact it does, because the MOD function returns any fraction part as a positive number even if the number is negative.

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152505

Use SUMPRODUCT to iterate the range and count:

=SUMPRODUCT(--(INT(B1:J1)<>B1:J1))=0

enter image description here

Upvotes: 1

Related Questions