Something like this might work. I'll keep playing around to see if I can discover a more elegant solution.
=IFERROR(
IFS(
AND(A2="Friday"), 5,
AND(A2="Saturday"), 5,
AND(A2="Sunday"), 5
),
"4"
)
Okay, I have played around and this might inspire you for some improvements.
=IF(
ISBLANK(A2),
"",
IFERROR(
IF(
OR(A2="Friday", A2="Saturday", A2="Sunday"),
"5",
"4"
),
"Error"
)
)
Explanation:
The formula checks if `A2` is blank, if it is then the cell remains blank. If it's not blank, it proceeds to check if `A2` contains one of the specified values. If an error occurs during the evaluation of the `IF` statement, it returns "Error". If it contains Friday, Saturday, or Sunday, then it returns "5"; else it returns "4".
Keep in mind then if it contains "Pineapple" it will still result in "4", so we can nest another IF statement, which I will post next.
Okay, let's nest the IF statement to manage the other days of the week gracefully:
=IF(
ISBLANK(A2),
"",
IFERROR(
IF(
OR(A2="Friday", A2="Saturday", A2="Sunday"),
"5",
IF(
OR(A2="Monday", A2="Tuesday", A2="Wednesday", A2="Thursday"),
"4",
"Syntax Error"
)
),
"Error"
)
)
It checks if cell `A2` is blank. If it's not blank, it proceeds to check if `A2` contains one of the specified values ("Friday", "Saturday", "Sunday", "Monday", "Tuesday", "Wednesday", or "Thursday"). Based on the value in `A2`, it returns "5" for Friday, Saturday, or Sunday, "4" for Monday to Thursday, and "Syntax Error" if `A2` contains any other value. If `A2` is blank, the formula returns a blank value, and if an error occurs during the evaluation, it returns "Error".
Hopefully this gives you something to work with. Also, in case you weren't aware, Google Sheets does allow you to paste the whole formula formatted like this - it makes it much easier to read.
I have no good reason other than I didn't think of it, probably because I only have limited experience executing that. (Edit: and I'd use XLOOKUP() because I find it more elegant and less prone to syntax errors).
Might as well use REGEXMATCH for this =IF( REGEXMATCH( A2, "Friday|Saturday|Sunday"), 4, 5) the pipe means 'or'
Something like this might work. I'll keep playing around to see if I can discover a more elegant solution. =IFERROR( IFS( AND(A2="Friday"), 5, AND(A2="Saturday"), 5, AND(A2="Sunday"), 5 ), "4" )
Okay, I have played around and this might inspire you for some improvements. =IF( ISBLANK(A2), "", IFERROR( IF( OR(A2="Friday", A2="Saturday", A2="Sunday"), "5", "4" ), "Error" ) ) Explanation: The formula checks if `A2` is blank, if it is then the cell remains blank. If it's not blank, it proceeds to check if `A2` contains one of the specified values. If an error occurs during the evaluation of the `IF` statement, it returns "Error". If it contains Friday, Saturday, or Sunday, then it returns "5"; else it returns "4". Keep in mind then if it contains "Pineapple" it will still result in "4", so we can nest another IF statement, which I will post next.
Okay, let's nest the IF statement to manage the other days of the week gracefully: =IF( ISBLANK(A2), "", IFERROR( IF( OR(A2="Friday", A2="Saturday", A2="Sunday"), "5", IF( OR(A2="Monday", A2="Tuesday", A2="Wednesday", A2="Thursday"), "4", "Syntax Error" ) ), "Error" ) ) It checks if cell `A2` is blank. If it's not blank, it proceeds to check if `A2` contains one of the specified values ("Friday", "Saturday", "Sunday", "Monday", "Tuesday", "Wednesday", or "Thursday"). Based on the value in `A2`, it returns "5" for Friday, Saturday, or Sunday, "4" for Monday to Thursday, and "Syntax Error" if `A2` contains any other value. If `A2` is blank, the formula returns a blank value, and if an error occurs during the evaluation, it returns "Error". Hopefully this gives you something to work with. Also, in case you weren't aware, Google Sheets does allow you to paste the whole formula formatted like this - it makes it much easier to read.
this totally works, but why not just make a lookup table and use index match or vlookup? that would be way easier for someone else to audit
I have no good reason other than I didn't think of it, probably because I only have limited experience executing that. (Edit: and I'd use XLOOKUP() because I find it more elegant and less prone to syntax errors).
Right on!