Forum Discussion
Conditional range
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.
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.
- Brian0088Copper Contributor
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.
You switched Days and Ranking in the XLOOKUP part.
- OliverScheurichGold Contributor
=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?