![]() |
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 |
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 |
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 |
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 |
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 |
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 |
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. |
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. |
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. |
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! |
Formula to identify decimal versus whole numbers
this is the answer lol. =CEILING(C10,1) |
Formula to identify decimal versus whole numbers
|
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. |
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! |
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. |
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 |
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!! |
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. |
All times are GMT +1. The time now is 09:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com