If Statement with Two Different Logical Test

S

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!
 
R

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
 
T

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)
 
S

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.
 
S

Storm

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

Storm

This is great! Thanks Biff!

T. Valko said:
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)
Click to expand...
 
S

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

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,D1:D2,,))),1,2)

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

Storm

Gocha! Thanks again Biff.

T. Valko said:
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,D1:D2,,))),1,2)

So, for practical purposes, I would not use these!
Click to expand...
 
A

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)
 
G

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
[email protected]
 
Top