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:

	week	Month	Month +1	Month -1	Forecast
a	0	0	0	500	500
b	0	200	201	300	200
c	100	0	0	101	100

<tbody>
</tbody>

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.