[Webinar] Streamline your web hosting managementRegister Today

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

Limitation on "SUM"??

Hi,

I have 31 cells going left to right that all have whole numbers, (no formulas).  In the 32nd cell, I am trying to use the SUM formula to add all the cells.  Here is where the problem lies.  I used the standard =sum(c2:ag2), however, upon hitting enter, only the formula will show up in the cell.  I worked on this for a while, and finally got =sum(c2+d2+e2.....) to work, but I noticed I had accidently left out one cell.  When I went back in to correct this, (adding the cell in the same manner as all the other cells), again only the formula would show up and not the added sum.  I am working on Office 98, Macintosh.  This is the only time I've ever had a problem using formulas.  Does Excel have a limit to the number of cells that can be used in a formula?  Does Excel have a hard time working left to right?  How can I get this to work?

Thanks.
0
messentary
Asked:
messentary
  • 5
  • 4
  • 3
  • +3
1 Solution
 
marszeCommented:
In the tools->options (in the pc) there is a check box that control the display of formulas or results.
When is checked, the sheet shows formulas instead of results values.
0
 
calacucciaCommented:
Marsze,

that doesn't seem to be the problem, messentary states that this problem only appears when he's having 31 cells on the same row in a sum funtion.

Messentary,

that's the first time I've heard about this. Of course, I use a Windows PC with Office 97, so it could be a bug linked to the 98 version for the Mac only.

I hope to come back after some research.

Calacuccia
0
 
messentaryAuthor Commented:
Marsze, as Calacuccia states, I do see other formulas, so don't you think that I made sure that box was unchecked?

Cal,  I thought about that too, however, it is basically Ecxel 5.0 with the same limitations and functions as the Windows version, (which is what I use at home).  But what I will do, (and I should have thought of this earlier), is email the worksheet to my home PC, and see if the problem, or glich, persists.  Please do some research.  I did a search in the help files for limitations on the functions, and there are quite a few, however, I didn't see anything that would limit how many cells could be used in a simple sum function.

Here is a little something that seems strange.  I can do sums, averages, ... going up and down with absolutely no problems, but even a simple function like =sum(c2+d2) will not show the value, only the formula.  Any suggestions?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
tureCommented:
messentary,

I believe that this problem is due to the number formatting set for either the summed range (C2:AG2) or possibly the cell where you enter the sum formula. It is NOT a limitation of the SUM function - it can sum a range with thousands of cells without problems.

1. Select the summed range (C2:AG2)
2. Format - Cells - 'Number' tab
3. Select 'General' or a suitable number format
   Make sure that you DO NOT choose 'Text' - it will cause problems like the one you describe
4. Click 'OK' to accept the setting
5. Select the cell where you will enter the formula and set the number format in the same way

Now try the sum formula again...
  =SUM(C2:AG2)
Any better?

Ture Magnusson
Karlstad, Sweden
0
 
antratCommented:
Ture
 I too thought as you did that the cell formating may be causing the problem, but on trying to reproduce messentary's problem all that would happen is Excel would ignore the cell not formated correctly. But having said that I'm not using a Mac.

messentary
Does this problem happen on any worksheet you use? and any workbook? This really is strange!

antrat

0
 
messentaryAuthor Commented:
well, this is the only time I have had this problem.  But it is also the only time I have tried to use the sum function in a row.  I have never had any problems using any function in a column.  I will try out Ture's solution when I get to work and get right back to you.

Thanks for the input so far.
0
 
messentaryAuthor Commented:
So here I am at work.  I tried the formatting the cells to numbers, but that didn't work.  So i tried going very basic, and removing the sum function, and just using math, (i.e. =(c2+d2+e2.....) and that worked.  It still makes me wonder what was the problem with the sum function.  I will still await answers to this, as I will definately be using other functions in rows with a lot of cells in the future, and not all of them can be brought back to basic math.  

If there are no responses in about two days, could either cal, ture, or amtrat respond to remind me to give out the points.  I know there was no answer to this particular problem, but I am a firm believer that no question should ever be deleted.  If people try, they should recieve some compensation.  I will distribute points to all who gave comments in a couple of days.
0
 
calacucciaCommented:
Messentary,

just to make sure: did you try the sum function at your home PC ?

Calacuccia
0
 
messentaryAuthor Commented:
Not yet, I have to email it home tonight.  But that is still something I want to try out.  I will let you know if I ran into the same problem.
0
 
tureCommented:
messentary,

Could you possibly send me the workbook that causes these problems? I'd like to have a look at it.

/Ture
0
 
calacucciaCommented:
Well that's a good idea from Ture. Could you send it me too:

geert.dumortier7@yucom.be

Calacuccia
0
 
tureCommented:
Sorry... My e-mail address is: ture_magnusson@hotmail.com

/Ture
0
 
antratCommented:
Me too

drhawley@geo.net.au

antrat
0
 
ahammarCommented:
I wouldn't mind a copy either if you get a chance.
ahammar@cyberhighway.net


Antrat: What are you waiting for?? Only 50 more points to go...............:-)

I should just give you the points myself to kill the suspense...:-) Good Luck!

Cheers!
ahammar
0
 
antratCommented:
Darn, I was hoping you wouldn't notice :)

antrat
0
 
messentaryAuthor Commented:
Ture and Calacuccia, you have a question in the MS Ofiice forum for 50 points. Thank you for your help.

I emailed the worksheet to my home PC, and the problem did not persist, so I am to believe that it was definately a Mac issue.

As for emailing out the worksheet, I would love to do that, but there is a confidentiality issue that attaches itself.  Thanks to everyone who assisted.
0
 
antratCommented:
Thanks messentary

If you ever find out the reason I would love to know.

antrat
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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