SUM IS INCORRECT

without specific details it is hard to guess.

Often the cell is not the same as the other in some way. Perhaps it is formatted as text, or there is a funny character in the cell. Try copying the data to a new table in a new document to double check the results.

If you can post the data then we can try too.

also...

you sum up a column with the sum() function.

the way I read your post is that you type the equal sign, then select the column.... like this:

This is NOT summing the column.

The way to sum a column is:

type the equal sign, then "sum(" then select the column then type ")":

=sum(D)

Like Wayne, I'm mystified by the issue and by the description.

Can you provide a screenshot that includes the result cell showing the result, the formula as it appears in the formula editor, and the column of numbers that is to be summed?

Procedure:

Double click the result cell to open the formula editor.

Drag the editor away from the result cell to show the result as calculated.

Take a screen shot that includes the three elements listed above.

• Place mouse pointer at top left of area to be included.
• Press shift-command-4. The pointer will change to a crosshair.
• Press and hold the mouse button and drg the pointer to the lower right corner of the area to be included.
• Release the mouse button.

Post the screen shot here:

• Click the cameral icon.
• Click Choose file.
• In the dialogue window, go to your Desktop, and choose the file (Screen shot... and the date and time taken)
• Click Choose.
• Click Insert Image.

The screenshot image will be inserted in your post.

Regards,

Barry

Hi Dominic,

Numbers has a useful feature where numbers are right justified and text is left justified.

By adjusting the alignment you have overridden this clue as to whether your numbers are numbers or text. You can undo this by clicking here

I think you will see that you "numbers taht are unsummed are being understood as text. I can't tell by looking at your screenshots why this is so- I don't see any non-numeric characters.

quinn

I think quinn has correctly identified the issue. Here's an example showing the same figures as your (Dominic's) table above.

Columns A and B have alignment set to Automatic. Column C is set to Center.

The entries in A14, A15 and A16 are numbers.

The entries in B14 and B15 are text, produced by typing a single quote (apostrophe) before the digits ( '250 ). B16 contains a number.

Entries in C14, C15 and C16 were produced by selecting the cells in these rows of Column B, then filling right into Column C. The results give another clue that two of the 'numbers' are actually text—note that the last digit of the 'numbers' in rows 14 and 15 have both increased by 1, while the number in row 16 has remained the same as the number in Column B.

The formula box (dragged away from it's cell to allow seeing both the formula and its result in the same screen shot) is for the selected cell, C17. Formulas in A17 and B17 are the same except for the column letters, which match the column of their cell.

All three sums in row 17 are correct, as SUM assigns a value of 0 to text strings.

Regards,

Barry

Just so you know. a numeric value can only contain the following characters:

0-9

.

-

+

anything else and the content of the cell is rightly considered something else

Hi Dominic,

If you 'number' cannot be formatted as a number then Numbers does not believe it is a number. As I said, from your screenshots it is not clear why this is so. It may be that the entry in those cells is somehow corrupt or that the text has been formatted to hide the non-numberness of the entry. My suggestion above was in the way of diagnosis. The feature is that numbers are aligned right and text to the left. If the column had not been aligned center it would have been clear from the outset that you had text that looked like numbers in the column. You have confirmed that the entries are not numbers by trying to format them as numbers and being rebuffed.

I think you need to reenter your text/number values. I would suggest deleating the content of the cell before you reenter your values. Do not cut/paste. After your delete you can format the cell as a numbers. If it reverts to automatic there is something deeply wrong with the table. If it is practical you should recreate the table. A quick way to identify those cells that need your attention is to use that far right alignment button that will left justify the cells that you need to edit.

Barry,

I hadn't realized that a ' would disappear when entered with a number. I like that. I have been able to reformat those cells as numbers so I don't think that is what has happened here.

quinn

Dominic writes:

"T Quinn - it is not a "helpful" feature it is a confusing and dangerous feature!!! Good job i spotted the error some time back so my figures were accurate (using a calculator cause apple failed me). why cant it give you a warning it does that rather than give us the wrong figures??

What is the purpose of "automatic" feature if it identifies a number as text? that's not automatic. It's wrong."

quinn said "useful" not "helpful," but either applies here.

With cell format set to "automatic," Numbers does NOT change a number to text or a text screen to a number. What it does it align text values to the left margin and align number values (and date/time values and duration values) to the right margin.

"why cant it give you a warning(?)"

Provided you have left the cells' text alignment setting at Automatic, 'it' does 'give you a warning' by aligning non-numeric values left instead of right (see column B of my sample table above). If you set the cell alignment to right, that setting overrides the Automatic setting (and turns off the warning).

The 'automatic' feature does NOT identify a number as text. Numbers identifies the type of data in the cell; "automatic" sets the cell format to fit the type of data identified. It is entirely possible for what looks like a number to actually be a text string.

Here's an image of one row of a Numbers table. Can you tell which cells contain numbers and which contain text?

Same table, same cells, same content. Which contain numbers? Which contain text?

Still the same table, cells and content. Which contain numbers? Which contain text?

In the first image, alignment for all cells is set to left. In the second image it's set to right. In the third the setting is automatic.

Which is most useful for determining the answer to the question above? Which migth be useful in trouble-shooting a table which is giving wrong unexpected results?

Regards,

Barry

I have fiddled with pages - i change a cell with a number in it to "number" on cell format and then it reverts back to automatic and is not considered a sum.

If the cell containing the 'number' won't accept formatting it's contents as a number, then there is something about that content that makes it unrecognizable as a number.

Is it because when i typed the figure i changed the font from roman to aerial??

No. The character doesn't change when you dress it up in different clothes, which is what you're doing when you change fonts.

if i re-type the figure then change it to numbers it seems to stay as numbers which changes it back to roman font and then works.

Two things happening here: when you retyped the 'number' replaced everything that was in the cell, including whatever was preventing the 'number' from being recognized as a number and the format setting that told Numbers to dress the contents in Arial, rather than Times New Roman—the default font for whatever style was the default for that table.

Moving the alignment from left to right makes no difference

The only difference that setting alignment right or left is that the contents of the cell should move left or right to match the change. Changing the alignment doesn't change the content.

Here's a Pages table containing a mix of numbers and text that looks like numbers. Alignment and font settings are different for each row (1 to 4). Row 5 contains formulas that sum each column. All sums are correct. (Remember that the numeric value of text is zero.)

Alignment settings and font used:

Row 1: Automatic, Helvetica

Row 2: Left, Times New Roman

Row 3: Center, Arial

Row 4: Right, Zapfino

Row 5: Automatic, Bank Gothic

Any more help please? am i going to have to re-type the figures into my pages document and leave it as roman font even though i prefer aerial and it will be out of sink with the text which is aerial?

As noted (and demonstrated) above, the font used should not affect the math.

Before retyping the 'numbers', try adding a new column to the right of the one containing the problem 'numbers' and using a formula to extract the numerical value from the data cells in the problem column.

=VALUE(B) will attempt to extract the numerical value in the cell in the same row of column B as the formula occupies in the new column. Change 'B' to match the column you are taking the values from.Place it next to the first data cell, and fill down to the cell beside the last data cell.

If the formula is successful, Select and Copy all of these cells, then select the first cell from which the data was copied and converted, go to the Edit menu and choose Paste Formula Results, replacing whatever was in the cells with the numeric value extracted from them.

CAUTIONS:

Test this on a disposable COPY of your file.

Do not use this method on cells containing a formula—only on cells where the data has been entered directly, or has been pasted in. Used on a cell containing a formula, the paste will replace the formula with a fixed value.

You may decide that re-typing the data as needed is the safer path.

Regards,

Barry