Formula to identify decimal versus whole numbers

[moily]

Hello,
I'm using Excel 2003 and am interested in knowing a forumla that can
identify decimal numbers versus whole numbers. I need to create an if
forumla that says YES if it's a decimal (ends in .5) and NO if it's a whole
number. Details below:

I have a cell that can be input with only whole numbers
(1.00,2.00,3.00,4.00,5.00, to max of 31) or numbers that end in .5 (0.50,
1.50, 2.50, 3.50, 4.50, 5.50, to max of 30.5) but the decimals will always
only end in .5 (no other decimal points).

I've had difficulty using wildcards with numbers: ie:
if(a1="*"&.5,"YES","NO") or if(a1="*"&".5","YES","NO"). One of the problems
is that Excel automatically puts a 0 in front of the decimal point if it's
not in quotes but it could be any number up to 30 in front of the decimal
point. However, I can't put it in quotes I assume because it's a number, not
text (I've tried it anyway and it still doesn't work).

Thanks in advance for any help!

Best,
Ann

 

[Dave Peterson]

=if(a1=int(a1),"whole Number","not whole number")

 

[David Biddulph]

=IF(MOD(A1,1),"YES","NO")

 

[moily]

Perfect!

=if(a1=int(a1),"whole Number","not whole number")

 

[moily]

Perfect too!

=IF(MOD(A1,1),"YES","NO")

 

[jpallen86]

Thank you!

=if(a1=int(a1),"whole Number","not whole number")

 

[joeu2004]

Thank you!

I'm sure Dave will be happy to hear that 5(!) years later.

But FYI, in general, MOD(A1,1)=0 or INT(A1)-A1=0 is more reliable than
INT(A1)=A1.

If we type the number into A1, INT(A1)=A1 might suffice.

But if A1 is calculated (i.e. a formula), INT(A1)=A1 sometimes returns TRUE
incorrectly.

For example, enter the following formula into A1: =12.9999999999999 +
5E-14.

Excel displays 13.0000000000000 no matter how many decimal places we
specify. And INT(A1)=A1 returns TRUE.

But A1 is not really exactly 13.

Note that MATCH(INT(A1),A1,0) returns a #N/A error, indicating no match.

Moreover, MOD(A1,1)=0 and INT(A1)-A1=0 return FALSE, indicating that A1 is
not an exact integer.

The reason why INT(A1)=A1 mistakenly returns TRUE is complicated to explain.

First, INT(A1) does not always truncate the value, as we might expect. (In
contrast, compare with VBA Int(Range("A1")).) Instead, INT(A1) seems to
first round to 15 significant digits, then truncate.

Second, INT(A1)=A1 returns TRUE because Excel deems INT(A1) to be "close
enough" to A1, presumably an extension of the dubious heuristic poorly
described under the misleading title "Example When a Value Reaches Zero" at
http://support.microsoft.com/kb/78113.

The inconsistent implementation of that heuristic leads to seeming
contradiction like INT(A1)-A1 is exactly zero, but INT(A1)-A1-0 is not.
Presumably, that causes similar contractions like INT(A1)=A1 is TRUE, but
INT(A1)-A1=0 is FALSE.

 

[jake.cooper.94]

I know this is 5 years later, but it's still at the top of the Google search results list for "Excel whole number".

So here's a simple solution to your problem in particular.
Using nested IF's and the INT function, I was able to make a formula that returns "Whole Number" for whole numbers, "Half Number" for anything ending in .5, and "Other Decimal" for any number that doesn't fit into the first 2 catagories.

=IF(INT(A1)=A1,"Whole Number",IF(INT(A1)+0.5=A1,"Half Number","Other Decimal"))


A general solution to "Is A1 a whole number"

=IF(INT(A1)=A1,,)

This returns TRUE, or FALSE.

 

[jake.cooper.94]

I know this is 5 years later, but it's still at the top of the Google search results list for "Excel whole number".

So here's a simple solution to your problem in particular.
Using nested IF's and the INT function, I was able to make a formula that returns "Whole Number" for whole numbers, "Half Number" for anything ending in .5, and "Other Decimal" for any number that doesn't fit into the first 2 catagories.

=IF(INT(A1)=A1,"Whole Number",IF(INT(A1)+0.5=A1,"Half Number","Other Decimal"))


A general solution to "Is A1 a whole number"

=IF(INT(A1)=A1,,)

This returns TRUE, or FALSE.

 

[themurph555]

Hi guys

Im trying to do an IF formula that determines whether its a decimal or not.

If it IS i want to use this formala =ROUNDUP(F10/1,0)*1 so that anything above 1 = 2.

e.g if the value is 1.01+ i need the value to be 2. if its 2.01 i need it to be 3.

If its NOT a decimal then i do NOT want this formula to apply.

is there any way to do this?

Cheers!

 

[themurph555]

this is the answer lol.

=CEILING(C10,1)

 

[Ron Rosenfeld]

Hi guys

Im trying to do an IF formula that determines whether its a decimal or not.

If it IS i want to use this formala =ROUNDUP(F10/1,0)*1 so that anything above 1 = 2.

e.g if the value is 1.01+ i need the value to be 2. if its 2.01 i need it to be 3.

If its NOT a decimal then i do NOT want this formula to apply.

is there any way to do this?

Cheers!

=ROUNDUP(F10,0) should also do what you want.