Conditional range

[Brian0088]

Hello to all,

I'm trying to create a spreadsheet that will keep track of my studying and preparation for a big exam. I'm using space repetition to learn. So in my spreadsheet I have the date that I studied each subject/ chapter and I would like to add different values to this date depending on the ranking I give it. So for example, if I studied a Economics today but I don't feel like I know the material well I'll rank it a 3 (in a scale 1-10) and it will add 2 days from my last study session whereas a  ranking of 10 will set it 21 days later.  I'm having a tough time with this.  Most of the conditional subject I've encounter deal with T or F but not 0=<x<3, 3=<x <7, 7=<x<10 sort of issues. Thank you.

[#2]

@Brian0088 

Create a range that lists the rankings and corresponding number of days to be added.

Convert the range to a table (Insert tab of the ribbon > Table).

It'd look like this (I made up the days, obviously).

You can then use XLOOKUP or VLOOKUP to calculate the next study date:

Formula in D2:

=B2+XLOOKUP(C2,Table2[Ranking],Table2[Days])

where Table2 is the name of the table.

[#3]

@Brian0088 

=DROP(REDUCE("",SEQUENCE(ROWS(B2:B5)),LAMBDA(x,y,VSTACK(x,IFERROR(EXPAND(INDEX(A2:A5,y,1),INDEX(B2:B5,y,1),1),INDEX(A2:A5,y,1))))),1)

Is this what you basically want to do? If so it should be possible to add criteria for 0=<x<3, 3=<x <7, 7=<x<10. Btw. what does "T or F" mean?

[#4]

Thanks!!

So I did what you said and I can only do it for one date then I'll get the error. I'm trying to go across with this formula. How do I remedy this? I appreciate your assistance. 

 

 

@HansVogelaar 

[#5]

@Brian0088 

You switched Days and Ranking in the XLOOKUP part.