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.

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

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.

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.

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.

"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.