Forum Discussion

Brian0088's avatar
Brian0088
Copper Contributor
Aug 28, 2023

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.

  • 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.

    • Brian0088's avatar
      Brian0088
      Copper 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. 

      HansVogelaar 

  • 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?

Share