Formula to identify decimal versus whole numbers

  • Thread starter Thread starter moily
  • Start date Start date
M

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
 
Thank you!
Click to expand...

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.
 
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.
 
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.
 
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!
 
I know this is over 5 years old, but I'm in a bit of a bind looking for help. Here is the situation.

I want to express numbers with certain rules at the .5 decimal range. For example, a number like 239.5 would round up to 240 while a number like 238.5would round down to 238. It would go to the nearest even number based on what the digit previously to it is (even or odd). Otherwise the system wouldround according to normal rules. I know its a big IF statement but the only part I've been able to code correctly is.

=IF(LOGIC,Roundup(#,0),Rounddown(#,0))

I'm new to coding with excel, so any help would be appriciated.
 
If you will be dealing only with zero or positive rounding "digits to theright of the decimal", then you can write a simple User Defined Function since the VBA Round Function does what you want.



However, you may also be interested in http://support.microsoft.com/kb/196652 which discusses implementation of various types of rounding in Excel.



For the Bankers Rounding algorithm:



To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.

Ensure your project is highlighted in the Project Explorer window.

Then, from the top menu, select Insert/Module and

paste the code below into the window that opens.



To use this User Defined Function (UDF), enter a formula like



=BRound(A1,0)



in some cell.



=====================================

Option Explicit

Function BRound(Num As Double, Optional NumDecPlaces As Long = 0) As Double

BRound = Round(Num, NumDecPlaces)

End Function

=================================
Click to expand...

Sir you are awesome thank you!
 
Back
Top