Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
I have the following formula to capture staff timekeeping hours, identified
by codes, e.g., "3", "3.1", "3.1.1", "3.1.1.1" which is formatted as TEXT (the time associated with each code is formatted as a number). {=SUM(IF('L:\Tracking\CY 04\[Myers CY 04.xls]Myers-Jan 04'!$B$2:$B$200="3.1",'L:\Tracking\CY 04\[Myers CY 04.xls]Myers-Jan 04'!$C$2:$C$200))} Problem: The formula is capturing all the data for codes which are three or more digits ("3.1.1") but not data where the codes are one ("3") or two digits ("3.1") Two other findings... 1. If I go to the worksheet and physically type the same number over the original, the data might (not always) show up, but a global search and replace of the numbers to a formatted text number doesn't work. 2. Even though the cells are formatted as TEXT, if I physically put an apostrophe in front of the one/two digit numbers, they will (always) show up. I greatly appreciate any advice on how to correct this or globally place an apostrophe in front of one/two digit numbers as I would really don't want to go through an entire year of data for each person in our office. |
#2
![]() |
|||
|
|||
![]()
On Thu, 2 Dec 2004 04:47:06 -0800, "Suzanne"
wrote: I have the following formula to capture staff timekeeping hours, identified by codes, e.g., "3", "3.1", "3.1.1", "3.1.1.1" which is formatted as TEXT (the time associated with each code is formatted as a number). {=SUM(IF('L:\Tracking\CY 04\[Myers CY 04.xls]Myers-Jan 04'!$B$2:$B$200="3.1",'L:\Tracking\CY 04\[Myers CY 04.xls]Myers-Jan 04'!$C$2:$C$200))} Problem: The formula is capturing all the data for codes which are three or more digits ("3.1.1") but not data where the codes are one ("3") or two digits ("3.1") Two other findings... 1. If I go to the worksheet and physically type the same number over the original, the data might (not always) show up, but a global search and replace of the numbers to a formatted text number doesn't work. 2. Even though the cells are formatted as TEXT, if I physically put an apostrophe in front of the one/two digit numbers, they will (always) show up. I greatly appreciate any advice on how to correct this or globally place an apostrophe in front of one/two digit numbers as I would really don't want to go through an entire year of data for each person in our office. It seems as if your formula is getting confused because some of the values in the ranges get interpreted as numbers rather than text. One way to force them to text would be to use the TEXT funtion in your formula. You can substitute for your ranges (rg) the formula: =TEXT(rg,"@") --ron |
#3
![]() |
|||
|
|||
![]()
Suzanne,
I don't fully understand your formula since it appears to be looking only for 3.1, but perhaps that's just an abbreviated example. I fooled around a little with this and found the following which may be helpful. First of all, 3 and 3.1 would both be recognized by Excel as numbers. That's probably why those values are causing you difficulty. If I do a replace 3.1 with '3.1, that seems to work. That would be in the data area. A "replace all" will start changing the formula and won't be helpful. Also, and I don't know if this is helpful or not, if I format one of those cells to text, then replace 3.1 with '3.1, and THEN replace it back to 3.1 I seem to get a text value that works. It appears that formatting the cell as text does not make it a text value for your purposes. However, if you then put the ' in front of it, and take it away, the cell then becomes text. Art "Suzanne" wrote: I have the following formula to capture staff timekeeping hours, identified by codes, e.g., "3", "3.1", "3.1.1", "3.1.1.1" which is formatted as TEXT (the time associated with each code is formatted as a number). {=SUM(IF('L:\Tracking\CY 04\[Myers CY 04.xls]Myers-Jan 04'!$B$2:$B$200="3.1",'L:\Tracking\CY 04\[Myers CY 04.xls]Myers-Jan 04'!$C$2:$C$200))} Problem: The formula is capturing all the data for codes which are three or more digits ("3.1.1") but not data where the codes are one ("3") or two digits ("3.1") Two other findings... 1. If I go to the worksheet and physically type the same number over the original, the data might (not always) show up, but a global search and replace of the numbers to a formatted text number doesn't work. 2. Even though the cells are formatted as TEXT, if I physically put an apostrophe in front of the one/two digit numbers, they will (always) show up. I greatly appreciate any advice on how to correct this or globally place an apostrophe in front of one/two digit numbers as I would really don't want to go through an entire year of data for each person in our office. |
#4
![]() |
|||
|
|||
![]() If you type in your numbers 3.1 and 3 and then formatted to text, excell still treats them like numbers. Check your formula again for "3" and then go to one of your number 3 entries and change the entry to '3 (apostrophe 3) Lance "Suzanne" wrote: I have the following formula to capture staff timekeeping hours, identified by codes, e.g., "3", "3.1", "3.1.1", "3.1.1.1" which is formatted as TEXT (the time associated with each code is formatted as a number). {=SUM(IF('L:\Tracking\CY 04\[Myers CY 04.xls]Myers-Jan 04'!$B$2:$B$200="3.1",'L:\Tracking\CY 04\[Myers CY 04.xls]Myers-Jan 04'!$C$2:$C$200))} Problem: The formula is capturing all the data for codes which are three or more digits ("3.1.1") but not data where the codes are one ("3") or two digits ("3.1") Two other findings... 1. If I go to the worksheet and physically type the same number over the original, the data might (not always) show up, but a global search and replace of the numbers to a formatted text number doesn't work. 2. Even though the cells are formatted as TEXT, if I physically put an apostrophe in front of the one/two digit numbers, they will (always) show up. I greatly appreciate any advice on how to correct this or globally place an apostrophe in front of one/two digit numbers as I would really don't want to go through an entire year of data for each person in our office. |
#5
![]() |
|||
|
|||
![]()
Thanks to all. This is a pretty important discovery since we often use
numbers as designators (not a value). Excel's inability to correctly process a number formatted as text is a problem I hope can be resolved in the future. In the meantime, looks like I'll have to double check future reports to ensure number designators, even when formatted as text, are not treated as a number. To correct the problem, I wrote a macro to put an apostrophe in front of each 1 and 2 digit code, which did resolve the problem (after finding out Excel won't allow wildcards in a global search/replace). The hour or so it took to write it will save me, rather my admin support, from going through thousands of line items for all of our staff. Thanks Again... Suzanne "LanceB" wrote: If you type in your numbers 3.1 and 3 and then formatted to text, excell still treats them like numbers. Check your formula again for "3" and then go to one of your number 3 entries and change the entry to '3 (apostrophe 3) Lance "Suzanne" wrote: I have the following formula to capture staff timekeeping hours, identified by codes, e.g., "3", "3.1", "3.1.1", "3.1.1.1" which is formatted as TEXT (the time associated with each code is formatted as a number). {=SUM(IF('L:\Tracking\CY 04\[Myers CY 04.xls]Myers-Jan 04'!$B$2:$B$200="3.1",'L:\Tracking\CY 04\[Myers CY 04.xls]Myers-Jan 04'!$C$2:$C$200))} Problem: The formula is capturing all the data for codes which are three or more digits ("3.1.1") but not data where the codes are one ("3") or two digits ("3.1") Two other findings... 1. If I go to the worksheet and physically type the same number over the original, the data might (not always) show up, but a global search and replace of the numbers to a formatted text number doesn't work. 2. Even though the cells are formatted as TEXT, if I physically put an apostrophe in front of the one/two digit numbers, they will (always) show up. I greatly appreciate any advice on how to correct this or globally place an apostrophe in front of one/two digit numbers as I would really don't want to go through an entire year of data for each person in our office. |
#6
![]() |
|||
|
|||
![]()
"Suzanne" wrote...
Thanks to all. This is a pretty important discovery since we often use numbers as designators (not a value). Excel's inability to correctly process a number formatted as text is a problem I hope can be resolved in the future. .... Don't hold your breath. All programming languages I know that provide automatic type conversion back and forth between text and numbers nevertheless enforce the distinction between the two, just like Excel. That is, "2" + "1" < "3", but "2" + "1" = 3 and ("2" + "1") & "" = "3". In this regard, Excel is working correctly acording to its own specs. It's just that you want it to work some other way. Given the backward compatibility nightmare if this changed to the way you want Excel to work, you're going to have to adapt to Excel's way of doing this because it's unlikely in the extreme it'll even do this the way you want it to. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Where is the bug in my array? | Excel Discussion (Misc queries) | |||
Working with array equations | Excel Discussion (Misc queries) | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) | |||
Change Path names in copied work book | Excel Worksheet Functions | |||
Keeping Array values? | Excel Worksheet Functions |