Formala Leading Plus Sign "+" Behavior

[yellowizer]

I have not seen this behavior in Excel (2013) before, but was trying to trouble shoot how to turn it off.

The user entered a formula like this (without quotes) in a spreadsheet: "+180/90"

Excel then automatically converted the cell's formula to a value: "2"

However, when I try to duplicate the issue in a new spreadsheet I enter the formula as above. However Excel changes the formula to "=180/90" and displays the formulas result, "2".

I tried adjusting the Calculation options to manual and Advanced Lotus Compatibility Settings for the sheet (File-->Options-->Advanced) without success. I even looked in AutoCorrect and couldn't find anything.

Anyone ever experience this issue? Ideas on how to duplicate the behavior?

 

[joeu2004]

I have not seen this behavior in Excel (2013) before, but was trying to trouble shoot how to turn it off.

The user entered a formula like this (without quotes) in a spreadsheet: "+180/90"[.] Excel then automatically converted the cell's formula to a value: "2"
[....]
I tried adjusting the Calculation options to manual and Advanced Lotus Compatibility Settings for the sheet (File-->Options-->Advanced) without success. I even looked in AutoCorrect and couldn't find anything.

Anyone ever experience this issue? Ideas on how to duplicate the behavior?

No and no. But I have read similar reports of such behavior. I don't believe they are limited to Excel 2013 (which I do not have).

I would really like to understand it. But like you, I cannot duplicate it (in Excel 2003, 2007 and 2010). I have tried all the things you did, even AutoCorrect options.

However, when I try to duplicate the issue in a new spreadsheet I enter the formula as above. However Excel changes the formula to "=180/90" and displays the formulas result, "2".

To be clear: displays =180/90 in the Formual Bar, and displays 2 in the cell when not in edit mode (e.g. another cell is selected).

... Which is the correct and expected behavior. Agreed?

Can you take an offending Excel file, strip it down to just the one cell where the problem occurred, and still duplicate the problem (a) on your computer or (b) on the user's computer?

If you can do #b, but #a, what is different between your computer environment and the user's?

You might need to look at external characteristics such as Regional and Language Options settings and even the keyboard type.

 

[pgc01]

Hi
Welcome to the board

Check the workbook for code. My guess is that the worksheet where that happen has a change event macro.

 

[joeu2004]

Check the workbook for code. My guess is that the worksheet where that happen has a change event macro.

You might be right. But FYI, that was not the case (or so the user insisted) in the other instances that I looked into (to no avail) over the years. Of course, that was among the first questions we asked.

Funny thing is: as soon as we started asking probing questions, most users stopped responding. Either they were too embarrassed to admit the obvious ("I had pressed F9 in the Formula Bar and forgot to press Esc to restore the formula"), or the problem inexplicably disappeared.

I hope "yellowizer" won't be so rude.

[EDIT] I should explain.... The "rudeness" is not quitting the discussion because the problem went away with or without explanation. The "rudeness" is not explaining why you are quitting the discussion, which might be as simple as: "no longer a problem" or "no longer interested".

 

[Fazza]

I've encountered this in the past and recall being rather puzzled. I gave up after a few minutes as couldn't see how to change it. Haven't seen it for a while though..

 

[pgc01]

joeu2004, Fazza

Thank you for sharing your experience.

This kind of feature, entering a value and let excel change it automatically, is implemented a lot of times. For ex. the user enters 1023 and there's some code that automatically changes it to 10:23. I thought this might be the case.
I did not know that this could be a bug that sometimes surfaces.

 

[Fazza]

Recalling a bit more about it, for me it might have been a work file received via email from someone - so from a different version. I think it wasn't one of my files. Curious for sure

 

[konew1]

I used to get this quite often at work when using 2003. I don't write code but there could have been code embedded in some file that I had been given. The environment was not one of extensive code being used, so I think it unlikely to be the case. I found the perfect work around solution. I stopped using + to start a formula.

It seems strange that several different users have experienced the same issue. To attribute it to code is a suggestion that a lot of coders thought it a worthwhile piece of code. I struggle with that. Maybe the reason more people don't see it is because not many start a formula with +

 

[Jon Peltier]

Use an equals sign for your formulas. The plus sign is a legacy compatibility thing from when Lotus 1-2-3 was still around.

 

[joeu2004]

I did not know that this could be a bug that sometimes surfaces.

We cannot say that with impunity. In fact, I doubt it very much. It's one heckuva defect, if that were the case.

If Excel does indeed cause the change, it is more likely a feature of Lotus "transition" support that we do not understand yet. (However, I doubt that, too.)

But there are several "user errors" that could explain the behavior. We have mentioned two:

1. Pressing F9 in the Formula Bar, then moving the curor without pressing Esc to undo F9 calculation.

2. An event macro that replaces (some) constant expressions with their value.

My money is on #1. It is easy to fat-finger, especially if the user has a keyboard like mine where the "numeric keypad" overlays regular keys instead of being separate and off to the side.

#2 seems difficult to implement, and it is unclear why anyone would. Arguably, most examples are simple to implement: replace the entire formula with its value. But I have seen examples where a subexpression is replaced with its value, leaving the rest of the formula intact.