If and statements libre Calc

English
#1

Hi guys this shouldnt be too hard for some of you but I am boggled seems like it should have been so easy.

Here is my scenario

lets say I have 6 cells that I am using A1 - A6

A1 is a number that from 0 to 100 say that is always changing.
a2 is my cell I have trouble with will explain in a min.

a3 says green
a4 says red
a5 says blue
a6 says black

what i am trying to do in a2 is say that - If a1 is betwwen 0-25 display a3 but if it is betwwen 26 and 50 display a4 but if it is between 51-75 display a5 and lastly if between 76-100 display a6…tried nested ifs and tried AND statements which was closer to what i want but only display true or false…so frustrated can someone please help.

thanks so much

#2

No, it’s not so easy it’d be just for anyone.

#3

=IF(A1<26;A3;(IF(A1<51;A4;(IF(A1<76;A5;(IF(A1<101;A6;""))))))) should work.

#4

=IF(A1="";"";(IF((A1>=0)AND(A1<26);A3;(IF(A1<51;A4;(IF(A1<76;A5;(IF(A1<101;A6;"")))))))) this accounts for NULL and other than 0-100.

#5

I think It can be a little easier using an inner array with MATCH()
¬=IF(AND(A1>0;A1<101);INDEX(A3:A6;MATCH(A1;{1;26;51;76}));"out of range")

or simplifying a bit @Ratslinger’s formula
¬=IF(AND(A1>0;A1<101);INDEX(A3:A6;(A1<26)+(A1<51)+(A1<76)+(A1<101));"out of range")

#6

Thanks @mariosv - always good to learn better ways! I do like the “out of range”.

#7

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.