Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding a negative number instead of substracting it
On my budget page, there are negative numbers when an expenditure is more
than the budgeted amount. I need these negative numbers to be added to the total sum instead of substracting it from the total sum. How do I do this? -- Cotton Girl |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding a negative number instead of substracting it
=sumif(a1:a10,""&0) - sumif(a1:a10,"<"&0)
You could also this: =sum(abs(a1:a10)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Cotton Girl wrote: On my budget page, there are negative numbers when an expenditure is more than the budgeted amount. I need these negative numbers to be added to the total sum instead of substracting it from the total sum. How do I do this? -- Cotton Girl -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding a negative number instead of substracting it
This creates a circular reference. I have three columns and several rows.
For example row 28, column B is my budgeted amount, column C is the actual, and column D is the sum, which is a negative number. This negative number needs to add to my grand total on column D row 1. I created your formula in row 28 column D and it created a circular reference. Do I need to create the formula in a different cell? -- Cotton Girl "Dave Peterson" wrote: =sumif(a1:a10,""&0) - sumif(a1:a10,"<"&0) You could also this: =sum(abs(a1:a10)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Cotton Girl wrote: On my budget page, there are negative numbers when an expenditure is more than the budgeted amount. I need these negative numbers to be added to the total sum instead of substracting it from the total sum. How do I do this? -- Cotton Girl -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding a negative number instead of substracting it
Yes.
-- David Biddulph Cotton Girl wrote: This creates a circular reference. I have three columns and several rows. For example row 28, column B is my budgeted amount, column C is the actual, and column D is the sum, which is a negative number. This negative number needs to add to my grand total on column D row 1. I created your formula in row 28 column D and it created a circular reference. Do I need to create the formula in a different cell? =sumif(a1:a10,""&0) - sumif(a1:a10,"<"&0) You could also this: =sum(abs(a1:a10)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Cotton Girl wrote: On my budget page, there are negative numbers when an expenditure is more than the budgeted amount. I need these negative numbers to be added to the total sum instead of substracting it from the total sum. How do I do this? -- Cotton Girl -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding a negative number instead of substracting it
And to add to David's response--don't put the formula in any cell that is used
in the formula. Cotton Girl wrote: This creates a circular reference. I have three columns and several rows. For example row 28, column B is my budgeted amount, column C is the actual, and column D is the sum, which is a negative number. This negative number needs to add to my grand total on column D row 1. I created your formula in row 28 column D and it created a circular reference. Do I need to create the formula in a different cell? -- Cotton Girl "Dave Peterson" wrote: =sumif(a1:a10,""&0) - sumif(a1:a10,"<"&0) You could also this: =sum(abs(a1:a10)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Cotton Girl wrote: On my budget page, there are negative numbers when an expenditure is more than the budgeted amount. I need these negative numbers to be added to the total sum instead of substracting it from the total sum. How do I do this? -- Cotton Girl -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Substracting from oldest date | Excel Discussion (Misc queries) | |||
Substracting Dates. | Excel Discussion (Misc queries) | |||
Substracting from a column | New Users to Excel | |||
substracting numbers in non contigous cells | Excel Discussion (Misc queries) | |||
2003= negative number&2004= negative number How Do I Calculate gro | Excel Worksheet Functions |