Jun 29 2022 07:24 AM
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,
Jun 29 2022 07:56 AM
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)
)
Jun 29 2022 08:21 AM
=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.
Jun 29 2022 09:47 AM
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)