excel 2010 and entering formulas

[jbrandt]

Hi all,

So, I've noticed in 2010, that when I enter values into a cell with some special characters like "( ) + - / ? " it automatically tries to enter it as a formula.

Example, If I type:

-5 (?)

I get the "the formula you typed contains an error" message and hi-lights the "?". Well, I don't want a formula. I know that you can type a " ' " at the beginning it will enter as text, as in:

'-5 (?)

My issue is that I don't remember Excel 2003 doing this. It would ONLY recognize a formula if you type an "=". It seems that in 2010 it tries to recognize a formula even with out the "="...

Is there a preference buried somewhere where I can tell Excel to only recognize a formula IF a "=" is used?

Thanks!

 

[Joe4]

I am not sure if any changes were made 2010, or if there any settings that you can change (I am still using 2007), but if you format your column to be Text before typing anything in, then I think you should be able to type:
-5
without having to use a single quote mark first and it won't treat it as a formula.

 

[jbrandt]

Yeah, I tried that. The problem is that for the values that don't have special characters ("?") I want to be able to sum them, etc...

Also, a correction, I meant to say I used to be using Excel 2007, not 2003. Excel 2010 is still what I'm using now.

I work in satellite remote sensing, and I'm compiling a list of values that correspond to elevation change (subsidence) based on the difference between two satellite radar images (each image gives a relative elevation for a certain date). Some values have more uncertainty than others (hence the "?" or even a "-"), but the other values I want to be able to add together for further analysis.

Sample Data:

A B C D
1 1st date 2nd date Subsidence Value (mm)
2 20040826 20041209 -
3 20040930 20041104 -10
4 20041104 20050113 -15
5 20050113 20050324 -
6 20050217 20051229 -
7 20051229 20060309 -10(?)

So I want to be able to add D3 D4 to show total subsidence btwn 9/30/2004 and 1/13/05, and then be able to recognize that "about" 10mm occurred in cell D7.

 

[Joe4]

It is generally note a good idea to mix numbers and letters in a column if you wish to perform mathematical computations on them. How about adding an additional column where you can enter the "?" or maybe something else to indicate uncertainty (like a "U" for uncertain).

Then you can use a SUMIF formula to just add up the rows that don't have a "U" in this new column.

 

[jbrandt]

I'm working with a quite extensive Excel sheet I started in Excel 2007, so adding another column for the "U" qualifier is not an easy task (lots of linked and merged cells, etc...). Before I upgraded to 2010, I could just have a "?" next to the value I was uncertain of, but for the cells I DID want to use, I'd just link to those specific cells in a SUM calculation. If I format the ALL cells in that row as text, I can't add the ones I want together.


i.e.

Total subsidence = SUM(D3, D4, D10...)

 

[jmthompson]

FYI- I just tested in XL2007 and it does evaluate to a formula error:

-5(?)

Can you double check how this value was entered previously?

 

[Joe4]

I understand what you are saying. I am just telling you that you are experiencing some limitations and issues because of poor sheet design (mixing text and numbers in a column you want to perform mathematical computations on is poor design and limits your options). If it reasonable to rectify that, that would be the recommended way to go.

FWIW, I have Excel 2007 and I am experiencing the same error that you say you are experiencing in 2010, but not in 2007. You may want to check the different options under Excel Options, and see if there are any settings that look like may make a difference (play around with promising ones and see if you can get it to work out).

Here is another possibility. Format the whole column as Text. Then place a Worksheet_Change event procedure in your worksheet that will automatically change any entry that is all numeric or begins with a "=" (to indicate a formula) to numeric upon cell entry. That should do what you want.

Here is what that Event Procedure code would look like if being applied to column C:

Private Sub Worksheet_Change(ByVal Target As Range)
 
'   Check to make sure only one cell entered and entry is in column C
    If Target.Count = 1 And Target.Column = 3 Then
'   See if value is all numeric or a formula, if so change to numeric format
        If IsNumeric(Target.Value) Or (Left(Target, 1) = "=") Then
            Application.EnableEvents = False
            Target.NumberFormat = "0"
            Target = Target.Value
            Application.EnableEvents = True
        End If
    End If
 
End Sub

 

[jbrandt]

I just checked on a coworker's machine that has 2007 and 2003 and I am in fact an idiot.

I did some looking at another older spreadsheet I made, and what I was actually able to do was type "5?" without it trying to see it as a formula. If I type "-5?" it still tries to make it a formula in any version I use. Apparently, it's the "-" that makes the difference.

The older spreadsheet I made was for an area where there was only uplift (only positive numbers), this new area has both uplift and subsidence (negative numbers) so I hadn't need to enter a "-" until now.

I guess now it's just a little more annoying since now I have to adjust my spreadsheet to account for all the new negative numbers. I will likely just take the time and add a new column for the certainty qualifier.

Thanks for your replies!