IF function question

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.
 
D

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
 
D

Dutilbug

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

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
 
B

Bob Phillips

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

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.
 
B

Bob Phillips

Then just combine your formulae, with a nested IF

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

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.
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top