Excel mistakes cell formats

March 29th, 2011 - 04:14 am ET by Lucio | Report spam
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
email Follow the discussionReplies 4 repliesReplies Make a reply

Replies

#1 Dave Peterson
March 29th, 2011 - 07:58 am ET | Report spam
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: ¡

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

Now format D1 as Text.
put ASDF in D1
put Ñ 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.

On 03/29/2011 03:14, Lucio wrote:
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

Similar topics