sumif function
 

Is there a way I could sum a cell, make it a positive number and add 50, only
if the cell has a negative number in it?
--
Ksy

sumif function
 

triniqueen wrote:
Is there a way I could sum a cell, make it a positive number and add 50, only
if the cell has a negative number in it?

Not sure if this is what you mean:

=IF(A1<0,-A1+50,NA()) // works on one cell only

or, if you want to operate on a range (these are array formulae; commit
with Ctrl+Shift+Enter):

=SUM(IF($A$1:$A$4<0,-1*($A$1:$A$4)+50,0)) // adds 50 to each value
or
=SUM(IF($A$1:$A$4<0,-1*($A$1:$A$4),0))+50 // adds 50 to the result

sumif function
 

For one cell:

=IF(A1<0,ABS(A1)+50,A1)

for a column, one way:

=SUMIF(A1:A100,"0")-SUMPRODUCT(--(A1:A100<0),A1:A100-50)

another alternative:

=SUMPRODUCT(ABS(A1:A100))+COUNTIF(A1:A100,"<0")*50


In article ,
triniqueen wrote:

Is there a way I could sum a cell, make it a positive number and add 50, only
if the cell has a negative number in it?

sumif function
 

Thanks for your help. I'm trying to calculate amount of loan I need to take.
I would only need to take out the loan if the number is less than 0 but I
must also take out enough to where there is always a positive balance of $50.
I don't know if I explained that clearly enough.
--
Ksy


"smartin" wrote:

triniqueen wrote:
Is there a way I could sum a cell, make it a positive number and add 50, only
if the cell has a negative number in it?

Not sure if this is what you mean:

=IF(A1<0,-A1+50,NA()) // works on one cell only

or, if you want to operate on a range (these are array formulae; commit
with Ctrl+Shift+Enter):

=SUM(IF($A$1:$A$4<0,-1*($A$1:$A$4)+50,0)) // adds 50 to each value
or
=SUM(IF($A$1:$A$4<0,-1*($A$1:$A$4),0))+50 // adds 50 to the result

sumif function
 

Thanks alot, I went in an modified it a little and it worked great!!!!!! I
put =SUM(IF(A1<0,-1*(A1)+50,0)) . I got exactly what I was after!!!!!!
--
Ksy


"smartin" wrote:

triniqueen wrote:
Is there a way I could sum a cell, make it a positive number and add 50, only
if the cell has a negative number in it?

Not sure if this is what you mean:

=IF(A1<0,-A1+50,NA()) // works on one cell only

or, if you want to operate on a range (these are array formulae; commit
with Ctrl+Shift+Enter):

=SUM(IF($A$1:$A$4<0,-1*($A$1:$A$4)+50,0)) // adds 50 to each value
or
=SUM(IF($A$1:$A$4<0,-1*($A$1:$A$4),0))+50 // adds 50 to the result

sumif function
 

Thanks JE, I did try using ABS before but I didn't quite use it like you
showed. You guys have been great!!!
--
Ksy


"JE McGimpsey" wrote:

For one cell:

=IF(A1<0,ABS(A1)+50,A1)

for a column, one way:

=SUMIF(A1:A100,"0")-SUMPRODUCT(--(A1:A100<0),A1:A100-50)

another alternative:

=SUMPRODUCT(ABS(A1:A100))+COUNTIF(A1:A100,"<0")*50


In article ,
triniqueen wrote:

Is there a way I could sum a cell, make it a positive number and add 50, only
if the cell has a negative number in it?

sumif function
 

Actually, that's one too many functions. Try:

= ABS(A1) + 50*(A1<0)

In article ,
JE McGimpsey wrote:

For one cell:

=IF(A1<0,ABS(A1)+50,A1)

sumif function
 

Why have you used SUM there? It isn't doing anything in your formula.
Why not just
=IF(A1<0,-1*(A1)+50,0) or =IF(A1<0,-A1+50,0) or =IF(A1<0,50-A1,0) or
=(50-A1)*(A1<0)

Perhaps you should look at Excel help for the SUM function?
--
David Biddulph

"triniqueen" wrote in message
...
Thanks alot, I went in an modified it a little and it worked great!!!!!! I
put =SUM(IF(A1<0,-1*(A1)+50,0)) . I got exactly what I was after!!!!!!
--
Ksy


"smartin" wrote:

triniqueen wrote:
Is there a way I could sum a cell, make it a positive number and add
50, only
if the cell has a negative number in it?

Not sure if this is what you mean:

=IF(A1<0,-A1+50,NA()) // works on one cell only

or, if you want to operate on a range (these are array formulae; commit
with Ctrl+Shift+Enter):

=SUM(IF($A$1:$A$4<0,-1*($A$1:$A$4)+50,0)) // adds 50 to each value
or
=SUM(IF($A$1:$A$4<0,-1*($A$1:$A$4),0))+50 // adds 50 to the result