Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Column sum incorrect upon subtracting
I have a column with numbers that is totaled at the bottom. The Auto Sum
formula correctly adds all the cells. I tried to make two of the cells negative so that they would be subtracted. But, the cell amount is being doubled before it is subtracted from the total. So, if the cell amount is 50, for some reason 100 is being subtracted. I have all cells formatted as numbers, not general or text, and have checked for hidden formulas (there are none). Here is my example to show what is happening: =SUM(B2:B6) where B2 = 5 B3 = 5 B4 = 10 B5 = 15 B6 = 15 My total is correct at 50. But if I try to make B3 -5 and B4 -10, it is doubling each cell amount before it's subtracting, so it's subtracting 30 instead of 15. PLEASE HELP!! Thank you!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Column sum incorrect upon subtracting
Just making sure here... changing those cels means you end up with 20. What
result are you getting? "Drew" wrote: I have a column with numbers that is totaled at the bottom. The Auto Sum formula correctly adds all the cells. I tried to make two of the cells negative so that they would be subtracted. But, the cell amount is being doubled before it is subtracted from the total. So, if the cell amount is 50, for some reason 100 is being subtracted. I have all cells formatted as numbers, not general or text, and have checked for hidden formulas (there are none). Here is my example to show what is happening: =SUM(B2:B6) where B2 = 5 B3 = 5 B4 = 10 B5 = 15 B6 = 15 My total is correct at 50. But if I try to make B3 -5 and B4 -10, it is doubling each cell amount before it's subtracting, so it's subtracting 30 instead of 15. PLEASE HELP!! Thank you!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Column sum incorrect upon subtracting
Your result of 20 is correct.
The "delta" or amount of change from a +5 to -5 is 10, and delta from +10 to -10 is 20, so that's a total delta of 30, and 50-30 = 20. The OP is thinking that the total should only be reduced by -5 + -10, or 15 (I think) and is expecting a result of 35. To get that result, B3 and B4 need to both be zero, for a delta of -15. "Sean Timmons" wrote: Just making sure here... changing those cels means you end up with 20. What result are you getting? "Drew" wrote: I have a column with numbers that is totaled at the bottom. The Auto Sum formula correctly adds all the cells. I tried to make two of the cells negative so that they would be subtracted. But, the cell amount is being doubled before it is subtracted from the total. So, if the cell amount is 50, for some reason 100 is being subtracted. I have all cells formatted as numbers, not general or text, and have checked for hidden formulas (there are none). Here is my example to show what is happening: =SUM(B2:B6) where B2 = 5 B3 = 5 B4 = 10 B5 = 15 B6 = 15 My total is correct at 50. But if I try to make B3 -5 and B4 -10, it is doubling each cell amount before it's subtracting, so it's subtracting 30 instead of 15. PLEASE HELP!! Thank you!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Column sum incorrect upon subtracting
5+5+10+15+15 = 50
5-5-10+15+15 = 20 So what you have is 5+15+15 =35 then -5-10=-15 35-15=20 -- Regards, Peo Sjoblom "Drew" wrote in message ... I have a column with numbers that is totaled at the bottom. The Auto Sum formula correctly adds all the cells. I tried to make two of the cells negative so that they would be subtracted. But, the cell amount is being doubled before it is subtracted from the total. So, if the cell amount is 50, for some reason 100 is being subtracted. I have all cells formatted as numbers, not general or text, and have checked for hidden formulas (there are none). Here is my example to show what is happening: =SUM(B2:B6) where B2 = 5 B3 = 5 B4 = 10 B5 = 15 B6 = 15 My total is correct at 50. But if I try to make B3 -5 and B4 -10, it is doubling each cell amount before it's subtracting, so it's subtracting 30 instead of 15. PLEASE HELP!! Thank you!! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Column sum incorrect upon subtracting
"Drew" wrote:
My total is correct at 50. But if I try to make B3 -5 and B4 -10, it is doubling each cell amount before it's subtracting, so it's subtracting 30 instead of 15. "Subtracting 30 instead of 15" from what? I suspect you are saying that when you change B3 to -5 and B4 to -10, the sum becomes 20 -- which is correct, by the way. I think you are misinterpreting that as 50 - 30, and you are incorrectly expecting 50 - 15, i.e. 50 - 5 - 10. With the change, the sum is 5 + (-5) + (-10) + 15 + 15, which can be written 5 - 5 - 10 + 15 + 15. Noting that the middle -15 + 15 is zero, the remaining sum is 5 + 15, which is 20. ----- original message ----- "Drew" wrote in message ... I have a column with numbers that is totaled at the bottom. The Auto Sum formula correctly adds all the cells. I tried to make two of the cells negative so that they would be subtracted. But, the cell amount is being doubled before it is subtracted from the total. So, if the cell amount is 50, for some reason 100 is being subtracted. I have all cells formatted as numbers, not general or text, and have checked for hidden formulas (there are none). Here is my example to show what is happening: =SUM(B2:B6) where B2 = 5 B3 = 5 B4 = 10 B5 = 15 B6 = 15 My total is correct at 50. But if I try to make B3 -5 and B4 -10, it is doubling each cell amount before it's subtracting, so it's subtracting 30 instead of 15. PLEASE HELP!! Thank you!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtracting figures in a column | New Users to Excel | |||
Adding or subtracting numbers from a column | Excel Discussion (Misc queries) | |||
Subtracting two Subtotals in the same column | Excel Discussion (Misc queries) | |||
Incorrect Data Labels on Column Chart | Charts and Charting in Excel | |||
Excel column sumif is incorrect | Excel Discussion (Misc queries) |