Ask Your Question
0

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

Comments

... 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
0

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

Comments

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
0

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

Stats

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

Seen: 51 times

Last updated: Feb 13