Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Bizarre behaviour when entering a formula

Posted on 2014-12-11
12
Medium Priority
?
140 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

715 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