Thread: 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

Try this in A1:

=IF(OR(B1={"X","Y","Z"}),"*","")

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"gimp" <dkuhl75@yahoo.com> wrote in message
news:1151279349.314459.225170@u72g2000cwu.googlegroups.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
>

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 news:1151279349.314459.225170@u72g2000cwu.googlegroups.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

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 news:OlcbOPLmGHA.1552@TK2MSFTNGP04.phx.gbl
*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" <dkuhl75@yahoo.com> wrote in message
> news:1151279349.314459.225170@u72g2000cwu.googlegroups.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

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" <fra68ve@InVento.it> wrote in message
news:OY$ikcLmGHA.4512@TK2MSFTNGP04.phx.gbl...
> 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 news:OlcbOPLmGHA.1552@TK2MSFTNGP04.phx.gbl
> *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" <dkuhl75@yahoo.com> wrote in message
> > news:1151279349.314459.225170@u72g2000cwu.googlegroups.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
>
>

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 news:1151279349.314459.225170@u72g2000cwu.googlegroups.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 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 news:1151285408.196654.265410@c74g2000cwc.googlegroups.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 news:1151279349.314459.225170@u72g2000cwu.googlegroups.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

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" <fra68ve@InVento.it> wrote in message
news:OY$ikcLmGHA.4512@TK2MSFTNGP04.phx.gbl...
> 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 news:OlcbOPLmGHA.1552@TK2MSFTNGP04.phx.gbl
> *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" <dkuhl75@yahoo.com> wrote in message
> > news:1151279349.314459.225170@u72g2000cwu.googlegroups.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
>
>

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?