If function, create a certain value within a number range.

[Roggan81]

Hi!

I need some help creating a formula to help making my scheduling more automatic. This is the problem I need help with:

I need a formula to create a value for me if a number in a specific cell is within a certain number range.

exemple:
If A1 is between 0 and 39, if true 0, if false 0.
If A1 is between 39 and 78, if true 30, if false 0.
If A1 is between 78 and 117, if true 60, if false 0.
If A1 is between 117 and 400, if true 90, if false 0.

I tried this, but it doesn´t work:
These formulas are written in B1 to E1

=if(39>A1>0; 0; 0)<A1<39; p ;0)< 0>
=if(78>A1>38; 30; 0)<A1<39; p ;0)< 0><A1<78; p ;0)< 30>
=if(117><A1<117; p ;0)< 60>A1>78; 60; 0)<A1<39; p ;0)< 0>
=if(400><A1<400; p ;0)< 90>A1>117; 90; 0)<A1<39; p ;0)< 0>

Maybe one of you smart guys/girls can help me on this one or suggest a better suitable funtion for me?

 

[Michael M]

Hi and welcome to the Board
Try

=IF(A1<39,0,IF(A1<78,30,IF(A1<117,60,IF(A1<400,90,0))))

 

[Roggan81]

Thanks for your fast reply and welcome!

But, excell does not accept the code you gave me.

 

[bosco_yip]

Or

=LOOKUP(A1,{0,0;39,30;78,60;117,90})

Regards

 

[Roggan81]

That doesn´t work either. Thanks for your effort...

 

[dado6612]

It seems you are using european format style of excel, just simply replace "," with ";" in formulas

=IF(A1<39;0;IF(A1<78;30;IF(A1<117;60;IF(A1<400;90;0))))</pre>

 

[Roggan81]

You are 100% correct. Your string works perfectly!

Problem solved and great thanks to everyone!