I am trying to find a mode of weekdays (Monday, Tuesday, etc.) within a date range by using the formula:
=ARRAYFORMULA(MODE(SUMPRODUCT(--(AgendaData!$A:$A>=A$6),--(AgendaData!$B:$B<=B$6),AgendaData!$F:$F)))
The error reads: "MODE cannot produce a result. No values occur more than once."
I think it has something to do with the F column being in text format. I want the result to show the mode of the weekday in text. I wonder what I am doing wrong here.
You can find the mode of text values by converting them to numbers, finding the mode, and converting back. It's convenient to use a three-column lookup table for this, e.g.,
Monday 1 Monday
Tuesday 2 Tuesday
Wednesday 3 Wednesday
Thursday 4 Thursday
Friday 5 Friday
Saturday 6 Saturday
Sunday 7 Sunday
Let's say this table is in columns I:K, and your data is in column F. Then the following finds its mode:
=vlookup(mode(arrayformula(iferror(vlookup(F:F, I:J, 2, false)))), J:K, 2)
Here, arrayformula(iferror(vlookup... is conversion to numeric, using the part I:J of the table, and ignoring blank cells and anything that isn't a day of week. After mode is applied, vlookup converts the number back to text using the part J:K of the table.