IF function question

  • Thread starter Thread starter Dutilbug
  • Start date Start date
D

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.
 
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
 
Thanks, but that is giving me the same answer in both cells. I need a zero in
one cell.
 
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
 
If your teacher is offering you bonus points, what is the point if we do it
for you?
 
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.
 
Then just combine your formulae, with a nested IF

=IF(condition1, action1,IF(condition2, action2,action3))
 
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.
 
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
 
Back
Top