Software·43,882 discussions

Sum Function in Excel 2000

Locked

I have a simple spreedsheet. The very last column contains a formula. I have that cell formated to display a whole number (no decmial). At the bottom of this column I want to sum the numbers in the column. When I attempt to do this execl is taking the absolute value of the number and adding. What I want to do is to add the whole numbers (after rounding)

Data: Cell J5 contains the formula 6*530/2000
Cell J6 contains the formula 100*556/2000

Absolute value of J5 is 1.59 cell is formated to display 2
Absolute value of J6 is 27.8 cell is formated to display 28

Absolute value of J32 is 29.39. I want excel to sum the displayed values not the absolute value.

Topic

Sum Function in Excel 2000

In reply to Sum Function in Excel 2000

I had no problem with this when I formatted the cells themselves to “number” with decimal points as “0” instead of asking for rounding in the formula.

After entering the formula…
Highlight the range of cells, including the formulas and the total. Do not round any numbers in the formulas themselves.
Right-click to bring up shortcut menu. Click on “format cells”. In the Number tab, click on Number on the left and put “0” in the box at the right that says “Decimal Places”. Click on OK.

This worked for me Excel 2000 on Win2KPro with the numbers given here.

Nikki

Sum Function in Excel 2000

In reply to Sum Function in Excel 2000

I am using the same OS

This has been attempted. It is how I she got the numbers in J5 and J6 to display 2 and 28. The issue is that even though the numbers are displaying as whole numbers they are adding with the numbers to the right of the decmial.

Sum Function in Excel 2000

In reply to Sum Function in Excel 2000

What you probably have to do is to use an array function:
{=SUM(ROUND(J1:J11;0))} [addding cells J1 through J11]
For two values this would not be needed. [=ROUND(J5;0)+ROUND(J6;0) is sufficient]
For a whole array of values it is the best solution.
NOTE Here I assume that : [colon] is the sign for denoting a range and ; [semi colon] is the list separator. You may live in one of those countries where they use , [comma] as list separator, which is not unlikely as you are using a decimal point.
The formula has to be entered without the {}
Once you have typed the formula use SHIFT CTRL ENTER to make the array function.
Good luck

Sum Function in Excel 2000

In reply to Sum Function in Excel 2000

Indeed, if you do not need the individual values for further individual calculation, the idea of answer #3 is very simple. Just round each value [use the correct formula] and apply the classical sum function at the bottom. Actually, that is what I usually do when there is no need for further calculations. As you did not specify further details I gave a solution only involving the sum and nothing else.
So, if you don?t need the individual items I would suggest follow the advice of answer #3 [again with the correct round formula]

Sum Function in Excel 2000

In reply to Sum Function in Excel 2000

Poster rated this answer

Sum Function in Excel 2000

In reply to Sum Function in Excel 2000

Your problem is that the SUM function uses the actual values derived from the formula in each cell, not from what the cell is formatted to display.

Answer 2 will work.

Another option would be to use the ROUND function in the formula for each cell (rather than formatting the cell to a given number of decimal places) (i.e. =ROUND(6*530/2000) ) and then just run a simple SUM function at the bottom of the column.

Sum Function in Excel 2000

In reply to Sum Function in Excel 2000

Poster rated this answer

Sum Function in Excel 2000

In reply to Sum Function in Excel 2000

=roundup(6*530/2000,0) in each cell, then sum those values. an alternative is to use a white column and white font color on an adjacent cell to perform this rounding and then sum those numbers if you want to leave the formulas plain with no round command. You could conceivably create these rounds in the sum formula only, but that would make for a pretty lengthy formula if you have 34 rows to sum.

Sum Function in Excel 2000

In reply to Sum Function in Excel 2000

Poster rated this answer

Sum Function in Excel 2000

In reply to Sum Function in Excel 2000

I would use the ROUNDUP Function. In cell J32 Type: =ROUNDUP(J5+J6, 0) your answer will now be 30.

Sum Function in Excel 2000

In reply to Sum Function in Excel 2000

Poster rated this answer

Sum Function in Excel 2000

In reply to Sum Function in Excel 2000

This question was closed by the author

[Author]

Replies