Excel mistakes cell formats

[Lucio]

Hi,
With certain forumlas, putting them in a cell formatted with a
«General» format (and all the cells around this cell have a General
format too), this cell trasfoms its format to Text. The result has a
left align, and if I edit the forumla and reenter without modifies, in
the cell appair the text of the forumla instead the value.

I don't know if it's a localization bug (I have Excel in Italian), but
the follower formula:
=COUNTIF(Sheet1!$A$13:$A$147,Sheet2!a13)+E4-1
has the bug, while the follower:
=(COUNTIF(Sheet1!$A$13:$A$147,Sheet2!a13)+E4)-1
hasn't

 

[Dave Peterson]

Saved from a previous post.

Excel likes to help.

Try this on a test worksheet.
Select A1 and hit ctrl-; (to put the date in the cell)
now select B1 and type: =a1

Notice that excel changed the format of B1 to match the format in A1.

Now format D1 as Text.
put ASDF in D1
put =D1 in E1
You see ASDF.

With E1 selected, hit the F2 key and then enter (to pretend that you're changing
the formula).

Excel has "helped" you by changing that cell's format to text.

I don't know of any way of changing this behavior.

I just select the cell, and reformat it to General (or whatever I wanted). I
hit F2 and then enter (to reenter that formula).

Sometimes this feature is nice, sometimes it ain't.

 

[GS]

Lucio submitted this idea :

=(COUNTIF(Sheet1!$A$13:$A$147,Sheet2!a13)+E4)-1

IMO, this is the correct way to enter the formula. What the difference
is lies in the use of parenthesis to articulate the way the formula
works. In this case, it subtracts 1 from the sum of the COUNTIF() plus
E4. Had you written the formula as...

=SUM(COUNTIF(Sheet1!$A$13:$A$147,Sheet2!A13),E4)-1

...then the end result would be the same because using the plus operator
to add E4 to the result of COUNTIF() needs to be done BEFORE
subtracting 1. Another way to write this would be...

=SUM(COUNTIF(Sheet1!$A$13:$A$147,Sheet2!A13)-1,E4)

OR

=SUM(COUNTIF(Sheet1!$A$13:$A$147,Sheet2!A13),E4-1)

 

[joeu2004]

With certain forumlas, putting them in a cell formatted
with a «General» format (and all the cells around this
cell have a General format too), this cell trasfoms its
format to Text. [....]
I don't know if it's a localization bug (I have Excel in
Italian), but the follower formula:
=COUNTIF(Sheet1!$A$13:$A$147,Sheet2!a13)+E4-1
has the bug, while the follower:
=(COUNTIF(Sheet1!$A$13:$A$147,Sheet2!a13)+E4)-1
hasn't

Although you say that "all the cells" are formatted as General, I
think you will find that E4 is formatted as Text when the problem
arises.

I think you will also find that adding redundant parentheses (second
formula) does not make a difference. The difference is probably due
to a coincidental change in the format of E4 -- something other than
Text.

That is what I observed in experiments with the US version of XL2003.

As for the explanation.... Yes, Excel tries to be helpful and
sometimes changes a General format to something else based on the
format or nature of the operands in an expression.

I agree that it is a nuisance. AFAIK, there is no way to disable that
"helpful" behavior, at least not in XL2003, other than by changing the
format of cells from General to something else beforehand.

 

[Lucio]

With certain forumlas, putting them in acellformatted
with a «General» format (and all the cells around this
cellhave a General format too), thiscelltrasfoms its
format to Text. [....]
I don't know if it's a localization bug (I haveExcelin
Italian), but the follower formula:
=COUNTIF(Sheet1!$A$13:$A$147,Sheet2!a13)+E4-1
has the bug, while the follower:
=(COUNTIF(Sheet1!$A$13:$A$147,Sheet2!a13)+E4)-1
hasn't

Although you say that "all the cells" are formatted as General, I
think you will find that E4 is formatted as Text when the problem
arises.

No, I'm absolutely sure that cells in rows 1:12 are formatted in
General format. The cells into the range E13:E[...] (until the last
used row of the sheet - Note, last used row, not the last row) have
the Text format

I think you will also find that adding redundant parentheses (second
formula) does not make a difference.  The difference is probably due
to a coincidental change in the format of E4 -- something other than
Text.

Yes, it does!!!

That is what I observed in experiments with the US version of XL2003.

As for the explanation....  Yes,Exceltries to be helpful and
sometimes changes a General format to something else based on the
format or nature of the operands in an expression.

I agree that it is a nuisance.  AFAIK, there is no way to disable that
"helpful" behavior, at least not in XL2003, other than by changing the
format of cells from General to something else beforehand.

I found something of strange... This behaviour (that is very nuisance,
everytime I have to change the formula, I have to change the format
before that!!) aren't dependant only by formula arguments or by the
cell formats where the formula refers to, but I think (I'm not sure)
that it depends also from the format of the last cells in the same
column of the used range.
I changed the format of the 13:250 rows, this behaviour was
disappeared!!! I throw to put back the old cell format, I cannot see
again that behaviour.