Stuck trying to find a formula for most recent change in a series of numbers

Copper Contributor

Hi all, 

 

Trying to figure out a formula that I can apply to a large dataset  where I have a a set of columns representing months, and the rows representing rent amounts for properties. 

 

I need to figure out a formula to show the most recent previous rent if it is different to the current rent, not including months where the rent is 0 (property vacant). Example below with desired output.

 

Example Excel.png

 

Any help greatly appreciated! 

 

Thanks, 

 

 

 

3 Replies

Hi @cjp1993 

 

_Screenshot.png

 

Assuming Excel 2021 or 365, in Q2:

=LET(
    NoZero,    FILTER(B2:O2,B2:O2<>0),
    LastValue, INDEX(NoZero,,COLUMNS(NoZero)),
    IF(LastValue <> P2, LastValue, P2)
)

 

@cjp1993 

=INDEX(B2:G2,,LARGE(IF((B2:G2>0)*(B2:G2<>H2),COLUMN(B:G)),1)-1)

If you don't work with Office365 or 2021 you can try this formula. Enter the formula with ctrl+shift+enter.

rent change.JPG

@cjp1993 

 

If not 2021 or 365:

_Screenshot.png

in Q2 as a regular formula (Enter only):

=IF(LOOKUP(2,1/(B2:O2<>0),B2:O2) <> P2, LOOKUP(2,1/(B2:O2<>0),B2:O2), P2)