Solved

Bizarre behaviour when entering a formula

Posted on 2014-12-11
12
132 Views
Last Modified: 2014-12-12
All,

I am experiencing some bizarre behaviour when entering a formula. Below are screen shots of each stage of the process.

I have a sheet with a Summary list of values and a total:
original sheet
Cell F8 is selected as you can see in the address bar/cell name at top left. As highlighted the cell is formatted as General.

I enter a formula in cell F8 pulling the total from a Data sheet. This total should be the same as the total in B8. Screen shot below:
With formula
As you can now see the cell format has changed to Text. Why???  But the value of the SUM formula is correct. Realising that the two total values are not the same, I edit the SUM formula to deduct the other total:
Edited formula
Edited formula only shows formula in cell and not the result; because it is now text but why did it change?

The only thing I can see is that the header of column C on the Data sheet, ie the first cell in the SUM range is formatted as Text. If I change the header cell to General, it works properly; ie it doesn't change the SUM to text.

I know excel is clever enough to change the format of a cell when you first enter a formula, eg if a cell referred to is a date, it will change the formula cell to a date. Is it not clever enough to realise that it doesn't need to change in this instance or at least can't determine what format to use so does nothing?

Any suggestions? A bit of a quandry for me.

Thanks
Rob H
0
Comment
Question by:Rob Henson
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40494805
Do you have any macros which could affect this? does it only happen on cell F8?
0
 
LVL 32

Author Comment

by:Rob Henson
ID: 40494870
No macros, new workbook. Didn't try any other cell, just happened to pick F8 at random.
0
 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40495543
It may be a problem with thousand and decimal separators. You may check it.
When decimal separator setting in excel and the separators used in values didn't match, excel treats them as text.
You may need to check values in column C in "Data" sheet.
Whole column C in "Data" sheet may also be formatted as Text.
0
 
LVL 32

Author Comment

by:Rob Henson
ID: 40495630
Only header in data column is text, values are general with no separators. If values were text SUM would return zero.
0
 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40495676
Text values will be summed as 0 in Sum formula, but others should be added normally.
Some cells may be formatted as text and some cells as general etc.
And i'm not sure about effect of single quote in the beginning of cell value to text/number combined formulas.

If the first cell you refer in your formula is formatted as Text, the cell you enter formula also turns to Text. Not sure if it always works this way.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40495828
It's just Excel trying to be helpful and failing. It's been discussed (complained about) before - maybe one day they'll stop it. The easiest workaround is just to not format the header cells as Text - use the apostrophe prefix instead if required.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 32

Author Comment

by:Rob Henson
ID: 40495859
I don't deliberately format the header as text, its just how it comes out of the system that produces the data.
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40495862
I've recreated the same problem.

I would call it a bug, but Microsoft disagrees - here is what it says about it: http://support.microsoft.com/kb/135506/en-us

And that article goes all the way back to Excel 5.0! Clearly Microsoft is not going to change it.

While it seemingly looks like a formula, the number is left-aligned (like text) instead of right-aligned (like a number).

Basically, there's nothing you can do about it except workaround it. Sorry!
0
 
LVL 32

Author Comment

by:Rob Henson
ID: 40495931
That's what I thought. Workaround as is often the case with MS. I wonder if Open Office or others behave the same.
0
 
LVL 32

Author Closing Comment

by:Rob Henson
ID: 40495957
Phillip - just looked at that KB article and as you point out it goes all the way back to Excel 5.

However, the article suggests it doesn't apply to 2010 or 2013 as they are not listed; unless that is just because the article hasn't been updated. I am using 2010 where it is happening.

Thanks for everyone's help. It is a feature that I was aware of and seen it happening when I wanted it to, unfortunately on this occasion I didn't want it to.

Regards
Rob H
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40495958
>> unless that is just because the article hasn't been updated

I would suggest that that is the case.
0
 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40495978
If i understand right, problem is about this,
"If the first cell you refer in your formula is formatted as Text, the cell you enter formula also turns to Text. Not sure if it always works this way."

I see that is a more general thing then about beign first cell you refer.

It behaves different in some situations.
EXCEL-FORMULA-SHOWN-AS-TEXT.xlsx
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now