complicated IF THEN formulas

[jcasilio]

Hi:

I am a newbie that has been struggling to try to figure out how to
create some more complicated IF THEN formulas in Excel.

For example; I want to get a value of 2 for 1<A30 <= 200, 3 if
200<A30<=300, and 4 if 300<A30<=500.

I have spent hours trying to figure it out. I can't understand what I
am doing wrong. I have tried all of the Help files etc. etc. Maybe I am
just too dumb to get the syntax correct. Would a kind soul please send
me a message with the formula. Thank you very much.

(e-mail address removed).

 

[Guest]

I want to get a value of 2 for 1<A30 <= 200, 3 if
200<A30<=300, and 4 if 300<A30<=500.

To demonstrate the literal translation of your requirement:

=if(and(1<a30,a30<=200), 2,
if(and(200<a30,a30<=300), 3,
if(and(300<a30,a30<=500), 4, "")))

That might give you some insight into how to put together
complex conditional expressions. Your particular example
can be simplified:

=if(a30<=1, "", if(a30<=200, 2, if(a30<=300, 3,
if(a30<=500, 4, ""))))

 

[Robert]

Using the IF and AND functions:

=IF(AND(A1<=500,A1>300),4,IF(AND(A1<=300,A1>200),3,IF(AND(A1<=200,A1>1),2,0)))

 

[Bob Phillips]

=IF(A30<=1,"",IF(A30<=200,2,IF(A30<=300,3,IF(A30<=500,4,IF(....x,y)))

be aware that you can only get 7 nested IFs in a formula such as this. If
you need more, you need to do it a nother way, suc as a lookup table in
M1:Nn, like

0 2
200 3
300 4
500 5


and use

=VLOOKUP(A30,M1:N10,2)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Arvi Laanemets]

Hi

=MAX(2,INT((A30+99)/100))

 

[Bob Phillips]

Arvi,

The ranges are not the same size, so this doesn't work.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Arvi Laanemets]

Hi

I see now :-((
300 ... 500

Then maybe
=MATCH(A30,{0;201;301;501},1)