Search code examples
excelif-statementexcel-formulanestedoffset

Find difference between 2 most recent weeks Excel

I have the below table, and have created the Wk2-Wk3 column by using a nested IF/AND function

    =
IF(AND(C3="Newbie",D3="Promise"),"N-P",
IF(AND(C3="Newbie",D3="Sleepy"),"N-S",
...
IF(AND(C3="Broken Promise",D3="Newbie"),"Bp-N",
IF(AND(C3="Fallen Star",D3="Newbie"),"Fs-N"
)))))))))))))
ID Wk 1 Wk 2 Wk 3 Wk2-Wk3
1 Newbie Promise Waning Promise P-Wp
2 Newbie Sleepy Hibernating S-H
3 Promise Promise Star P-S
4 Promise Waning Promise Broken Promise Wp-Bp
5 Hibernating Hibernating Newbie H-N
6 Newbie Newbie Promise N-P

However, as the weeks progress I will be adding additional 'Wk' columns and therefore the last column will be changing to Wk3-Wk4, Wk4-Wk5, .. and so on.

I don't want to have to keep altering cell references. Is there a way to find the last column using a fixed formula that always uses the two latest weeks.

I tried using OFFSET, but cannot find a way to reference in relation to the formula cell. The following would not work:

 =
IF(AND(((OFFSET(E2,0,-2))="Newbie",((OFFSET(E2,0,-1))="Promise"),"N-P",
IF(AND(((OFFSET(E2,0,-2))="Newbie",((OFFSET(E2,0,-1))="Sleepy"),"N-S",
...
IF(AND(((OFFSET(E2,0,-2))="Broken Promise",((OFFSET(E2,0,-1))="Newbie"),"Bp-N",
IF(AND(((OFFSET(E2,0,-2))="Fallen Star",((OFFSET(E2,0,-1))="Newbie"),"Fs-N"
)))))))))))))

Using latest version of Excel, thank you in advance

Solution

  • Alternatively use INDEX() to retrieve the two cells to the left of the cell holding the formula:

    enter image description here

    Formula in E2:

    =REDUCE("",INDEX(2:2,COLUMN()-{2,1}),LAMBDA(a,b,TEXTJOIN("-",,a,PROPER(CONCAT(LEFT(TEXTSPLIT(b," ")))))))

    Looking at the logic in your IF() it seems you don't really need all these nested statements if you actually just need the first letter of each word. I did mimic this logic and used TEXTSPLIT() to retrieve every leftmost character of each word in the cells, concatenate them and use PROPER() before TEXTJOIN().

    This would, however, require access to ms365's insiders channel.