Nested IF/AND Statement

[buffgirl71]

I have the following data:

2004BAL 2005ACT 2006ACT
50000 0 10000
0 5000 0
10000 0 10000
5000 4000 0
0 5000 0
0 0 5000
15000 0 0
12000 0 0

I need an IF function that says
IF 2005 ACT = 0 AND 2006 ACT = 0 THEN 2004 BAL, ELSE NULL. The result
in the first six rows would be null, in the seventh row, it would be
15000 and in the eighth row it would be 12000.

I don't know how to put that into an Excel IF function.

Thank you for your help.

 

[Pete_UK]

Assuming your data is in columns A to C, with a header row, then in D2
enter this formula:

=IF(AND(B2=0,C2=0),A2,"")

and copy down column D for as many items as you have in the other
columns.

Hope this helps.

Pete

 

[joeu2004]

I have the following data:

2004BAL 2005ACT 2006ACT
50000 0 10000
0 5000 0
10000 0 10000
5000 4000 0
0 5000 0
0 0 5000
15000 0 0
12000 0 0

I need an IF function that says
IF 2005 ACT = 0 AND 2006 ACT = 0 THEN 2004 BAL, ELSE NULL. The result
in the first six rows would be null, in the seventh row, it would be
15000 and in the eighth row it would be 12000.

Assume that the above table is in A1:C9. That is, A1, B1 and C1 are
the title lines, and A2, B2 and C2 comprise the row that contains
50000, 0 and 10000, for example. Then D2 (for example), you might
write:

=if(and(B2=0,C2=0), A2, "")

Copy that formula down through D9. You will see that the relative
references B2, C2 and A2 change as you would want them to.

HTH

 

[buffgirl71]

Perfect! Thank you!!

Assuming your data is in columns A to C, with a header row, then in D2
enter this formula:

=IF(AND(B2=0,C2=0),A2,"")

and copy down column D for as many items as you have in the other
columns.

Hope this helps.

Pete

 

[buffgirl71]

Thanks to you and the other person who responded. This formula works
perfectly.

 

[Pete_UK]

You're welcome.

Pete

Perfect! Thank you!!