IF Function

[scottappleford]

Hi

I was wondering if anyone could help.

I am trying to write a formula and not sure whether the IF function is the correct one.

I would like the following:

If say cell A1 has a range of -10 to 10 then Cell A2 will return a 0, if it is between 10 and 15 retrun a 1 and if over 15 then return a 2, if between -10 and -15 then return -1 and if -15 or more than retrun a 2.

Thanks for your help.

 

[KenHigg]

I don't use excel that much but I suspect you could nest if statements to do this. Something like:

if(condition 1,true part,if(condition 2,true part,false part))

???

 

[namliam]

How about simply using Round(Yourcell/10) ??

 

[Bilbo_Baggins_Esq]

Hi

I was wondering if anyone could help.

I am trying to write a formula and not sure whether the IF function is the correct one.

I would like the following:

If say cell A1 has a range of -10 to 10 then Cell A2 will return a 0, if it is between 10 and 15 retrun a 1 and if over 15 then return a 2, if between -10 and -15 then return -1 and if -15 or more than retrun a 2.

Thanks for your help.

I think a nested IF will work, but you'll need to use AND as well.
First, I want to clarify one of your comments.

You wrote:
"If say cell A1 has a range of -10 to 10"
Did you mean to say "If say cell A1 is within a range of -10 to 10"?

You wrote:
"if -15 or more than retrun a 2"
Did you mean to say "if -15 or less than retrun a -2"?


If so, then yes, you can nest up to 7 levels in Excel 2003 (and earlier) and 64 levels in Excel 2007.

=IF(A1<-15,-2,IF(AND(A1>=-15,A1<-10),-1,IF(AND(A1>=-10,A1<10),0,IF(AND(A1>=10,A1<=15),1,2))))

You also may want to fine tune your ranges. Technically, they overlap as you wrote them, and Excel will evaluate to the first true statement.


EDIT:
Actually, you don't need the AND:

=IF(A1<-15,-2,IF(A1<-10,-1,IF(A1<10,0,IF(A1<=15,1,2))))

Sorry bout that brain fart

 

[Bilbo_Baggins_Esq]

How about simply using Round(Yourcell/10) ??

I considered the round() function, but he has a limit of -15 on the low side and +15 on the high side.

anything outside -25 to +25 values would round to values outside his desired range of -2 to +2.