Beginner needs help with formula in excel

**CGSORS** · 10-02-2014, 10:22 AM

I am a very basic computer user, but am trying to formulate cells in Excel. I am using Office 2011 on a Mac.

I have cells in column J that are both positive and negative. In column C, I'd like to do the following: if the value in column J is positive, then show 0 in the same row of column c; if the value is a negative, then enter the same number but as a positive. So if J4 is 600, I'd like for C4 to display 0, if J4 is (600), I'd like for it to display 600.

Thank you very much for any assistance you can give me!

**finkel82** · 10-02-2014, 10:28 AM

HI CGSORS,

I'm not sure if the Mac version is different but assuming it's not then you could use in Column C (assuming row 2):

=if(J2<0,0,J2)

What you're saying is "If J2 is less than 0 then return a 0 otherwise return what is in J2".

Hope that helps
Alex

**Speshul** · 10-02-2014, 10:32 AM

I think you had it backwards finkel82,

=IF(J4>0,0,ABS(J4))

If J4 is larger than 0, return a 0, otherwise return the POSITIVE version of J4

The ABS function returns a numbers position relative to zero. So, =ABS(100) would be 100 since it is 100 away from zero, and =ABS(-50) would be 50, because it is 50 away from zero. In your case, you have ABS(J4), which will return J4's value relative to zero.

here is more on the IF function;

=IF(logical_test,value_if_true,value_if_false)

logical_test: This is where you put the test. The rest can be any statement that results in a True or False result, for example A1>15. If A1 is in fact larger than 15, the result will be True. Common operators would be
.

">" Greater Than
"<" Less Than
"<>" NOT equal to
">=" Greater than or equal to
"<=" Less than or equal to
"=" Equal to

value_if_true: This is the result if logical_test returns a TRUE value.
value_if_false: As you probably guessed, this is the result if logical test returns a FALSE value.

A full example of the formula would be:

=IF(A1=10,A1+10,B1)

Now, if cell A1 is equal to 10, the result of the formula will be 20. Why? Because if A1=10, the value_if_true will occur, resulting in 10+10
If A1 is NOT equal to 10, the result of the formula will be whatever the value of B1's cell is

Remember, any one of the three inputs for the IF function can be another formula, for example, here are two IF's nested:
=IF(A1=10,A1+10,IF(A1>10,A1,B1-10))
The value_if_false is another IF function, that will only be executed by Excel if the previous IF statement (A1=10) is False