Solved

Bizarre behaviour when entering a formula

Posted on 2014-12-11
12
133 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 33

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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 33

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
 
LVL 33

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 33

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 33

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

840 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