Help with OR/AND with IF

[Xirom431]

Hi,

Thank you in advance for the help. I have 4 Columns

2

1

Open

1

1

2

Open

1

If either column A or B is greater than 1, Column C will show Open. I got that far. However, I need help with D Column. I would like to show the different between Column A and B when Column C is Open. If A or B is blank, C and D should be blank.

Thanks,

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=if(c2="open",a2-b2,"")

 

[Joe4]

Try this (for an entry on Row 2):

=IF(AND(A2<>"",B2<>""),ABS(A2-B2),"")

 

[Xirom431]

Try this (for an entry on Row 2):

=IF(AND(A2<>"",B2<>""),ABS(A2-B2),"")

Hi Joe,

 

[Xirom431]

Try this (for an entry on Row 2):

=IF(AND(A2<>"",B2<>""),ABS(A2-B2),"")

Hi Joe,
I think I got the formula semi-correct. How do I make C & D columns blank if A & B are blank?

1	1	Closed	0
3	2	Open	0
		Closed	0

C Column formula.
=IF(ABS(H6-G6)>0,"Open","Closed")

D Column
=IF(I7="Open",ABS(H6-G6),0)

 

[Fluff]

Did you try the formula I suggested?
Also please update your profile as requested. It helps to know what functions you have available.

 

[Xirom431]

Did you try the formula I suggested?
Also please update your profile as requested. It helps to know what functions you have available.

I tried the formula but it did not come out correctly.

 

[Fluff]

In what way?

 

[Joe4]

Hi Joe,
I think I got the formula semi-correct. How do I make C & D columns blank if A & B are blank?

1	1	Closed	0
3	2	Open	0
		Closed	0

C Column formula.
=IF(ABS(H6-G6)>0,"Open","Closed")

D Column
=IF(I7="Open",ABS(H6-G6),0)

I thought you said you already got the column C formula working column working on your own, and you needed help with the column D formula.
I gave you the answer to that in my post, but for some reason, you did not use it! Your formula does not resemble mine!

Both Fluff and I have given you answers to try, so please try them.
Also note that your original post mentions columns C and D, but your formulas are using G and H.

And it isn't clear what you want to return if column A is something like "2" and column B is empty.
Should that return "Open" or "Closed"?

 

[Xirom431]

I thought you said you already got the column C formula working column working on your own, and you needed help with the column D formula.
I gave you the answer to that in my post, but for some reason, you did not use it! Your formula does not resemble mine!

Both Fluff and I have given you answers to try, so please try them.
Also note that your original post mentions columns C and D, but your formulas are using G and H.

And it isn't clear what you want to return if column A is something like "2" and column B is empty.
Should that return "Open" or "Closed"?

I finally got the formula for

Column C formula
=IF(A6="","",IF(ABS(A6-B6)=0,"Closed","Open"))
Column D formula
=IF(C6="","",IF(C6="Open",ABS(B6-A6),0))