Technical Discussion
  >> Windows Issues


Register (or login) on our website and you will not see this ad.


  Print Thread
Standard User GeoffB
(fountain of knowledge) Mon 28-Apr-14 07:34:15
Print Post

Excel 2013 text numbers


[link to this post]
 
I have stored some currency values as text in a large sheet. These are expected payments that I want to record but which can't be included in calculations as they haven't arrived yet. I had assumed that these would be ignored in all calculations, but I find that, whilst they don't affect the running totals, they do affect the overall total of the column. Is this a deliberate feature or a bug? Surely all text entries should be ignored for calculation purposes.

Dell Studio1558 with Win7 Home Premium
8GB RAM
HP Pavilion netbook Win8 6GB RAM
IE11 and Live Mail
BT Infinity via HH3
(Virgin 100Mbs as backup)
Standard User cheshire_man
(knowledge is power) Mon 28-Apr-14 07:53:06
Print Post

Re: Excel 2013 text numbers


[re: GeoffB] [link to this post]
 
Have you stored them as text, i.e. with a ' (single quote) in front, or formatted them as Text?

If the latter then, as far as i know, they'll still be treated as numbers. Formatting as Text only affects how they're displayed.

Edit for typo

Tony
We have more and more laws, and less and less enforcement

Edited by cheshire_man (Mon 28-Apr-14 09:29:06)

Standard User micksharpe
(eat-sleep-adslguide) Mon 28-Apr-14 09:17:22
Print Post

Re: Excel 2013 text numbers


[re: GeoffB] [link to this post]
 
It might be safer to store the expected values in a separate column--the potential for confusion...

Alternatively, you could use conditional formatting to highlight the text fields.

'Sir, please,' she said... 'Will you not share your wisdom with us?'
'I have no wisdom,' he told her.
'Your experiences, then?'
'They have been trivial, uninteresting, and full of error.'
Ian M. Banks - Feersum Endjinn


Register (or login) on our website and you will not see this ad.

Standard User micksharpe
(eat-sleep-adslguide) Mon 28-Apr-14 10:18:35
Print Post

Re: Excel 2013 text numbers


[re: GeoffB] [link to this post]
 
On doing a bit of research:

In formulas, text values in cells are converted to numbers whenever possible. However, the SUM function has some peculiarities. It ignores cell references containing text values, but it will convert text literals to numbers whenever possible. For example:

A1: 1 (number)
A2: '2 (text)
A3: 3 (number)

=A1+A2+A3 gives 6
=SUM(1,2,3) gives 6
=SUM(1,"2",3) gives 6
=SUM(A1,A2,A3) gives 4
=SUM(A1:A3) gives 4

See the Excel function reference entry for SUM.

Why does it do this? Presumably, because there was a bug in the original implementation and the developers didn't want to change the behaviour in case it broke existing worksheets.

Apart from writing a VBA function, I cannot find a clever work-around. However, one solution would be to create a hidden column to reproduce the values as numbers (by adding 0 to them) and then summing those values. If you define:

B1: =A1+0
B2: =A2+0
B3: =A3+0

=SUM(B1:B3) would give 6

'Sir, please,' she said... 'Will you not share your wisdom with us?'
'I have no wisdom,' he told her.
'Your experiences, then?'
'They have been trivial, uninteresting, and full of error.'
Ian M. Banks - Feersum Endjinn
Standard User micksharpe
(eat-sleep-adslguide) Mon 28-Apr-14 12:23:29
Print Post

Re: Excel 2013 text numbers


[re: micksharpe] [link to this post]
 
OK. Here's the solution...

Use the formula =SUM(A1:A3+0)

You must enter the formula using Ctrl+Shift+Enter. The formula will be displayed in curly brackets which denotes an array formula.

The formula will add zero to each cell in the range and then sum the results. Any text values will be treated as numbers where possible. If a cell contains something that cannot be treated as numeric, a #VALUE error will be displayed. If you enter the formula without using Ctrl+Shift+Enter, a #VALUE error will be displayed.

Microsoft article on array formulas

'Sir, please,' she said... 'Will you not share your wisdom with us?'
'I have no wisdom,' he told her.
'Your experiences, then?'
'They have been trivial, uninteresting, and full of error.'
Ian M. Banks - Feersum Endjinn
Standard User philippercival
(fountain of knowledge) Mon 28-Apr-14 15:18:08
Print Post

Re: Excel 2013 text numbers


[re: micksharpe] [link to this post]
 
I suspect that he does not want the text values included in any calculations. Your solution is if I am reading you correctly making sure they are included.

if I am right then the sum function for a simple ranges will exclude text values.

edit added

If the other calculations are not too complex then perhaps you could use the isnumber function
eg.
assuming
a1 contains 2
and
a2 contains '2 (text)
then
in b1 =a1*isnumber(a1) will produce 2
in b2 =a2*isnumber(a2) will produce 0

Perhaps an array expert will come up with an array version of the above.

http://speedtest.net/result/2459383290.png

Solar Results
Nov  143,  Dec  125,  Jan  77,  Feb 174,  Mar  210,  April 384, May 450, 
June 485, July 512, Aug 433, 
Max Day  25.2

Edited by philippercival (Mon 28-Apr-14 16:11:01)

Standard User micksharpe
(eat-sleep-adslguide) Mon 28-Apr-14 15:30:49
Print Post

Re: Excel 2013 text numbers


[re: philippercival] [link to this post]
 
You're right. In that case, the obvious solution for calculating running totals would be to use a formula such as

For cell B2: =B1+IF(ISNUMBER(A2),A2,0)

This would need to be repeated for each running total.

Still, the best solution in my opinion is to place estimated values in a separate column.

'Sir, please,' she said... 'Will you not share your wisdom with us?'
'I have no wisdom,' he told her.
'Your experiences, then?'
'They have been trivial, uninteresting, and full of error.'
Ian M. Banks - Feersum Endjinn
Standard User GeoffB
(fountain of knowledge) Mon 28-Apr-14 16:13:28
Print Post

Re: Excel 2013 text numbers


[re: micksharpe] [link to this post]
 
Thanks for the very detailed answers. I've taken the wimp's route and put the values into a separate column so that I can simply do an =B1 type formula to transfer them into the main column when the payments come through. I've certainly learnt a lot about Excel functions today though!

Dell Studio1558 with Win7 Home Premium
8GB RAM
HP Pavilion netbook Win8 6GB RAM
IE11 and Live Mail
BT Infinity via HH3
(Virgin 100Mbs as backup)
Standard User micksharpe
(eat-sleep-adslguide) Mon 28-Apr-14 16:40:56
Print Post

Re: Excel 2013 text numbers


[re: GeoffB] [link to this post]
 
Not the wimp's route at all. Spreadsheets need to be robust and easy to understand, especially if you are writing them for someone else's use. The golden rule is KISS... Keep It Simple Stupid. wink

'Sir, please,' she said... 'Will you not share your wisdom with us?'
'I have no wisdom,' he told her.
'Your experiences, then?'
'They have been trivial, uninteresting, and full of error.'
Ian M. Banks - Feersum Endjinn
Standard User XRaySpeX
(eat-sleep-adslguide) Mon 28-Apr-14 19:05:57
Print Post

Re: Excel 2013 text numbers


[re: cheshire_man] [link to this post]
 
In reply to a post by cheshire_man:
If the latter then, as far as i know, they'll still be treated as numbers. Formatting as Text only affects how they're displayed.
Depends on whether cells are formatted as text before or after the (quasi-) numbers are input to them.

If formatted before than the 'nos' are displayed and stored as text.
If formatted after than the 'nos' are displayed as text but stored as nos.

1999: Freeserve 48K Dial-Up => 2005: Wanadoo 1 Meg BB => 2007: Orange 2 Meg BB => 2008: Orange 8 Meg LLU => 2010: Orange 16 Meg LLU => 2011: Orange 20 Meg WBC
  Print Thread

Jump to