A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Despite formula I use (for example SUM) result is always zero.



 
 
Thread Tools Display Modes
  #1  
Old April 23rd, 2010, 03:10 PM posted to microsoft.public.excel.worksheet.functions
Anida
external usenet poster
 
Posts: 2
Default Despite formula I use (for example SUM) result is always zero.

Despite formula I use (for example the most simple SUM) it always shows me
zero result even it should show a lot more. Values in other cells (cells that
I sum) are in number format. Why this happens??
Ads
  #2  
Old April 23rd, 2010, 03:27 PM posted to microsoft.public.excel.worksheet.functions
David Biddulph
external usenet poster
 
Posts: 8,714
Default Despite formula I use (for example SUM) result is always zero.

It doesn't matter whether the cells are FORMATTED to DISPLAY as number;
what matters is whether the CONTENTS are numbers. My guess is that they are
text that might look like a number. If one of your numbers is in A2, what
do the formulae =ISTEXT(A2) and =ISNUMBER(A2) show?
--
David Biddulph


"Anida" wrote in message
...
Despite formula I use (for example the most simple SUM) it always shows me
zero result even it should show a lot more. Values in other cells (cells
that
I sum) are in number format. Why this happens??



  #3  
Old April 24th, 2010, 03:32 AM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 8,574
Default Despite formula I use (for example SUM) result is always zero.

This quick fix might suffice for you
Instead of using: =SUM(A2:A5)
use this, array-enter it, ie press CTRL+SHIFT+ENTER to confirm the formula:
=SUM(A2:A5+0)
The "+0" will coerce all text numbers (if any) within the source range
(A2:A5) to real numbers w/o impacting their intrinsic values, and return the
desired results. Happy? hit the YES below
--
Max
Singapore
---
"Anida" wrote:
Despite formula I use (for example the most simple SUM) it always shows me
zero result even it should show a lot more. Values in other cells (cells that
I sum) are in number format. Why this happens??

  #4  
Old April 26th, 2010, 09:43 AM posted to microsoft.public.excel.worksheet.functions
Anida
external usenet poster
 
Posts: 2
Default Despite formula I use (for example SUM) result is always zero.

When I put =isnumber(a2) it shows me true, and for =istext false. What next?

"David Biddulph" wrote:

It doesn't matter whether the cells are FORMATTED to DISPLAY as number;
what matters is whether the CONTENTS are numbers. My guess is that they are
text that might look like a number. If one of your numbers is in A2, what
do the formulae =ISTEXT(A2) and =ISNUMBER(A2) show?
--
David Biddulph


"Anida" wrote in message
...
Despite formula I use (for example the most simple SUM) it always shows me
zero result even it should show a lot more. Values in other cells (cells
that
I sum) are in number format. Why this happens??



.

  #5  
Old April 26th, 2010, 12:26 PM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
external usenet poster
 
Posts: 19,791
Default Despite formula I use (for example SUM) result is always zero.

Make sure that calculation is set to automatic.

In xl2003 menus:
tools|options|calculation tab

And your range to sum does include A2, right?

Anida wrote:

When I put =isnumber(a2) it shows me true, and for =istext false. What next?

"David Biddulph" wrote:

It doesn't matter whether the cells are FORMATTED to DISPLAY as number;
what matters is whether the CONTENTS are numbers. My guess is that they are
text that might look like a number. If one of your numbers is in A2, what
do the formulae =ISTEXT(A2) and =ISNUMBER(A2) show?
--
David Biddulph


"Anida" wrote in message
...
Despite formula I use (for example the most simple SUM) it always shows me
zero result even it should show a lot more. Values in other cells (cells
that
I sum) are in number format. Why this happens??



.


--

Dave Peterson
  #6  
Old April 26th, 2010, 12:28 PM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
external usenet poster
 
Posts: 19,791
Default Despite formula I use (for example SUM) result is always zero.

And any chance that the sum is 0? Maybe you have positive, negative and zero
values in the range?

And one more thing to check.

Type these two formulas in separate cells:
=count(A1:a10)
=counta(a1:a10)

(change A1:A10 to the range you're summing.)

=count() will count the numbers in the range.
=counta() will count numbers, strings, even formulas that evaluate to "" in the
range.

Anida wrote:

When I put =isnumber(a2) it shows me true, and for =istext false. What next?

"David Biddulph" wrote:

It doesn't matter whether the cells are FORMATTED to DISPLAY as number;
what matters is whether the CONTENTS are numbers. My guess is that they are
text that might look like a number. If one of your numbers is in A2, what
do the formulae =ISTEXT(A2) and =ISNUMBER(A2) show?
--
David Biddulph


"Anida" wrote in message
...
Despite formula I use (for example the most simple SUM) it always shows me
zero result even it should show a lot more. Values in other cells (cells
that
I sum) are in number format. Why this happens??



.


--

Dave Peterson
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:06 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.