ExcelBanter - View Single Post - brainteaser: change a value by 90 (but answer must lie between 0-180 )

On 15 Nov 2005 11:51:42 -0800, wrote:

wrote:

bpeltzer wrote:

=mod(a1+90,180) will constrain the sum of a1 and 90 to the range of 0-180.

Thanks you - that's excellent.

but how do i contrain sum from 1 to 180 (not 0 to 180)

(so 90 degrees becomes 180 instead of 0)

Sorry - ignore this comment . Ron did it correctly:
=MOD(A1+90,180)+(MOD(A1+90,180)=0)*180

Ron if you have time, please can you explain your formula (if possible)

I just made '0' a special case.

The first part of the function:

=MOD(A1+90,180)

constrains the results to 0-179.

By changing the '0' to '180', we constrain the results to 1-180.

The function (MOD(A1+90,180)=0) will evaluate to TRUE or FALSE. In
Excelspeak, TRUE = 1 and FALSE =0. So if the function evaluates to '0', we
then have:

=0 + TRUE * 180
=0 + 1*180
=180.

If the first part evaluates to anything other than 0,then the second part
evaluates to 0*180 = 0 so it is ignored.

It's really the same as writing:

=MOD(A1+90,180) + IF(MOD(A1+90,180)=0,180,0)

--ron