IF function

[georgie23]

Hi guys,

I would like to ask for your help with an If function.

What I would like to do can be seen below:

IF A>0 and B>0 see "X"
IF A>0 and B=0 see "W"
IF A=0 and B>0 see "Z"
If A=0 and B=0 see "Y"

From	To	Result
16	18	X
17	0	W
0	19	Z
0	0	Y

<tbody>
</tbody>

Thank you for the help in advance

Greg

 

[hiker95]

georgie23

Welcome to the MrExcel forum.

Something like this?

Excel 2007
	A	B	C
1	From	To	Result
2	16	18	X
3	17	0	W
4	0	19	Z
5	0	0	Y
Sheet1

Cell Formulas
Range	Formula
C2	=IF(AND(A2=0,B2=0),"Y",IF(AND(A2=0,B2>0),"Z",IF(AND(A2>0,B2=0),"W",IF(AND(A2>0,B2>0),"X",""))))

The formula in cell C1 copied down:
=IF(AND(A2=0,B2=0),"Y",IF(AND(A2=0,B2>0),"Z",IF(AND(A2>0,B2=0),"W",IF(AND(A2>0,B2>0),"X",""))))

 

[njimack]

Similar to hiker95's, but a bit shorter...
=IF(A2>0,IF(B2>0,"X","W"),IF(B2>0,"Z","Y"))

 

[Gerald Higgins]

Or perhaps

=IF(A1*B1>0,"X",IF(B1=0,IF(A1>0,"W","Y"),"Z"))

This relies on there never being any negative values in your data.

 

[Peter_SSs]

Assuming no negatives ..

njimack's formula could be condensed to

=IF(A2,IF(B2,"X","W"),IF(B2,"Z","Y"))


.. or for a longer but different approach

=LOOKUP((SIGN(A2)&SIGN(B2))+0,{0,1,10,11},{"Y","Z","W","X"})

 

[Drrellik]

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	5	0
2
3
4
5
6
7		.=IF(AND(A1>0,B1>0),"X","")			These 4 lines are how I would accomplish your single arguments
8	W	.=IF(AND(A1>0,B1=0),"W","")
9		.=IF(AND(A1=0,B1>0),"Z","")
10		.=IF(AND(A1=0,B1=0),"Y","")
11
12	W	.=IF(AND(A1>0,B1>0),"X",IF(AND(A1>0,B1=0),"W",""))							This is how I would put two together
13	W	.=IF(AND(A1>0,B1>0),"X",IF(AND(A1>0,B1=0),"W",IF(AND(A1=0,B1>0),"Z","")))								This is how I put three together.
14										You should be able to follow this
15										and add your fourth argument
16
17
18
19
20
Sheet1

I hope this helps you Don

 

[georgie23]

Thank you guys


It was very helpful


Really appreciate it


Have a great day !


Cheers

 

[Drrellik]

Wow, I will have to be quicker around here. when I first looked at your post Georgie there were no replys, by the time I posted my version of an answer your had 5 others.

 

[hiker95]

georgie23,

You are very welcome. Glad we could help.

Thanks for the feedback.

And, come back anytime.