Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to nest a left function within a sumif function? | Excel Worksheet Functions | |||
How do I use the TODAY function with the SUMIF function? | Excel Worksheet Functions | |||
SUMIF Function Inside SUMPRODUCT Function | Excel Worksheet Functions | |||
Can SUMIF function include AND function | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |