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

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)


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 28Apr14 09:29:06)


It might be safer to store the expected values in a separate columnthe 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.


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 workaround. 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


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


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 28Apr14 16:11:01)


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


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)


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.
'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


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 DialUp => 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


