Forum Discussion

James_Pipe's avatar
James_Pipe
Copper Contributor
Jan 15, 2022

Broken syntax in formula

 

=IFERROR(IFS(([@Code]="SM"),1.5,AND(SUM(IF(IFERROR(LEFT([Name],SEARCH(" ",[Name])-1),0)=(LEFT([@Name], SEARCH(" ",[@Name],1)-1)),1,0))=1,XLOOKUP((LEFT([Name],SEARCH(" ",[Name])-1),0),Sunday2[Name],TRUE,0)),IFS(AND(4<=($D9-$C9),($D9-$C9)<6,XLOOKUP([@Name],AmendedHours[Names],AmendedHours[Break],0)<Rules!$V$5),Rules!$V$5,AND(6<=($D9-$C9),($D9-$C9)<8,XLOOKUP([@Name],AmendedHours[Names],AmendedHours[Break],0)<Rules!$V$6),Rules!$V$6,AND(8<=($D9-$C9),($D9-$C9)<9,XLOOKUP([@Name],AmendedHours[Names],AmendedHours[Break],0)<Rules!$V$7),Rules!$V$7,9<=($D9-$C9),Rules!$V$8,($D9-$C9)<4,0),AND(SUM(IF(IFERROR(LEFT([Name],SEARCH(" ",[Name])-1),0)=(LEFT([@Name], SEARCH(" ",[@Name],1)-1)),1,0))>1,(RIGHT(XLOOKUP([@Name],Sunday2[Name],Sunday2[Name],0),LEN([@Name])-SEARCH(" ",[@Name],1)))>"0"),IFS(AND(4<=($D9-$C9),($D9-$C9)<6,XLOOKUP([@Name],AmendedHours[Names],AmendedHours[Break],0)<Rules!$V$5),Rules!$V$5,AND(6<=($D9-$C9),($D9-$C9)<8,XLOOKUP([@Name],AmendedHours[Names],AmendedHours[Break],0)<Rules!$V$6),Rules!$V$6,AND(8<=($D9-$C9),($D9-$C9)<9,XLOOKUP([@Name],AmendedHours[Names],AmendedHours[Break],0)<Rules!$V$7),Rules!$V$7,9<=($D9-$C9),Rules!$V$8,($D9-$C9)<4,0),AND(4<=($D9-$C9),($D9-$C9)<6,XLOOKUP([@Name],AmendedHours[Names],AmendedHours[Break],0)<Rules!$V$5),Rules!$V$5,AND(6<=($D9-$C9),($D9-$C9)<8,XLOOKUP([@Name],AmendedHours[Names],AmendedHours[Break],0)<Rules!$V$6),Rules!$V$6,AND(8<=($D9-$C9),($D9-$C9)<9,XLOOKUP([@Name],AmendedHours[Names],AmendedHours[Break],0)<Rules!$V$7),Rules!$V$7,9<=($D9-$C9),Rules!$V$8,($D9-$C9)<4,0)

 

Been creating a rota spreadsheet for work and after altering one formula massively found there is an error with this somewhere that excel keeps flagging at me. I checked as far as I can tell the parenthesis of each part and have drawn a blank as to what is wrong. Wondering if it is nesting IFS inside IFS. Link for a copy of the doc I've put below if anyone can help figure it out. Any help is appreciated

https://1drv.ms/x/s!AuNtEnNrqupkgqIyKYW9KindiIVmlA?e=hhpUtb

The main idea for my break formula was:

  1. If 'SM' is added to the code section return 1.5 
  2. Check name in OT table and there is a single person with that first name -> return hour rules when longer than amended hours break
  3. Check name in OT table and if there are multiple people with that first name then check surname in OT matches  -> return hour rules when longer than amended hours break
  4. Else run the normal hour rules when longer than amended hours break
excel
Formulas and Functions
office 365

1 Reply

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 15, 2022

    James_Pipe 

    Here at least there is no syntax error

    =IFERROR(
   IFS(
      [@Code]="SM", 1.5,
      AND( SUM( IF(
               IFERROR( LEFT([Name], SEARCH(" ",[Name] )-1 ),0 ) =
               ( LEFT([@Name], SEARCH(" ",[@Name], 1 )-1 ) ),
               1,
               0)
            ) = 1,
            XLOOKUP(
               LEFT([Name],SEARCH(" ",[Name])-1),0,
               Sunday2[Name],
               TRUE,
               0
            )
      ),
         IFS( AND(
            4<=($D9-$C9),
            ($D9-$C9) < 6,
            XLOOKUP( [@Name], AmendedHours[Names], AmendedHours[Break], 0) < Rules!$V$5
            ),
            Rules!$V$5,
            AND(
               6<=($D9-$C9),
               ($D9-$C9)<8,
               XLOOKUP([@Name],AmendedHours[Names],AmendedHours[Break],0) <Rules!$V$6
            ),
            Rules!$V$6,
            AND(
               8<=($D9-$C9),
               ($D9-$C9)<9,
               XLOOKUP([@Name],AmendedHours[Names],AmendedHours[Break],0)<Rules!$V$7
            ),
            Rules!$V$7,
            9<=($D9-$C9), Rules!$V$8,
            ($D9-$C9)<4,0
         ),
      AND(
         SUM( IF(
               IFERROR(LEFT([Name],SEARCH(" ",[Name])-1),0)=
               (LEFT([@Name], SEARCH(" ",[@Name],1)-1)),
               1,
               0)
         ) > 1,
         (RIGHT(
               XLOOKUP([@Name],Sunday2[Name],Sunday2[Name],0),
               LEN([@Name])-SEARCH(" ",[@Name],1))
         ) > "0"
      ),
      IFS(
         AND(
            4<=($D9-$C9),
            ($D9-$C9)<6,
            XLOOKUP([@Name],AmendedHours[Names],AmendedHours[Break],0) < Rules!$V$5
          ),
          Rules!$V$5,
         AND(
            6<=($D9-$C9),
            ($D9-$C9)<8,
            XLOOKUP([@Name],AmendedHours[Names],AmendedHours[Break],0) < Rules!$V$6
         ),
         Rules!$V$6,
         AND(
            8<=($D9-$C9),
            ($D9-$C9)<9,
            XLOOKUP([@Name],AmendedHours[Names],AmendedHours[Break],0)<Rules!$V$7
         ),
         Rules!$V$7,
         9<=($D9-$C9), Rules!$V$8,
         ($D9-$C9) <4, 0
      ),
      AND(
         4<=($D9-$C9),
         ($D9-$C9)<6,
         XLOOKUP([@Name],AmendedHours[Names],AmendedHours[Break],0)<Rules!$V$5
      ),
      Rules!$V$5,
      AND(
         6<=($D9-$C9),
         ($D9-$C9)<8,
         XLOOKUP([@Name],AmendedHours[Names],AmendedHours[Break],0)<Rules!$V$6
      ),
      Rules!$V$6,
      AND(
         8<=($D9-$C9),
         ($D9-$C9)<9,
         XLOOKUP([@Name],AmendedHours[Names],AmendedHours[Break],0)<Rules!$V$7
      ),
      Rules!$V$7,
      9<=($D9-$C9), Rules!$V$8,
      ($D9-$C9)<4, 0
   ),
0 )

    It's hard to understand entire logic.

Resources