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
Help with formula

 5StarLounger
 Posts: 1185
 Joined: 24 Jan 2010, 12:02
 Location: Wales, UK.
Help with formula
Nathan
There's no place like home.....
There's no place like home.....

 Administrator
 Posts: 77256
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands
Re: Help with formula
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.
=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.
Regards,
Hans
Hans

 5StarLounger
 Posts: 1185
 Joined: 24 Jan 2010, 12:02
 Location: Wales, UK.
Re: Help with formula
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?
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?
Nathan
There's no place like home.....
There's no place like home.....

 Administrator
 Posts: 77256
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands
Re: Help with formula
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)))
To start from another column, for example form column B:
=INDEX(B2:Z2,MAX((B2:Z2<>0)*(COLUMN(B2:Z2)COLUMN(B2)+1)))
Regards,
Hans
Hans

 5StarLounger
 Posts: 1185
 Joined: 24 Jan 2010, 12:02
 Location: Wales, UK.
Re: Help with formula
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.
If the value is null, test prior column(s). If the first column is still null, then 0.
Nathan
There's no place like home.....
There's no place like home.....

 Administrator
 Posts: 77256
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands
Re: Help with formula
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)))
=LET(m,MAX((B2:Z2<>0)*(COLUMN(B2:Z2)COLUMN(B2)+1)),IF(m=0,"",INDEX(B2:Z2,m)))
Regards,
Hans
Hans