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.
LVL 3
messentaryAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.