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

Question

Hi all,&nbsp;&nbsp;Trying to figure out a formula that I can apply to a large dataset&nbsp; where I have a a set of columns representing months, and the rows representing rent amounts for properties.&nbsp;&nbsp;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.&nbsp;&nbsp;Any help greatly appreciated!&nbsp;&nbsp;Thanks,&nbsp;&nbsp;&nbsp;&nbsp;

lorenzo · Answer

Hi&nbsp;cjp1993&nbsp;&nbsp;&nbsp;Assuming Excel 2021 or 365, in Q2:=LET(
    NoZero,    FILTER(B2:O2,B2:O2&lt;&gt;0),
    LastValue, INDEX(NoZero,,COLUMNS(NoZero)),
    IF(LastValue &lt;&gt; P2, LastValue, P2)
)&nbsp;

oliverscheurich · Answer

cjp1993&nbsp;=INDEX(B2:G2,,LARGE(IF((B2:G2&gt;0)*(B2:G2&lt;&gt;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.

lorenzo · Answer

cjp1993&nbsp;&nbsp;If not 2021 or 365:in&nbsp;Q2 as a regular formula (Enter only):=IF(LOOKUP(2,1/(B2:O2&lt;&gt;0),B2:O2) &lt;&gt; P2, LOOKUP(2,1/(B2:O2&lt;&gt;0),B2:O2), P2)

Forum Discussion

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

3 Replies

Resources