Solved

Bizarre behaviour when entering a formula

Posted on 2014-12-11
12
131 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
Comment Utility
Do you have any macros which could affect this? does it only happen on cell F8?
0
 
LVL 31

Author Comment

by:Rob Henson
Comment Utility
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
Comment Utility
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 31

Author Comment

by:Rob Henson
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 31

Author Comment

by:Rob Henson
Comment Utility
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
Comment Utility
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 31

Author Comment

by:Rob Henson
Comment Utility
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 31

Author Closing Comment

by:Rob Henson
Comment Utility
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
Comment Utility
>> 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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

743 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

17 Experts available now in Live!

Get 1:1 Help Now