A Microsoft Excel forum. ExcelBanter

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 » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Auto sum gives answer 0 when there are cell values



 
 
Thread Tools Display Modes
  #1  
Old August 14th 08, 06:23 PM posted to microsoft.public.excel.misc
Coylumbridge
external usenet poster
 
Posts: 1
Default Auto sum gives answer 0 when there are cell values


Ads
  #2  
Old August 14th 08, 07:23 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 22,912
Default Auto sum gives answer 0 when there are cell values

Could be the values you see are formatted as Text

Re-format all to General

Copy an empty cell.

Select the range of values and Paste Special>Add>OK>Esc.


Gord Dibben MS Excel MVP

On Thu, 14 Aug 2008 10:23:02 -0700, Coylumbridge
> wrote:


  #3  
Old November 12th 08, 03:22 PM posted to microsoft.public.excel.misc
Skarabat
external usenet poster
 
Posts: 1
Default Auto sum gives answer 0 when there are cell values

I am having this problem as well. I made sure that the autocalculation was
chosen in the tools. When I enter this line it will not total the numbers -
I just get $0
=SUM(J127+J125+J123+J121+J111+J109+J107+J105+J103+ J101+J99+J97+J91+J89+J64+J58+J26+J24+J22+J20+J18+J 16+J11+J9+J7)

"Gord Dibben" wrote:

> Could be the values you see are formatted as Text
>
> Re-format all to General
>
> Copy an empty cell.
>
> Select the range of values and Paste Special>Add>OK>Esc.
>
>
> Gord Dibben MS Excel MVP
>
> On Thu, 14 Aug 2008 10:23:02 -0700, Coylumbridge
> > wrote:
>
>
>

  #4  
Old November 12th 08, 04:13 PM posted to microsoft.public.excel.misc
David Biddulph[_2_]
external usenet poster
 
Posts: 8,651
Default Auto sum gives answer 0 when there are cell values

Firstly get rid of the unnecessary SUM() function.
=SUM(J127+J125+J123+J121+J111+J109+J107+J105+J103+ J101+J99+J97+J91+J89+J64+J58+J26+J24+J22+J20+J18+J 16+J11+J9+J7)
can be replaced by
=J127+J125+J123+J121+J111+J109+J107+J105+J103+J101 +J99+J97+J91+J89+J64+J58+J26+J24+J22+J20+J18+J16+J 11+J9+J7

Secondly, did you try the solution which Gord suggested?

The chances are that your data cells contain text not numbers. After you've
tried Gord's solution, if you're still getting a zero result, look at
=ISNUMBER(J127) and =ISTEXT(J127), [for a cell where you think you've got a
non-zero value.]
If you've still got text you may need to look for non-printing characters in
the cell.
--
David Biddulph

"Skarabat" > wrote in message
...
>I am having this problem as well. I made sure that the autocalculation
>was
> chosen in the tools. When I enter this line it will not total the
> numbers -
> I just get $0
> =SUM(J127+J125+J123+J121+J111+J109+J107+J105+J103+ J101+J99+J97+J91+J89+J64+J58+J26+J24+J22+J20+J18+J 16+J11+J9+J7)
>
> "Gord Dibben" wrote:
>
>> Could be the values you see are formatted as Text
>>
>> Re-format all to General
>>
>> Copy an empty cell.
>>
>> Select the range of values and Paste Special>Add>OK>Esc.
>>
>>
>> Gord Dibben MS Excel MVP
>>
>> On Thu, 14 Aug 2008 10:23:02 -0700, Coylumbridge
>> > wrote:
>>
>>
>>



  #5  
Old October 1st 11, 10:47 AM posted to microsoft.public.excel.misc
Ragu Binu
external usenet poster
 
Posts: 1
Default Firstly get rid of the unnecessary SUM() function.

Check the cell value it might be stored as "numbers stored as Text" change that to "Convert to Numbers" by clicking the dialouge box or else you format the entire row or column in numbers and then re enter the value of each cell and then do autosumation you will get the correct answer..

hoe it will be usefull....

Ragu.....



> On Thursday, August 14, 2008 1:23 PM Coylumbridg wrote:


>



>> On Thursday, August 14, 2008 2:23 PM Gord Dibben wrote:


>> Could be the values you see are formatted as Text
>>
>> Re-format all to General
>>
>> Copy an empty cell.
>>
>> Select the range of values and Paste Special>Add>OK>Esc.
>>
>>
>> Gord Dibben MS Excel MVP



>>> On Wednesday, November 12, 2008 10:22 AM Skaraba wrote:


>>> I am having this problem as well. I made sure that the autocalculation was
>>> chosen in the tools. When I enter this line it will not total the numbers -
>>> I just get $0
>>> =SUM(J127+J125+J123+J121+J111+J109+J107+J105+J103+ J101+J99+J97+J91+J89+J64+J58+J26+J24+J22+J20+J18+J 16+J11+J9+J7)
>>>
>>> "Gord Dibben" wrote:



>>>> On Wednesday, November 12, 2008 11:13 AM David Biddulph wrote:


>>>> Firstly get rid of the unnecessary SUM() function.
>>>> =SUM(J127+J125+J123+J121+J111+J109+J107+J105+J103+ J101+J99+J97+J91+J89+J64+J58+J26+J24+J22+J20+J18+J 16+J11+J9+J7)
>>>> can be replaced by
>>>> =J127+J125+J123+J121+J111+J109+J107+J105+J103+J101 +J99+J97+J91+J89+J64+J58+J26+J24+J22+J20+J18+J16+J 11+J9+J7
>>>>
>>>> Secondly, did you try the solution which Gord suggested?
>>>>
>>>> The chances are that your data cells contain text not numbers. After you've
>>>> tried Gord's solution, if you're still getting a zero result, look at
>>>> =ISNUMBER(J127) and =ISTEXT(J127), [for a cell where you think you've got a
>>>> non-zero value.]
>>>> If you've still got text you may need to look for non-printing characters in
>>>> the cell.
>>>> --
>>>> David Biddulph
>>>>
>>>> "Skarabat" > wrote in message
>>>> ...




  #6  
Old October 2nd 11, 07:39 PM posted to microsoft.public.excel.misc
Pete_UK[_8_]
external usenet poster
 
Posts: 25
Default Firstly get rid of the unnecessary SUM() function.

Ragu,

Is it really necessary to respond to a post that is nearly three years
old?

Pete

On Oct 1, 10:47*am, Ragu Binu > wrote:
> Check the cell value it might be stored as "numbers stored as Text" change that to "Convert to Numbers" by clicking the dialouge box or else you format the entire row or column in numbers and then re enter the value of each cell and then do autosumation you will get the correct answer..
>
> hoe it will be usefull....
>
> Ragu.....
>

 




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 On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto copy single values to another cell DDavid Excel Discussion (Misc queries) 5 September 27th 07 02:11 PM
Is there a way to link Auto Filter values to a Cell SteveC Excel Worksheet Functions 2 May 1st 06 07:47 PM
AUTO COMPLETE FOR CELL VALUES MH Excel Discussion (Misc queries) 0 February 16th 06 06:03 PM
Auto-increment cell values by one [email protected] Excel Worksheet Functions 2 November 30th 05 03:58 PM
How do I connect 2 Excel Sheets to give auto answer on the 3rd Maca Excel Worksheet Functions 1 November 1st 05 12:31 PM


All times are GMT +1. The time now is 08:00 AM.


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