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

Occasional Visitor

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

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.

Any help greatly appreciated!

Thanks,

3 Replies

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

Hi @cjp1993

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)
)``````

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

``=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.

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

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)``