Simplify Formula Help

  • Thread starter Thread starter Daveo
  • Start date Start date
D

Daveo

Hi there,

Can anyone help me in simplifying the following formula:

=IF(AND('Shift Entry'!$I6="Y",(AND((OR('Shift Entry'!$E6<=1,'Shift
Entry'!$E6<2)))),('Shift Entry'!$F6>1)),1,IF(AND('Shift
Entry'!$V6="Y",(AND((OR('Shift Entry'!$R6<=1,'Shift
Entry'!$R6<2)))),('Shift Entry'!$S6>1)),1,IF(AND('Shift
Entry'!$AI6="Y",(AND((OR('Shift Entry'!$AE6<=1,'Shift
Entry'!$AE6<2)))),('Shift Entry'!$AF6>1)),1,IF(AND('Shift
Entry'!$AV6="Y",(AND((OR('Shift Entry'!$AR6<=1,'Shift
Entry'!$AR6<2)))),('Shift Entry'!$AS6>1)),1,0))))

Many thanks - David
 
Hi

=MIN(1,AND('Shift Entry'!$I6="Y",'Shift Entry'!$E6<2,'Shift
Entry'!$F6>1)+AND('Shift Entry'!$V6="Y",'Shift Entry'!$R6<2,'Shift
Entry'!$S6>1)+AND('Shift Entry'!$AI6="Y",'Shift Entry'!$AE6<2,'Shift
Entry'!$AF6>1)+AND('Shift Entry'!$AV6="Y",'Shift Entry'!$AR6<2,'Shift
Entry'!$AS6>1))
 
Another one

=OR(AND('Shift Entry'!$I6="Y",'Shift Entry'!$E6<2,'Shift
Entry'!$F6>1),AND('Shift Entry'!$V6="Y",'Shift Entry'!$R6<2,'Shift
Entry'!$S6>1),AND('Shift Entry'!$AI6="Y",'Shift Entry'!$AE6<2,'Shift
Entry'!$AF6>1),AND('Shift Entry'!$AV6="Y",'Shift Entry'!$AR6<2,'Shift
Entry'!$AS6>1))

, which returns TRUE/FALSE, or

=OR(AND('Shift Entry'!$I6="Y",'Shift Entry'!$E6<2,'Shift
Entry'!$F6>1),AND('Shift Entry'!$V6="Y",'Shift Entry'!$R6<2,'Shift
Entry'!$S6>1),AND('Shift Entry'!$AI6="Y",'Shift Entry'!$AE6<2,'Shift
Entry'!$AF6>1),AND('Shift Entry'!$AV6="Y",'Shift Entry'!$AR6<2,'Shift
Entry'!$AS6>1))*1

, which returns 0/1
 
Arvi Laanemets wrote...
Another one

=OR(
AND('Shift Entry'!$I6="Y",'Shift Entry'!$E6<2,'Shift Entry'!$F6>1),
AND('Shift Entry'!$V6="Y",'Shift Entry'!$R6<2,'Shift Entry'!$S6>1),
AND('Shift Entry'!$AI6="Y",'Shift Entry'!$AE6<2,'Shift Entry'!$AF6>1),
AND('Shift Entry'!$AV6="Y",'Shift Entry'!$AR6<2,'Shift Entry'!$AS6>1)
)
Click to expand...
....

If you want short as short as possible,

=OR((T(OFFSET('Shift Entry'!I6,0,{0,13,26,39}))="Y")
*(N(OFFSET('Shift Entry'!E6,0,{0,13,26,39}))<2)
*(N(OFFSET('Shift Entry'!F6,0,{0,13,26,39}))>1))
 
Back
Top