A nasty nested if

[Tom]

Good people of M.P.E.,

I have this formula on a timesheet

=IF(AND(Q38="-",Q47="+"),IF(R47-R38>R45,R47-R38,R38-R47),IF(AND(Q38="+",Q47="+"),IF(R47+R38>Q48,Q48,R47+R38),IF(AND(Q47="-",
Q38="+"),IF(R38-R47<0,R47-R38,IF(R38-R47>Q48,Q48,R38-R47)),R47+R38)))

It is to calculate and to carry flexi-time hours (up to a set maximum in
Q48) from one months time sheet to the next. It works as it is, however with
7 IF's and 3 AND's I'm sure someone will know a better way of doing this.

Any takers?

(Excel 2007 on XP btw)

Tom

 

[Pete_UK]

Well, to start you on your way, you could replace this part:

IF(R47+R38>Q48,Q48,R47+R38)

(4th IF) with this:

MIN(Q48,R47+R38)

and similarly, this part (7th IF):

IF(R38-R47>Q48,Q48,R38-R47)

with this:

MIN(Q48,R38-R47)

Although, if you are using XL2007 then you do not have the limitation
of 7 nested IFs that earlier versions suffered from.

Hope this helps.

Pete

 

[Pete_UK]

To take you a bit further, you can get rid of the ANDs by
concatenation.

Replace this:

AND(Q38="-",Q47="+")

with this:

Q38&Q47="-+"

Similarly with the other two ANDs.

Hope this helps.

Pete

 

[Roger Govier]

Hi Tom

Try
=IF(AND(Q38="-",Q47="+"),ABS(R38-R47),
IF(AND(Q38="+",Q47="+"),MIN(Q48,R47+R38),
IF(AND(Q47="-",Q38="+"),MIN(Q48,ABS(R38-R47)),
R47+R38)))

 

[helene and gabor]

Hello Tom,

How about:

=CHOOSE(BIN2DEC( --(Q38>0)& --(Q47>0)),ABS(R38-R47),MIN(Q48,ABS(R38-R47)),MIN(Q48,R47+R38))

Regards,

Gabor Sebo