Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to get IF(OR) formula to recognize multiple cells
I am using the formula
=IF(OR(A5={"a","a1","a2","a3","a4","x","x1","x2"," x3","x4","t","t1","t2","t3","t4","h","h1","h2","h3 ","h4","r","l"}),"X","") to place an "X" in the destination cell. How would I adapt this formula to include cells B5 through E5, and would also include the numbers 1 through 4 as part of the criteria? -- rwfster |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to get IF(OR) formula to recognize multiple cells
I'd try to tackle this by using a range of values and a VLOOKUP instead of
hard coding each value. This will give you lots of flexibility for adding and removing any of the constraints. In this solution, I'm assuming that you mean if A1 OR B1 OR C1 OR D1 match the criteria, then you want an 'X' to show up in E1. Create a 2-column table in, say, H1:I50 (or however many constraints you have) that has each of your constraints with a 1 next to it. It looks like this: String Value x 1 a1 1 a2 1 a3 1 et. cetera This table could even be on a different sheet if you want to tuckit somewhere away. Then, assuming your strings are in A1:D1 that you want to check, place this formula in E1: =IF(IFERROR(VLOOKUP(A1,$H$2:$I$11,2,0),0)+IFERROR( VLOOKUP(B1,$H$2:$I$11,2,0),0)+IFERROR(VLOOKUP(C1,$ H$2:$I$11,2,0),0)+IFERROR(VLOOKUP(D1,$H$2:$I$11,2, 0),0)0,"X","") This will look up each of the cells A1:D1 in the table and if it's there, it will return a 1. If not, it errors out and returns a 0. Then it adds them all up and if at least one of them hit, you get your X! Hopefully, you're using Excel 2007 so the IFERROR function works, otherwise, this thing gets ugly so you might need to split it up. I like this better because it's flexible. You can add as many constraints as you want in the list. Happy calculating! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to get IF(OR) formula to recognize multiple cells
One easy way is to use one helper col where you gather/list
all the possibles in the criteria to check for, eg assume col Z is where all the criteria will be listed Then simply use: =IF(COUNTIF(Z:Z,A5),"X","") Works? Click YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- "rwfster" wrote: I am using the formula =IF(OR(A5={"a","a1","a2","a3","a4","x","x1","x2"," x3","x4","t","t1","t2","t3","t4","h","h1","h2","h3 ","h4","r","l"}),"X","") to place an "X" in the destination cell. How would I adapt this formula to include cells B5 through E5, and would also include the numbers 1 through 4 as part of the criteria? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to get IF(OR) formula to recognize multiple cells
Max, that formula works great for the A5 cell. How can it be adapted to
include evaluating cells A5 through E5? I have tried adding A5:E5 and A5&B5&C5&D5&E5 without success. Using those parameters the destination cell E5 is empty. -- rwfster "Max" wrote: One easy way is to use one helper col where you gather/list all the possibles in the criteria to check for, eg assume col Z is where all the criteria will be listed Then simply use: =IF(COUNTIF(Z:Z,A5),"X","") Works? Click YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- "rwfster" wrote: I am using the formula =IF(OR(A5={"a","a1","a2","a3","a4","x","x1","x2"," x3","x4","t","t1","t2","t3","t4","h","h1","h2","h3 ","h4","r","l"}),"X","") to place an "X" in the destination cell. How would I adapt this formula to include cells B5 through E5, and would also include the numbers 1 through 4 as part of the criteria? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to get IF(OR) formula to recognize multiple cells
I copied and pasted your formula then changed it to reflect 2 columns ranging
from P1:I27 but get #NAME? in the destination cell. If it matters iI am using excell 2003. -- rwfster "GSnyder" wrote: I'd try to tackle this by using a range of values and a VLOOKUP instead of hard coding each value. This will give you lots of flexibility for adding and removing any of the constraints. In this solution, I'm assuming that you mean if A1 OR B1 OR C1 OR D1 match the criteria, then you want an 'X' to show up in E1. Create a 2-column table in, say, H1:I50 (or however many constraints you have) that has each of your constraints with a 1 next to it. It looks like this: String Value x 1 a1 1 a2 1 a3 1 et. cetera This table could even be on a different sheet if you want to tuckit somewhere away. Then, assuming your strings are in A1:D1 that you want to check, place this formula in E1: =IF(IFERROR(VLOOKUP(A1,$H$2:$I$11,2,0),0)+IFERROR( VLOOKUP(B1,$H$2:$I$11,2,0),0)+IFERROR(VLOOKUP(C1,$ H$2:$I$11,2,0),0)+IFERROR(VLOOKUP(D1,$H$2:$I$11,2, 0),0)0,"X","") This will look up each of the cells A1:D1 in the table and if it's there, it will return a 1. If not, it errors out and returns a 0. Then it adds them all up and if at least one of them hit, you get your X! Hopefully, you're using Excel 2007 so the IFERROR function works, otherwise, this thing gets ugly so you might need to split it up. I like this better because it's flexible. You can add as many constraints as you want in the list. Happy calculating! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to get IF(OR) formula to recognize multiple cells
"rwfster" wrote:
Max, that formula works great for the A5 cell That's good. But pl click the YES in that response How can it be adapted to include evaluating cells A5 through E5? I have tried adding A5:E5 and A5&B5&C5&D5&E5 without success .. I wonder if you meant to do something like this In say F5: =IF(SUMPRODUCT(COUNTIF(Z$2:Z$100,A5:E5)),"X","") where Z2:Z100 contains the criteria, ie the list of the possible values, and A5:E5 is the range to be checked for the occurence of any of these criteria values Adapt to suit. As-is the expression can be copied down to return correspondingly for A6:E6, A7:E7, and so on Celebrate success? Click YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to get IF(OR) formula to recognize multiple cells
works like a charm, many thanks
-- rwfster "Max" wrote: "rwfster" wrote: Max, that formula works great for the A5 cell That's good. But pl click the YES in that response How can it be adapted to include evaluating cells A5 through E5? I have tried adding A5:E5 and A5&B5&C5&D5&E5 without success .. I wonder if you meant to do something like this In say F5: =IF(SUMPRODUCT(COUNTIF(Z$2:Z$100,A5:E5)),"X","") where Z2:Z100 contains the criteria, ie the list of the possible values, and A5:E5 is the range to be checked for the occurence of any of these criteria values Adapt to suit. As-is the expression can be copied down to return correspondingly for A6:E6, A7:E7, and so on Celebrate success? Click YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to get IF(OR) formula to recognize multiple cells
Welcome
-- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- "rwfster" wrote in message ... works like a charm, many thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cells don't recognize formula | Excel Worksheet Functions | |||
How do I recognize duplicate cells in multiple columns? | Excel Discussion (Misc queries) | |||
How do I format a formula to recognize multiple comparison factors | Excel Worksheet Functions | |||
How to format cells to recognize numbers like 1.1.1? | Excel Discussion (Misc queries) | |||
How to make a cell recognize multiple text strings? | Excel Worksheet Functions |