Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to identify decimals??
Thanks in advance for any help you all provide!! I am trying to come up with a function that will identify if a cell has a decimal in it. My staff will be entering budgetary amounts into a spreadsheet and I want the amounts to be in whole dollars. I want to be able to use this function with conditional formatting to identify the decimal (error) when they enter it. does that make sense? -- Celt ------------------------------------------------------------------------ Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413 View this thread: http://www.excelforum.com/showthread...hreadid=513350 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to identify decimals??
For your Conditional Formatting formula use this:
=MOD(A1,1)<0 You could also use Data Validation to prevent entry of these numbers to begin with. This would allow you to customize an error message so the user would know why the value is invalid. Using this formula: =MOD(A1,1)=0 HTH, Elkar "Celt" wrote: Thanks in advance for any help you all provide!! I am trying to come up with a function that will identify if a cell has a decimal in it. My staff will be entering budgetary amounts into a spreadsheet and I want the amounts to be in whole dollars. I want to be able to use this function with conditional formatting to identify the decimal (error) when they enter it. does that make sense? -- Celt ------------------------------------------------------------------------ Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413 View this thread: http://www.excelforum.com/showthread...hreadid=513350 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to identify decimals??
Assuming you want to check column A, try this formula somewhere else:
=IF(A1=INT(A1),"no decimals","decimals") and copy down. Of course, you could change the first message to "" (i.e. blank) so that the other message stands out as you scroll down the page. If you don't expect many of these you can apply a filter to the column with the formula in to just display the cells with decimals. With conditional formatting you could change the foreground or background colour (or both) for further emphasis. Hope this helps. Pete |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to identify decimals??
Worked great!!! Thanks! -- Celt ------------------------------------------------------------------------ Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413 View this thread: http://www.excelforum.com/showthread...hreadid=513350 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to identify decimals??
That was a few days ago, but thanks for the feedback.
Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
Identify month in a period and run a formula | Excel Discussion (Misc queries) | |||
Formula to identify cell with different value in list | Excel Worksheet Functions | |||
Formula to identify what was NOT found | Excel Worksheet Functions | |||
How to identify an inserted picture within a formula | Excel Discussion (Misc queries) |