Arrary Formulas using only IF

[ExecEd]

I'd like to create a formula that results in a specific value if data in a cell is equal to an item in a list. I think this is an array formula but can't get it to work.

Here's what I've got so far:

=IF(A1=$C$6:$C$8,1.295,1.1)

where C6:C8 is the list. I seem only to get 1.295 if A1 is equal to C6.

Thanks!

[Asala42]

Hiya,

How about a non-array formula?

=if(countif($C$6:$C$8,A1),1.295,1.1)

Adam

[Asala42]

oops..typo

=if(Countif($C$6:$C$8,A2)>0,1.295,1.1)

my bad

Adam

[Aladin Akyurek]

On 2002-04-22 14:15, Asala42 wrote:
oops..typo

=if(Countif($C$6:$C$8,A2)>0,1.295,1.1)

my bad

Adam

Adam,

I didn't see any typo, but now you've got "one".



Aladin

[ExecEd]

THANKS! And quick too!

[Yogi Anand]

On 2002-04-22 14:10, ExecEd wrote:
I'd like to create a formula that results in a specific value if data in a cell is equal to an item in a list. I think this is an array formula but can't get it to work.

Here's what I've got so far:

=IF(A1=$C$6:$C$8,1.295,1.1)



where C6:C8 is the list. I seem only to get 1.295 if A1 is equal to C6.

Thanks!

For an array formula, try

=IF(SUM((A1=$C6:$C$8)+0),1.295,1.1) ... with CTRL+SHIFT+ENTER

will give you the right result 1.295

[Mark W.]

On 2002-04-22 14:10, ExecEd wrote:
I'd like to create a formula that results in a specific value if data in a cell is equal to an item in a list. I think this is an array formula but can't get it to work.

Here's what I've got so far:

=IF(A1=$C$6:$C$8,1.295,1.1)

where C6:C8 is the list. I seem only to get 1.295 if A1 is equal to C6.

Thanks!

{=IF(OR(A1=$C$6:$C$8),1.295,1.1)}

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.