# IF .... OR Statement

hello,i need some help on office calc. Any idea how can i tranfer the condition bellow to calc ?

• if 0<=B1<=20 then C1=0
• if 21<=B1<=45 then C1=0.5
• if 46<=B1<=59 then C1=1

edit retag close merge delete

... and what if B1 = -1? 20.5? 45.5? 60?

( 2019-02-12 19:46:57 +0100 )edit

Sort by » oldest newest most voted

=IF(AND(B1>=0;B1<=20);0;IF(AND(B1>=21;B1<=45);0,5;IF(AND(B1>=46;B1<=59);1;"Not in any range")))

more

You can also use IFS function to get rid of nested IF statements and to make scaling easier if needed =IFNA(IFS(AND(B1>=0;B1<=20);0;AND(B1>=21;B1<=45);0.5;AND(B1>=46,B1<=59);1);"Not in any range") For contiguous range I suggest better to use VLOOKUP function combined with the lookup table. Some samples here and here

( 2019-02-13 17:13:08 +0100 )edit

Thanks for the hint to IFS and IFNA. This will make my own formulas more readable.

( 2019-02-13 19:19:13 +0100 )edit

To further your knowledge, there is a short video on this topic w/companion spreadsheet: 21- Libre Office - Calc, Open Office -- Calc, Excel Tutorial -- Conditional If And Or functions

more