Find difference between 2 most recent weeks Excel

user_17162217 0
Viewed 69

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

offset if-statement excel-formula excel nested
4 Answers

If I understand correctly, then OFFSET is indeed what you can use, it just might seem momentarily confusing until you see it in action.

Try this demo on a new worksheet:

  • In the first 3 cells (A1:C1) enter "whatever" random values. I used 1,2,3, like this:
    ss

  • In D1 enter formula: =OFFSET(D1,0,-2)

  • In E1 enter formula: =OFFSET(E1,0,-2)

Note that both formulas refer to themselves. With most functions, this would create a circular reference error, but in this case, it's not looking for a value in that cell, just a starting point.

After the cell reference, the next two values in the function are the number of rows, and then number of columns, to offset by. Negative numbers would count "up" for rows, or "left" for columns, and positive numbers count "down" and "right".

Technically, OFFSET can take 2 other values too (for 5 parameters in total) but in this case, you can ignore the last 2. (They are to specify height and width, irrelevant unless you get into working with array formulas.)

So, the function in D1 is offsetting from cell D1 (itself), by zero rows (so neither up nor down), and "2 columns to the left".

ā†’ And now, if you drag D1 and E1 elsewhere (or insert columns/rows, etc) those 2 cells will automatically count the offset from the new location.

Just make sure you keep them as relative references (so D1, not $D$1) or else it will count from D1 regardless of where you move the formula.

...clear as mud? :-)

As an afterthought, here are a couple of other ways to visualize how OFFSET counts the cells to offset:
ss2 ss3

user_81127760

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.

user_97581940

Use a lookup table (Insert > Table from the ribbon, or alternatively create an ordinary range and use cell references instead of the table syntax in the formula below).

Value Abbreviation
Newbie N
Promise P
Waning promise Wp
... ...

From this, the value in your Wk2-Wk3 column can be calculated with a VLOOKUP(), XLOOKUP() or INDEX(MATCH()). For example,

=VLOOKUP(C3,LookupTable[[Value]:[Abbreviation]],2,FALSE)&"-"&VLOOKUP(D3,LookupTable[[Value]:[Abbreviation]],2,FALSE)

where LookupTable is the name of the table (you can set this on the "Table Design" ribbon tab).

user_47425700

Well, I did it like this, using text functions:

enter image description here

IF(IFERROR(FIND(" ",C3,1),0),LEFT(C3,1)&LOWER(MID(C3,FIND(" ",C3,1)+1,1)),LEFT(C3,1))&"-"&IF(IFERROR(FIND(" ",D3,1),0),LEFT(D3,1)&LOWER(MID(D3,FIND(" ",D3,1)+1,1)),LEFT(D3,1))

And, if you add the new week data columns appropriately then all you need to do is drag right...

enter image description here

user_49617000
Related