Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
another complex =IF question
i was given the formula to use in cell A1 if cell b1 = x to make A1
either be blank if it said something other than x, or if it said x it would generate an (*). now is there a formula that can be used in the same senario if B1 had a series of possible values to generate a (*) in cell A1? thanks again |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
another complex =IF question
Try this in A1:
=IF(OR(B1={"X","Y","Z"}),"*","") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "gimp" wrote in message oups.com... i was given the formula to use in cell A1 if cell b1 = x to make A1 either be blank if it said something other than x, or if it said x it would generate an (*). now is there a formula that can be used in the same senario if B1 had a series of possible values to generate a (*) in cell A1? thanks again |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
another complex =IF question
If I well understand your problem, this formula is what you need:
=IF(OR(B1="x",B1="a",B1=3),"(*)","") You can have till 30 conditions inside the OR function, so 30 possible values in B1 to have an (*) in A1. Nel post oups.com *gimp* ha scritto: i was given the formula to use in cell A1 if cell b1 = x to make A1 either be blank if it said something other than x, or if it said x it would generate an (*). now is there a formula that can be used in the same senario if B1 had a series of possible values to generate a (*) in cell A1? thanks again -- (I'm not sure of names of menus, options and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
another complex =IF question
This is avery interesting formula, I never saw before such use of array in
an OR function... As far you know it's possible to use this way to shorten such a formula: =IF(AND(B1=5,B1<=10,something,something_else) I've tried with this: =IF(AND(B1&{"=","<="}&{5,10}),"*","") but I have a #VALUE! as a result. Nel post *Ragdyer* ha scritto: Try this in A1: =IF(OR(B1={"X","Y","Z"}),"*","") --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "gimp" wrote in message oups.com... i was given the formula to use in cell A1 if cell b1 = x to make A1 either be blank if it said something other than x, or if it said x it would generate an (*). now is there a formula that can be used in the same senario if B1 had a series of possible values to generate a (*) in cell A1? thanks again -- Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
another complex =IF question
It's called an "Array Constant"
From XL's Help file: Items that an array constant can contain · Array constants can contain numbers, text, logical values such as TRUE or FALSE, or error values such as #N/A. · Numbers in array constants can be in integer, decimal, or scientific format. · Text must be enclosed in double quotation marks, for example "Tuesday". · You can use different types of values in the same array constant, for example, {1,3,4;TRUE,FALSE,TRUE}. · The values in an array constant must be constants, not formulas. · Array constants cannot contain $ (dollar signs), parentheses, or % (percent signs). · Array constants cannot contain cell references. · Array constants cannot contain columns or rows of unequal length. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Franz Verga" wrote in message ... This is avery interesting formula, I never saw before such use of array in an OR function... As far you know it's possible to use this way to shorten such a formula: =IF(AND(B1=5,B1<=10,something,something_else) I've tried with this: =IF(AND(B1&{"=","<="}&{5,10}),"*","") but I have a #VALUE! as a result. Nel post *Ragdyer* ha scritto: Try this in A1: =IF(OR(B1={"X","Y","Z"}),"*","") -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "gimp" wrote in message oups.com... i was given the formula to use in cell A1 if cell b1 = x to make A1 either be blank if it said something other than x, or if it said x it would generate an (*). now is there a formula that can be used in the same senario if B1 had a series of possible values to generate a (*) in cell A1? thanks again -- Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
another complex =IF question
ok what is the longest formula that can be created like this. i maxed
it out came back saying formula too long. i prolly have 1000 or more possibilities. Franz Verga wrote: If I well understand your problem, this formula is what you need: =IF(OR(B1="x",B1="a",B1=3),"(*)","") You can have till 30 conditions inside the OR function, so 30 possible values in B1 to have an (*) in A1. Nel post oups.com *gimp* ha scritto: i was given the formula to use in cell A1 if cell b1 = x to make A1 either be blank if it said something other than x, or if it said x it would generate an (*). now is there a formula that can be used in the same senario if B1 had a series of possible values to generate a (*) in cell A1? thanks again -- (I'm not sure of names of menus, options and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
another complex =IF question
I told you you have max 30 conditions, so max 30 values, but if you have
1000 or more possibilities, you can try with VLOOKUP function in this way: =IF(ISNA(VLOOKUP(B1,$A$3:$A$29,1,0))),"","*") where $A$3:$A$29 is the range with all the possible conditions (one per each cell). Obviously you have to set your own range. Nel post oups.com *gimp* ha scritto: ok what is the longest formula that can be created like this. i maxed it out came back saying formula too long. i prolly have 1000 or more possibilities. Franz Verga wrote: If I well understand your problem, this formula is what you need: =IF(OR(B1="x",B1="a",B1=3),"(*)","") You can have till 30 conditions inside the OR function, so 30 possible values in B1 to have an (*) in A1. Nel post oups.com *gimp* ha scritto: i was given the formula to use in cell A1 if cell b1 = x to make A1 either be blank if it said something other than x, or if it said x it would generate an (*). now is there a formula that can be used in the same senario if B1 had a series of possible values to generate a (*) in cell A1? thanks again -- (I'm not sure of names of menus, options and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy -- (I'm not sure of names of menus, options and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
another complex =IF question
You can't build a formula like that, and why would you?
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Franz Verga" wrote in message ... This is avery interesting formula, I never saw before such use of array in an OR function... As far you know it's possible to use this way to shorten such a formula: =IF(AND(B1=5,B1<=10,something,something_else) I've tried with this: =IF(AND(B1&{"=","<="}&{5,10}),"*","") but I have a #VALUE! as a result. Nel post *Ragdyer* ha scritto: Try this in A1: =IF(OR(B1={"X","Y","Z"}),"*","") -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "gimp" wrote in message oups.com... i was given the formula to use in cell A1 if cell b1 = x to make A1 either be blank if it said something other than x, or if it said x it would generate an (*). now is there a formula that can be used in the same senario if B1 had a series of possible values to generate a (*) in cell A1? thanks again -- Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
another complex =IF question
Just another play to try ..
Assume all the 1000+ possible values are listed in say, Sheet2's col A Then in Sheet1, Put in A1: =IF(ISNUMBER(MATCH(B1,Sheet2!A:A,0)),"*","") Copy A1 down to return correspondingly -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "gimp" wrote: .. i probably have 1000 or more possibilities. .. i was given the formula to use in cell A1 if cell b1 = x to make A1 either be blank if it said something other than x, or if it said x it would generate an (*). now is there a formula that can be used in the same senario if B1 had a series of possible values to generate a (*) in cell A1? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Anybody Help with previous question | Excel Discussion (Misc queries) | |||
Complex query question | Excel Worksheet Functions | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
complex count question | Excel Worksheet Functions | |||
Jon Peltier - Pivot Table Result for yesterday's "Complex Chart" Question | Charts and Charting in Excel |