Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Function Pitfalls in Celll Formatting
Does anybody know if incorrect cell formatting can cause issues in functions?
That is do I need to be cognisent of incorrect formats when undertaking calculations? That is, I know that I can format a cell as text. If the cell in question is a number say "100". If I format this as text, it doesn't seem to cause any issues in funtions. I also know that if I put the wrong number of days in month (30/02/2005) or the wrong number of months (30/13/2005) Excel automatically refuses to turn this into a serial number and the contents of the cell is left justified to reflect that fact that Excel does not treat it as a valid date. And this will obvilously cause issues in Date Functions. However its not clear to me if its the format or simply the wrong days/months. I also know that in the UK, if I type in "£100", the £ dissapears immediately and the cell recieves the format #,##0_); (#,##0); "0"_). However this does not happen if I type in "$100". The entry is treated like text and is left justified. But this creates a #VALUE! error if included in a function. Is this because its formatted as text or because the functions cannot deal with the "$" character. I am also wondering this is a regional setting (ie if I were in the US would this occur if I used the $ instead). Thanks in Advance |
#2
|
|||
|
|||
"ExcelMonkey" wrote in message ... I also know that if I put the wrong number of days in month (30/02/2005) or the wrong number of months (30/13/2005) Excel automatically refuses to turn this into a serial number and the contents of the cell is left justified to reflect that fact that Excel does not treat it as a valid date. And this will obvilously cause issues in Date Functions. However its not clear to me if its the format or simply the wrong days/months. Because it is not a valid date, and it includes text characters, /, then Excel will default it to text. If you enter =DATE(2005,2,30) in a cell, Excel knows that is a date, so calculates the 30th day of Feb, the 2nd March. So, IMO, nothing is wrong per se, it is just the action Excel takes depending on the information provided. I also know that in the UK, if I type in "£100", the £ dissapears immediately and the cell recieves the format #,##0_); (#,##0); "0"_). However this does not happen if I type in "$100". The entry is treated like text and is left justified. But this creates a #VALUE! error if included in a function. Is this because its formatted as text or because the functions cannot deal with the "$" character. I am also wondering this is a regional setting (ie if I were in the US would this occur if I used the $ instead). This is caused by the regional settings on your machine. You have set it to English (United Kingdom), so £ is the default currency. If you change this to English (United States) you will see the opposite actions with that same data. Again, the $100 is treated as text as Excel does not know it is currency, and it has a text character. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
undefined function error when creating xls pivot from mdb qry | Excel Worksheet Functions | |||
Conditionial formatting for merged cells, function Len($A$4) | Excel Worksheet Functions | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) | |||
How can I use an IF function to look for specific cell formatting. | Excel Worksheet Functions |