Jan 15 2022 09:27 AM
=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
The main idea for my break formula was:
Jan 15 2022 11:30 AM
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.