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

Occasional Visitor

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! 






3 Replies

Hi @cjp1993 




Assuming Excel 2021 or 365, in Q2:

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




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

rent change.JPG



If not 2021 or 365:


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)