functionif-statementgoogle-sheetsnestedformula
How can I correctly use the if(and()) function so that I can then drag it down alongside a column of numbers? (Google Sheets)
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")))))
Solution
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))))
