messentary
asked on
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.
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.
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
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
ASKER
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?
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?
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Thanks for the input so far.
ASKER
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.
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.
Messentary,
just to make sure: did you try the sum function at your home PC ?
Calacuccia
just to make sure: did you try the sum function at your home PC ?
Calacuccia
ASKER
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.
messentary,
Could you possibly send me the workbook that causes these problems? I'd like to have a look at it.
/Ture
Could you possibly send me the workbook that causes these problems? I'd like to have a look at it.
/Ture
Well that's a good idea from Ture. Could you send it me too:
geert.dumortier7@yucom.be
Calacuccia
geert.dumortier7@yucom.be
Calacuccia
Sorry... My e-mail address is: ture_magnusson@hotmail.com
/Ture
/Ture
Me too
drhawley@geo.net.au
antrat
drhawley@geo.net.au
antrat
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
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
Darn, I was hoping you wouldn't notice :)
antrat
antrat
ASKER
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.
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.
Thanks messentary
If you ever find out the reason I would love to know.
antrat
If you ever find out the reason I would love to know.
antrat
When is checked, the sheet shows formulas instead of results values.