I submit a negative number and it subracts twice the value
I submit a negative number and it subracts twice the value on Numbers 08 running Lion
Mac OS X (10.6.2)
What is the formula you are using?
Regards,
Barry
16 replies
I am using SUM, simply. If I put the minus sign after the amount all is well. A minus sign before the amount and it subtracts the amount twice the value
Greg
I'm sorry, Greg, but I neglected to charge the batteries on my mind reading and decrypting device last night, and it isn't working all that well today.
So I'm able to work with only the information that you provide via words or pictures (eg. screen shots).
So far that has been minimal, to say the least.
Information on the version of Numbers you're using, the version of Mac OS X your using, what is being SUMmed, and ANYTHING ELSE that might possibly have a bearing on the issue might provide a clue or two to what is happening and why.
Regards,
Barry
Barry, I am sorry for making this request as it seems it must be a corrupt file. If I make a new sheet using same SUM formula all works well. Strange. But I am running Lion 10.7.2 and Numbers 08, ver 1.0.3.
But it is still a mystery why this one 1 column, 10 row SUM is messed up for negative entries.
Greg
Thanks for getting back on that, Greg. Still a mystery to me as well, but you are likely correct regarding it being a corrupted file. Happy to see you've got it working again, though.
Regards,
Barry
Barry, thanks.
But, not good. I tried to open another blank spreadsheet and I cannot get any negative entry to subtract the amount correctly.
Really do not care as I can fix it by putting the minus after the number. I quit.
Happy Holidays
Greg
I am intrigued by your issue, but I don't have Numbers '08.
In '09, placing the minus sign after the number in a cell makes it text, which SUM will ignore.
Generally, when a formula is not returning what I think it should, checking the format of the cells can help. Another area to check is in Language in System Preferences, has the Region been changed.
I know you say you quit, but if you can describe what you mean by "putting the minus after the number" it would help me to scratch this itch.
What exactly is in those 10 cells and is the SUM formula simply just referencing those cells, e.g. =SUM(B2:B12)?
If you don't care to spend more time, I understand. Best of luck.
A screen shot (cmd-shift-4) of your spreadsheet might be of help here, one that includes the cells being summed and shows the SUM formula in the formula bar.
Ronnie this is my last cell formula =SUM(B2:B12)
Each cell in the 10 in the column has no formula.
Thanks
Greg
Ronnie, if I enter a value I want negative in a cell in MS I put a minus in front of the number.
In my Numbers if I put a minus sign in front of the number that number is subtracted in twice its value from the total, but the number stays the same in the cell. for example -1000 is entered but 2000 is subtracted from my sum but -1000 appears in the cell.
To get around this I must enter 1000- and then all is fine. Strange. I have worked with spreadsheets before, even my old Appleworks app. Never saw this before. Using Numbers 08 ver 1.0.3, Lion 10.7.2
Each cell in the column contains no formula. Only formula is the total cell =SUM(B2:B12)
Thanks
Greg
Ronnie, I might have it. Long explanation:
If I create an absolutely new simple sheet, enter a colum of numbers and =SUM(B2:B12), all works.
But as soon as I paste the two columns(column of names), (column of information) from the original bad sheet into it seems these pastings corrupt my numeric column in some way. So it seems anything pasted from the original working sheet onto a good new sheet causes the new sheet to have the same problem.
Can something in the non-data columns affect my column of numbers that worked fine before pasting anything?
Greg
Greg,
There are a few things that you could try to strip the corrupting influences.
Paste and Match Style
or
Paste Values
or
Paste into TextEdit, Copy, Paste into Numbers
Regards,
Jerry
"But as soon as I paste the two columns(column of names), (column of information) from the original bad sheet into it seems these pastings corrupt my numeric column in some way. So it seems anything pasted from the original working sheet onto a good new sheet causes the new sheet to have the same problem."
You might be able to strip that corruption with one of these techniques:
- Export the original document as a CSV file. Open this in Numbers, then transfer the names and information columns (individuallly) using Copy/Paste
- Export the original document as an MS Excel file, Open that file in Numbers.
- Copy the names column from the original document to the Clipboard.
Select the first cell in the Names column of the new table.
Use an Applescript to convert the contents of the clipboard to Text and paste it into the new document.*
repeat with Information column
Regards,
Barry
* I've contacted Yvan regarding a minor edit to a script he wrote some time ago to do this in Pages.
Assuming it is not corruption but some other problem, can you isolate the offending cell by trying SUM(B5) (if the offending cell is B5)? Does it still give the wrong sum of that one cell? Actually, it might be quicker to put in C2 the formula =SUM(B2) and fill down to C12 and see if there is a cell that is causing this problem. Maybe you'll find a problem.
OK everyone, thanks for your many good suggestions.
This is now CLOSED. The solution is easy: just put the minus sign after the number and all is well.
I do not use the application enough to justify any more research time.
Thank you for your responses to what I thought was a simple issue. I guess it is not.
Happy Holidays
Greg
I submit a negative number and it subracts twice the value