MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Finding integers


Posted by Ian G on August 07, 2001 10:16 PM

I have a column of several thousand numbers and I need to be able to identify which are integers. I thought Excel had an 'ISINTEGER'function, which would be just the thing, but there doesn't appear to be one. The solution doesn't need to be pretty - just a formula I could copy down in the column next to my numbers would be fine. Any advice?
Thanks


Posted by Aladin Akyurek on August 08, 2001 12:04 AM

Ian,

Here is one:

=(A1-INT(A1)=0)+0

or just

=A1-INT(A1)=0

Aladin

Posted by Ian G on August 08, 2001 12:34 AM

...that you provided two versions depending on whether my column was formatted as text or numbers? Out of curiosity I tried both and although I can make sense of both (1 is as good as TRUE for my purposes) I was wondering how the "+0" works. I have seen it used before on this page but have never really understood it. how does it force a numeric return instead of text - what's the logic to it?
Thanks
Ian

Posted by Aladin Akyurek on August 08, 2001 12:55 AM

+0

+0 (or *1) coerces/forces Excel to treat logical values, text formatted dates and numbers as numbers (that is, as values whose data types are numeric).

Aladin

=========


Posted by Mark W. on August 08, 2001 2:16 PM

Another solution...

=MOD(A1,1)=0

Posted by Aladin Akyurek on August 08, 2001 2:38 PM

Yep: "the Obvious"!

Posted by Ian G on August 08, 2001 4:17 PM

Re: Yep: "the Obvious"! - not obvious to me, but william of occam would be proud. thanks mark

Posted by Mark W. on August 08, 2001 4:31 PM

Ahh, Is this a subtle reference to Occam's razor?


Posted by Ian G on August 08, 2001 4:56 PM

yep . well, maybe not so subtle, but...

...I think he'd be impressed with the elegance and parsimony of many of the solutions on this page. As someone who has been responsible for some pretty ugly and unnecessarily convoluted formulae I know I am.

Posted by Aladin Akyurek on August 08, 2001 5:12 PM

Yes, it is...

although that Razor only comes into play when one has 2 or more answers/explanations from which to choose. So Ian should use it! But, what is the criteria that you use to select one answer/explanation over other candiatate answers/explanations? As the (philosophy of) science has it: Select the *simplest*, whence all the difficulties in some sciences.

Ian: I double-quoted "Obvious," in just some situations the simplest answers/explanations, while there, do not tend to fire up the first round (of elaborations/deliberations) while there.

Posted by Ian G on August 08, 2001 7:35 PM

Re: Yes, it is...

you're right - applying the law of parsimony isn't possible with only one option, however simple and elegant it may be. so your response was a good solution on two counts - it gave me an effective solution, and led to an even simpler one. gotta love this page!