IF function question

[Dutilbug]

My teacher has offered bonus points if we can use the same formula in two
different cells to obtain different results. This is for an accounting
worksheet with entries similar to:

Columns E&F Columns G&H Columns I&J
Debit Credit Debit Credit Debit Credit
5 0 5 0 10
0
0 3 7 0 4
0
0 3 0 7 0
10

My column I formula: =IF(E11-F11+G11-H11>0,+E11-F11+G11,0)
My column J formula: =IF(E11-F11+G11-H11<0,-(+E11-F11+G11-H11),0)

Only one of the I & J columns can have an amount above zero in it and the
other has to be zero. The formula in both I and J must be the same.

 

[Dutilbug]

Well, the formatting didn't stick so I guess the numbers don't make any
sense. There should be one number under each debit and credit. for a total of
six numbers in each row.
The first row should be 5, 0, 5, 0, 10, 0
The second row should be 0, 3, 7, 0, 4, 0
The third row should be 0, 3, 0, 7, 0, 10

 

[Ashish Mathur]

Hi,

You can try this
=IF(E11-F11+G11-H11>0,+E11-F11+G11,IF(E11-F11+G11-H11<0,-(+E11-F11+G11-H11),0))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Dutilbug]

Thanks, but that is giving me the same answer in both cells. I need a zero in
one cell.

 

[Ashish Mathur]

Hi,

Your question is not very clear. Anyways, try this

=IF($E11-$F11+$G11-$H11>0,+$E11-$F11+$G11,IF($E11-$F11+$G11-$H11<0,-(+$E11-$F11+$G11-$H11),0))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Bob Phillips]

If your teacher is offering you bonus points, what is the point if we do it
for you?

 

[Dutilbug]

Well Bob, first, my teacher doesn't mind when we work with other people to
figure things out. I have helped a lot of people in my class with other
projects. Second, I would be happy with just being pointed in the right
direction. I want to understand the why of it, not just copy and paste a
formula and be done with it.

 

[Bob Phillips]

Then just combine your formulae, with a nested IF

=IF(condition1, action1,IF(condition2, action2,action3))

 

[Dutilbug]

Thanks. I didn't mean to sound rude with my previous post, but I am an
excellent student and I work very hard. I have tried nesting. My problem is
that I need an answer to be either in cell I11 or J11, but not both. For the
first instance of my problem I have:
Cell E11 is $1,149.19
Cell F11 is $0.00
Cell G11 is $0.00
Cell H11 is $6,064.11
I need I11 to be $0.00
I need J11 to be $4,914.92

When I nest the formulas
=IF($E11-$F11+$G11-$H11>0,+$E11-$F11+$G11,IF($E11-$F11+$G11-$H11<0,-(+$E11-$F11+$G11-$H11),0))
it puts $4,914.92 in both I and J. I need $0 in I and $4,914.92 in J.
I have been told this is possible, but I keep hitting walls.

 

[Bob Phillips]

The way to do it is to test for the column as well as whether the sum is
positive or negative using the AND function.

=IF(AND(COLUMN()=9,(sum)>0),(sum),IF(AND(COLUMN()=10,(sum)<0),-(sum),0))

that should transfer across well