Ask Your Question

IF .... OR Statement

asked 2019-02-12 19:43:34 +0100

kik94 gravatar image

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

Thanks in advance.

edit retag flag offensive close merge delete


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

Mike Kaganski gravatar imageMike Kaganski ( 2019-02-12 19:46:57 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2019-02-12 20:04:34 +0100

Opaque gravatar image

=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")))

edit flag offensive delete link 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

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

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

Opaque gravatar imageOpaque ( 2019-02-13 19:19:13 +0100 )edit

answered 2019-02-13 10:39:23 +0100

libreofficeUser30872 gravatar image

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-02-12 19:43:34 +0100

Seen: 51 times

Last updated: Feb 13