Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 141
  • Last Modified:

Bizarre behaviour when entering a formula

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
Rob Henson
Asked:
Rob Henson
  • 5
  • 3
  • 3
  • +1
1 Solution
 
Phillip BurtonCommented:
Do you have any macros which could affect this? does it only happen on cell F8?
0
 
Rob HensonIT & Database AssistantAuthor Commented:
No macros, new workbook. Didn't try any other cell, just happened to pick F8 at random.
0
 
Hakan YılmazTechnical Office MEP EngineerCommented:
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
Industry Leaders: 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!

 
Rob HensonIT & Database AssistantAuthor Commented:
Only header in data column is text, values are general with no separators. If values were text SUM would return zero.
0
 
Hakan YılmazTechnical Office MEP EngineerCommented:
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
 
Rory ArchibaldCommented:
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
 
Rob HensonIT & Database AssistantAuthor Commented:
I don't deliberately format the header as text, its just how it comes out of the system that produces the data.
0
 
Phillip BurtonCommented:
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
 
Rob HensonIT & Database AssistantAuthor Commented:
That's what I thought. Workaround as is often the case with MS. I wonder if Open Office or others behave the same.
0
 
Rob HensonIT & Database AssistantAuthor Commented:
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
 
Phillip BurtonCommented:
>> unless that is just because the article hasn't been updated

I would suggest that that is the case.
0
 
Hakan YılmazTechnical Office MEP EngineerCommented:
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

Independent Software Vendors: 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!

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now