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

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

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

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

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