Excel 2016 for Mac中FindMax函数仅部分子区域可用问题咨询

Hey there! It’s super frustrating when a function works sometimes but throws errors other times—let’s break down why your FindMax is acting up with different MoviesByGenre ranges.

Common Causes of the #VALUE! Error #

• Dirty data in your range: If your MoviesByGenre range includes blank cells, error values (like #N/A or #DIV/0!), or non-text entries (numbers, dates), FindMax might choke when it hits these, especially as the range grows and the odds of encountering bad data go up.
• Excel 2016 Mac’s array formula limitations: Older Mac Excel versions have spotty support for complex array operations. If your FindMax relies on nested array functions (like MODE(IF(...))), larger ranges could trigger overflow or parsing errors that don’t show up with smaller datasets.
• Unhandled edge cases in your function logic: If FindMax doesn’t account for ties (multiple genres with the same highest count) or fails to adjust references when the range size changes, it might flip between working and erroring randomly.

Fixes to Try #

1. Clean Up Your Data First #

Start by ensuring your MoviesByGenre range only contains valid genre text. You can use a helper column to filter out bad entries:

=IF(AND(ISTEXT(A1), A1 <> ""), A1, "")

Fill this down alongside your original data, then use the cleaned column as your MoviesByGenre range instead.

2. Replace with a More Robust Formula #

If you’re using a built-in formula combo, swap it for this more Mac-compatible version (remember to enter it as an array formula with Cmd+Shift+Enter in Excel 2016 for Mac):

=INDEX(MoviesByGenre, MATCH(MAX(COUNTIF(MoviesByGenre, MoviesByGenre)), COUNTIF(MoviesByGenre, MoviesByGenre), 0))

This uses COUNTIF to tally frequencies, MAX to find the highest count, and INDEX/MATCH to pull the corresponding genre—no finicky nested arrays that Mac Excel might struggle with.

3. Debug the Range Step-by-Step #

Gradually expand your MoviesByGenre range (start with 10 rows, then 50, then full range) and run FindMax after each expansion. When it throws an error, check the newly added rows for blank cells, errors, or non-text values—this will pinpoint exactly what’s breaking the function.

4. Update a Custom VBA FindMax Function #

If FindMax is a custom VBA function, add error handling to skip bad cells. Here’s a revised version that’s more resilient:

Function FindMax(rng As Range) As String
    Dim freq As New Dictionary
    Dim cell As Range
    Dim maxCount As Integer
    Dim maxGenre As String
    
    ' Skip errors and blank cells
    On Error Resume Next
    For Each cell In rng
        If cell.Value <> "" And Not IsError(cell.Value) Then
            If freq.Exists(cell.Value) Then
                freq(cell.Value) = freq(cell.Value) + 1
            Else
                freq.Add cell.Value, 1
            End If
        End If
    Next cell
    On Error GoTo 0
    
    ' Find the genre with the highest frequency
    maxCount = 0
    For Each key In freq.Keys
        If freq(key) > maxCount Then
            maxCount = freq(key)
            maxGenre = key
        End If
    Next key
    
    FindMax = maxGenre
End Function

Just make sure you enable the Microsoft Scripting Runtime reference in the VBA editor (Tools > References) for the Dictionary object to work.

内容的提问来源于stack exchange，提问作者Lobscurite