How can I correctly use the if(and()) function so that I can then drag it down alongside a column of numbers? (Google Sheets)

user_18720889 0
Viewed 47

I use Google Sheets and I have tried to get this formula right for a while now but it always gives me "Undefined" for every cell I drag down to along a column of data.

If you're curious, this is for stocks' Relative Strength Index (RSI) – buying & selling rates.

Some number examples (down a column, starting with A1) are as follows:

  • 15
  • 26
  • 44
  • 67
  • 81
  • 60

How can I readjust this so that the following formula works? It goes down column B alongside column A (where the data is)!

=IF(AND(0<=$A1,$A1<20),"Risky",

IF(AND(20<=$A1,$A1<40),"Oversold",

IF(AND(40<=$A1,$A1<60),"Neutral",

IF(AND(60<=$A1,$A1<80),"Overbought",

IF(AND(80<=$A1,$A1<=100),"Too Risky","Undefined")))))
formula google-sheets if-statement function nested
2 Answers

Try below formula-

=IFS(AND(A1>=0,A1<20),"Risky",AND(A1>=20,A1<40),"Oversold",AND(A1>=40,A1<60),"Neutral",AND(A1>=60,A1<80),"Overbought",AND(A1>=80,A1<=100),"Too Risky",A1>100,"Undefined")

enter image description here

user_55147470

delete everything in your column and use in row 1:

=INDEX(IFNA(VLOOKUP(A1:A, 
 {0, "Risky";
  20, "Oversold";
  40, "Neutral";
  60, "Overbought";
  80, "Too Risky";
  101, "Undefined"}, 2, 1)))

update:

=INDEX(IF(A2:A="",,IFNA(VLOOKUP(B2:B*1, 
 {0, "Risky";
  20, "Oversold";
  40, "Neutral";
  60, "Overbought";
  80, "Too Risky";
  101, "Undefined"}, 2, 1))))

enter image description here

also see: https://webapps.stackexchange.com/q/123729/186471

user_56326290
Related