Help with formula

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Help with formula

Post by VegasNath »

Good evening all, I'd appreciate help with this formula please.

=IF(raw!F2=0,*,raw!F2)

*Being E2 if that is not 0, else D2 or the last column whereby not 0

Thanks
:wales: Nathan :uk:
There's no place like home.....

User avatar
HansV
Administrator
Posts: 78765
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Help with formula

Post by HansV »

For example:

=INDEX(A2:Z2,MAX((A2:Z2<>0)*COLUMN(A2:Z2)))

Adjust the range as needed. If you do not have Microsoft 365 or Office 2021, confirm the formula by pressing Ctrl+Shift+Enter.
Best wishes,
Hans

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Re: Help with formula

Post by VegasNath »

Thanks Hans, I hope you are well đź‘Ť

I can get the above to work using column A but I want to use column B as the starting column, which then gives me #REF. Any idea why that is?
:wales: Nathan :uk:
There's no place like home.....

User avatar
HansV
Administrator
Posts: 78765
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Help with formula

Post by HansV »

Hi Nathan, I'm fine. I hope you are too.
To start from another column, for example form column B:

=INDEX(B2:Z2,MAX((B2:Z2<>0)*(COLUMN(B2:Z2)-COLUMN(B2)+1)))
Best wishes,
Hans

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Re: Help with formula

Post by VegasNath »

Hi Hans, thanks. Is it possible to test for nulls? Essentially what I am trying to do is;

If the value is null, test prior column(s). If the first column is still null, then 0.
:wales: Nathan :uk:
There's no place like home.....

User avatar
HansV
Administrator
Posts: 78765
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Help with formula

Post by HansV »

Does this do what you want?

=LET(m,MAX((B2:Z2<>0)*(COLUMN(B2:Z2)-COLUMN(B2)+1)),IF(m=0,"",INDEX(B2:Z2,m)))
Best wishes,
Hans