Need Excel IF function help.

Microsoft Office excel 2007 - upgrade
January 25, 2011 at 04:34:43
Specs: Windows 7
I have two columns. First column contains either "X" or "Y" or "Z"second column contains a numeric value "1" or "2" or "3". In the third column I'd like to have the result be that IF "X" AND "1" then "1" OR IF "X" AND "2" then "2" OR IF "X" AND "3" then "3" OR IF "Y" AND "3" then "4" OR IF "Y" AND "2" then "5" OR IF "Y" AND "3" then "6", IF "Z" AND "1" then "6" OR IF "Z" AND "2" then "6" OR IF "Z" AND "3" then "7".


See More: Need Excel IF function help.

Report •

#1
January 26, 2011 at 13:25:41
As afar as I can tell, this is what you are asking for.

1 - IF "X" AND "1" then "1"
2 - IF "X" AND "2" then "2"
3 - IF "X" AND "3" then "3"
4 - IF "Y" AND "3" then "4"
5 - IF "Y" AND "2" then "5"
6 - IF "Y" AND "3" then "6"
7 - IF "Z" AND "1" then "6"
8 - IF "Z" AND "2" then "6"
9 - IF "Z" AND "3" then "7"

Look at items 4 & 6. You have Y and 3 twice, once returning 4 and once returning 6.

Sorry, it's one or the other.

Just checking: Items 6, 7 & 8 all return "6". Is that correct?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 26, 2011 at 20:15:15
Sorry that was a typo error, it should be read as

1 - IF "X" AND "1" then "1"
2 - IF "X" AND "2" then "2"
3 - IF "X" AND "3" then "3"
4 - IF "Y" AND "1" then "4"
5 - IF "Y" AND "2" then "5"
6 - IF "Y" AND "3" then "6"
7 - IF "Z" AND "1" then "7"
8 - IF "Z" AND "2" then "8"
9 - IF "Z" AND "3" then "9"


Report •

#3
January 26, 2011 at 20:29:02
Assuming your letters are in A1 and your numbers are in B1, and based solely on the example data that you provided, this gets you what you asked for:

=IF(A1="X", B1, IF(A1="Y", B1+3, IF(A1="Z", B1+6, "")))

Now, tell us what you really want.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
January 27, 2011 at 01:05:18
Hi DerbyDad03,

Few changes in the requirement as given below:

If "X" & "1" then 1
If "X" & "2" then 0.5
If "X" & "3" then 0.5
If "Y" & "1" then 2
If "Y" & "2" then 1
If "Y" & "3" then 1
If "Z" & "1" then 4
If "Z" & "2" then 2
If "Z" & "3" then 1
If "NA" then 0


Report •

#5
January 27, 2011 at 05:38:05
Didn't I say "Now, tell us what you really want"?

Sometimes it doesn't help to simplify your examples because it leads to solutions that don't fit your needs.

BTW...instead of just posting the new requirements, you could have (should have) thrown a "Thanks" in there just to be polite.

Your last criteria isn't clear. Your first 9 criteria are checking values in 2 columns, you last one only checks 1. Which one?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
January 27, 2011 at 21:04:37
Hi,

I will give you example for last value. Lets say in column A1 I have a value either of one "X" "Y" "Z" or "NA". If it is "NA" then in the result column it shoud give the output as "0". There is no need to check column B1 for this condition or we are not bothered about whatever is there in column B1.

My Apologize to u, if I have hearted to u by any means.


Report •

#7
January 28, 2011 at 03:15:03
Here are 2 different options:

A brute force IF statement that checks for each condition:

=IF(A1="X",IF(B1=1,1,IF(OR(B1=2,B1=3),0.5)),
IF(A1="Y",IF(B1=1,2,IF(OR(B1=2,B1=3),1)),
IF(A1="Z",IF(B1=1,4,IF(B1=2,2,IF(B1=3,1,0))),
IF(A1="NA",0,""))))

The problem with such as long formula is that not only is it difficult to put together, it can be confusing to edit if you change your criteria (X, y or Z) or the desired results (.5, 1, etc.)

A better option might be this:

Put a table someplace in your worksheet that looks like this:

	F	G	H	I
1		X	Y	Z
2	1	1	2	4
3	2	0.5	1	2
4	3	0.5	1	1

Then use a combination of IF's and VLOOKUP's to return your desired results. By using this method, you can simply change the values in your table and never have to touch the formula.

=IF(A1=$G$1, VLOOKUP(B1,$F$2:$I$4,2),
IF(A1=$H$1, VLOOKUP(B1,$F$2:$I$4,3),
IF(A1=$I$1, VLOOKUP(B1,$F$2:$I$4,4),
IF(A1="NA", 0, ""))))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
January 30, 2011 at 20:57:29
Thanks a lot dear...for your kind efforts towards my query. It's really helpfull. It's really gud to have ur kind of people who helps other's without knowing them.

Report •

Ask Question