Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default Formula to identify decimal versus whole numbers

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Formula to identify decimal versus whole numbers

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



moily wrote:

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
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default Formula to identify decimal versus whole numbers

=IF(MOD(A1,1),"YES","NO")
--
David Biddulph

"moily" wrote in message
...
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



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default Formula to identify decimal versus whole numbers

Perfect!

"Dave Peterson" wrote:

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



moily wrote:

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

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default Formula to identify decimal versus whole numbers

Perfect too!

"David Biddulph" wrote:

=IF(MOD(A1,1),"YES","NO")
--
David Biddulph

"moily" wrote in message
...
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






  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Formula to identify decimal versus whole numbers

Thank you!

On Thursday, May 1, 2008 7:45:04 AM UTC-4, Dave Peterson wrote:
=if(a1=int(a1),"whole Number","not whole number")



moily wrote:

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


  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 829
Default Formula to identify decimal versus whole numbers

wrote:
On Thursday, May 1, 2008 7:45:04 AM UTC-4, Dave Peterson wrote:
=if(a1=int(a1),"whole Number","not whole number")


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.

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Formula to identify decimal versus whole numbers

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.
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Formula to identify decimal versus whole numbers

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.
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Formula to identify decimal versus whole numbers

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!


  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Formula to identify decimal versus whole numbers


this is the answer lol.

=CEILING(C10,1)

  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Formula to identify decimal versus whole numbers

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.5 would 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 would round 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.
  #14   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,045
Default Formula to identify decimal versus whole numbers

On Wed, 16 Jul 2014 16:51:42 -0700 (PDT), wrote:

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.5 would 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 would round 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 the right 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
=================================
  #15   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Formula to identify decimal versus whole numbers

On Wednesday, July 16, 2014 8:45:01 PM UTC-4, Ron Rosenfeld wrote:
On Wed, 16 Jul 2014 16:51:42 -0700 (PDT), wrote:



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.5 would 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 would round 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 the right 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

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


Sir you are awesome thank you!


  #16   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,045
Default Formula to identify decimal versus whole numbers

On Thu, 17 Jul 2014 06:21:52 -0700 (PDT), wrote:

Sir you are awesome thank you!


Glad to help. Thanks for the feedback.
  #17   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Formula to identify decimal versus whole numbers

sexta-feira, 7 de Fevereiro de 2014 Ã*s 11:03:31 UTC, escreveu:
this is the answer lol.

=CEILING(C10,1)


thanks
  #18   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Formula to identify decimal versus whole numbers

I would like to offer some kudos for the CEILING answer. I know this is an ancient thread but I thought I would pass on some performance testing.

I posed the following challenge to some Excel experts:

Suppose data dumped from a recordset into Excel resulted in Excel recording all dates and date-times, as numbers. Dates are whole numbers, DateTimes *might* be whole numbers, but generally are decimal numbers. And suppose testing the field type, all you got was a value of 7 - which means a general date. Your "mission" is to offer a formula I can use on the entire column of data so that if I get a TRUE from that formula, it tells me I need to give the column a DateTime numberformat, and if False, which indicates all whole numbers, I can then use simply a Date format.

I chose a formula that used the CEILING example offered in this thread. I got 4 other offered formulas. Here are all 5 formulas:


All of the formulas offered require array entry except the last one
=SUM(IFERROR(CEILING(A:A,1),0))SUM(A:A)
=SUM(N(MOD(IF(ISNUMBER(A:A),A:A,0),1)0))
=SUM(IFERROR(FIND(""."",A:A),0))0
=SUM(IFERROR(MOD(A:A,1),))0
=COUNT(INDEX(1/MOD(A:A,1),))

So on the worksheet, the last formula would likely be the fastest in terms of Excel's calculation engine. But consider that I was using VBA for this operation, and the EVALUATE method. That method
(1) assumes array-entered formula in its computation
(2) works on the true FULL column, whereas the formulas above, if entered on the worksheet, would be much faster because Excel's own calculation engine knows to ignore unused worksheet rows in the computation.


Here are the results in my benchmarking tests:

Remember, if the formulas resulted in TRUE, then the data was DateTime, and I did this on a range in col A that had a header and some dates, and there were non-whole numbers therefore DateTime is the proper output; and I ran each computation 100 times in a loop:




Result: DateTime Time taken: 49.593 seconds for 100 calculations using =SUM(N(MOD(IF(ISNUMBER(A:A),A:A,0),1)0))

Result: DateTime Time taken: 31.262 seconds for 100 calculations using =COUNT(INDEX(1/MOD(A:A,1),))

Result: DateTime Time taken: 52.058 seconds for 100 calculations using =SUM(IFERROR(FIND(".",A:A),0))0

Result: DateTime Time taken: 30.576 seconds for 100 calculations using =SUM(IFERROR(MOD(A:A,1),))0

Result: DateTime Time taken: 28.907 seconds for 100 calculations using =SUM(IFERROR(CEILING(A:A,1),0))SUM(A:A)

As this benchmarking showed, the method with CEILING was a little faster than all the other methods, even though it used TWO SUMMATIONS!!!

WAY TO GO!!

  #19   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Formula to identify decimal versus whole numbers

Alternatively (and this may be a new feature), if you select "Automatic" under formatting it will achieve the same result.

Ask me how dumb I feel that I just found that out after trying all these formulas.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Subtracting two 2-decimal place numbers gives result 13-decimal places? [email protected] Excel Worksheet Functions 5 March 12th 07 11:38 PM
identify numbers and text differently in formula widman Excel Discussion (Misc queries) 3 November 14th 06 05:28 PM
How can I sort mostly 3 decimal with some 4 decimal numbers PeterM Excel Discussion (Misc queries) 4 August 16th 06 02:15 AM
Formula without using numbers after decimal in the answer Mustang Excel Discussion (Misc queries) 5 September 16th 05 04:35 PM
Identify Numbers Mandeep Dhami Excel Worksheet Functions 5 July 17th 05 11:32 AM


All times are GMT +1. The time now is 04:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"