Forum Discussion
James_Pipe
Jan 15, 2022Copper Contributor
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...
SergeiBaklan
Jan 15, 2022Diamond Contributor
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.