Adding OR to if formula

[Brew]

How do i update the following formula to add, OR if L6=0, then result is 5:
=IF(L6=4,1,MOD(L6+2,5))

 

[Yogi Anand]

How do i update the following formula to add, OR if L6=0, then result is 5:
=IF(L6=4,1,MOD(L6+2,5))

Hi dk:

One way would be to use ...

=IF(L6,IF(L6=4,1,MOD(L6+2,5)),0)

However, I hope you are not anticipating L6+2 resulting in becoming negative, because MOD() does not seem to work well then -- so in that case, I would suggest using ...

=IF(L6>0,IF(L6=4,1,MOD(L6+2,5)),0)

 

[Brew]

Yogi, neither solution met the desired result. The problem is the result can only be 1, 2, 3, 4, or 5, so in L7 increased the result from L6 by adding +2, however if L6=4, then L7=1 or if L6=5, then L7=2

 

[Peter_SSs]

Yogi, neither solution met the desired result. The problem is the result can only be 1, 2, 3, 4, or 5, so in L7 increased the result from L6 by adding +2, however if L6=4, then L7=1 or if L6=5, then L7=2

Brew, can you set out more clearly what the possible values in L6 can be and what the corresponding results you want are?
Is the formula only going to apply to L6? .. or will it be copied down or across?

 

[Brew]

The formula will be copied down and will not apply only to L6.
example:
L6=1, THEN L7=3
L6=2, THEN L7=4
L6=3, THEN L7=5
L6=4, THEN L7=2
L6=5, THEN L7=1

 

[Peter_SSs]

Brew

Try this
=CHOOSE(L6,3,4,5,2,1)

 

[Brew]

Thanks, that works