nested if

[Giancar]

Hi guys,

Not sure that title is correct, by the way I am stuck in one formula. Hopefully you can help.

This is the result I am looking for:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]week[/TD]
[TD]Month[/TD]
[TD]Month +1[/TD]
[TD]Month -1[/TD]
[TD]Forecast[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]500[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]0[/TD]
[TD]200[/TD]
[TD]201[/TD]
[TD]300[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]101[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Basically in "Forecast column" I want the first value available between column "week" and "month-1" that is not 0.
Anyone able to help?
Thank you

 

[AlexB123]

Assuming the intersection of "a" and "week" is the top left cell, "A1", Try:

=IF(A1<>0, A1, IF(B1<>0,B1,IF(C1<>0,C1,D1)))

 

[Giancar]

Assuming the intersection of "a" and "week" is the top left cell, "A1", Try:

=IF(A1<>0, A1, IF(B1<>0,B1,IF(C1<>0,C1,D1)))

Thanks, it is working.

 

[dreid1011]

Another option which would save time and formula length if you add more columns:

=INDEX(A1:D1,MATCH(TRUE,INDEX(A1:D1<>0,),0))

 

[Scott Huish]

You don't need that second INDEX function.

=INDEX(A2:D2,MATCH(TRUE,A2:D2<>0,0))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

 

[dreid1011]

Ah well, I found it that way and tested. Though it does save from having to CSE it.