If Statement with Two Different Logical Test

[Storm]

Hi there.

How would my if statement look like if I my logical test can be more than 1
value?

For example, if cell A1 = "apple" or "oranges", then return 1, else return
2. How can I show the apple or oranges logical test in equation form?

Thank you!

 

[Rick Rothstein \(MVP - VB\)]

Try this...

=IF(OR(A1={"apple","orange"}),1,2)

Note: I left both of these singular even though you made 'apple' singular
and 'oranges' plural. If you need that mixture, just add the 's' onto the
end of 'orange'. The key is, put all the text you want the cell to match
inside the curly braces, quoted and comma delimited.

Rick

 

[Luke M]

Use the OR function such as
=If(OR(A1="apple",A1="oranges"),1,2)

 

[T. Valko]

Try one of these:

=IF(OR(A1="apple",A1="orange"),1,2)

=IF(OR(A1={"apple","orange"}),1,2)

C1 = apple
C2 = orange

=IF(OR(A1=C1,A1=C2),1,2)

As an array formula** :

=IF(OR(A1=C1:C2),1,2)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Storm]

Thank you Luke. I was hoping there was I way I didn't have to specify the
"A1" reference cell at each instance...but hey, it works! Thanks for your
time.

 

[Aladin Akyurek]

=2-OR(A1="Apple",A1="Oranges")

 

[Storm]

Great! Not having to specify the reference cell "A1" at each instance is
efficient. Thanks for your time!

 

[Storm]

Thanks Aladin!

=2-OR(A1="Apple",A1="Oranges")

 

[Storm]

This is great! Thanks Biff!

Try one of these:

=IF(OR(A1="apple",A1="orange"),1,2)

=IF(OR(A1={"apple","orange"}),1,2)

C1 = apple
C2 = orange

=IF(OR(A1=C1,A1=C2),1,2)

As an array formula** :

=IF(OR(A1=C1:C2),1,2)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Storm]

Hi Bill...

What if...using the array method, I do have a list but I only want a select
few on that list? For example: C1 = apple; C2 = oranges; C3 = grapes. I
want my logical test to match either apple or grape, can I use this method?
I tried to edit the formula to show instead of =IF(OR(A1=C1:C2),1,2) I did
=IF(OR(A1=C1,C3),1,2)For some reason, even if A1 = C3, it still returned 2.
(note: when A1 = C1, it returned 1).

Thanks

 

[T. Valko]

can I use this method?

Not without making it overly complicated!

Array entered:

=IF(OR(A1=T(OFFSET(C1:C3,{0,2},,))),1,2)

D1 = 0
D2 = 2

=IF(OR(A1=T(OFFSET(C1:C3,D12,,))),1,2)

So, for practical purposes, I would not use these!

 

[Storm]

Gocha! Thanks again Biff.

Not without making it overly complicated!

Array entered:

=IF(OR(A1=T(OFFSET(C1:C3,{0,2},,))),1,2)

D1 = 0
D2 = 2

=IF(OR(A1=T(OFFSET(C1:C3,D12,,))),1,2)

So, for practical purposes, I would not use these!

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Gocha! Thanks again Biff.

 

[ASA]

I would be tempted to use a vlookup, create a table with your fruit in the
first column and a number in the second. then use formula

=vlookup (a1, fruittable, 2,0)

 

[Teethless mama]

=NOT(OR(A1={"apple","oranges"}))+1

 

[G Hunter]

What is a final number is let's say "56"? I would like this number to
trigger a dollar amount based on these range of numbers:

Group 1 Total Est. Hourly Usage (10-30 people) = $50
Group 2 Total Est. Hourly Usage (40-50 people) = $100
Group 3 Total Est. Hourly Usage (60-70 people) = $150
Group 4 Total Est. Hourly Usage (80-90 people) = $200
Group 5 Total Est. Hourly Usage (100-110 people) = $250
Group 6 Total Est. Hourly Usage (120+ people) = $300

So if my final number is "56 people" then how do I write the formula to be
able to work. Because the number may change each month. One month it may be
"26 people"?

So in basic words here is what I would like to say:
"If field A1 is between 10 & 39 then put $50 in field A2.
If field A1 is between 40 & 59 then put $100 in field A2.
If field A1 is between 60 & 79 then put $150 in field A2.
If field A1 is between 80 & 99 then put $200 in field A2.
If field A1 is between 100 & 119 then put $250 in field A2.
If field A1 is 120 and above then put $300 in field A2.

I don't know if I am explaining this correctly, but I would sure appreciate
some help. I am self taught so I don't know how to put this properly.
Thanks for any assistance.
G. Hunter
(e-mail address removed)

 

[Ken Wright]

See your other post.

Regards
Ken.......................