Excel公式优化需求：指定区域全空时返回空单元格

Let's tackle your problem step by step. Your original formula works great for finding the minimum date in the dynamic AF column range (from AF4 down to the row where column A first goes blank), but it returns that annoying default 1900-01-00 date when all values in the range are empty. Here's how to adjust it to return a blank cell instead:

Solution for Excel 365/2021 (Cleaner Code with LET) #

The LET function lets you define reusable variables, making the formula easier to read and maintain:

=LET(
    LastRow, MIN(IF(A4:A108="",ROW(A4:A108))),
    TargetRange, INDIRECT(ADDRESS(ROW(AF4),COLUMN(AF4))&":af"&LastRow),
    IF(COUNTA(TargetRange)=0, "", MIN(TargetRange))
)

Breakdown of the Logic: #

• LastRow: Calculates the row number of the first blank cell in A4:A108 (uses the exact same logic as your original formula).
• TargetRange: Defines the AF column range we need to evaluate (from AF4 down to AF[LastRow]).
• IF(COUNTA(TargetRange)=0, "", MIN(TargetRange)): Checks if there are any non-empty cells in the target range. If no values exist (COUNTA=0), returns a blank string; otherwise, returns the minimum date value as your original formula did.

Solution for Older Excel Versions (No LET Support) #

If you're using an older Excel version that doesn't support LET, you can nest the range logic directly in the IF function:

=IF(COUNTA(INDIRECT(ADDRESS(ROW(AF4),COLUMN(AF4))&":af"&MIN(IF(A4:A108="",ROW(A4:A108))))) = 0, "", MIN(INDIRECT(ADDRESS(ROW(AF4),COLUMN(AF4))&":af"&MIN(IF(A4:A108="",ROW(A4:A108))))))

Critical Note for Array Formula Input: #

Since both formulas use array logic (the IF(A4:A108="",ROW(A4:A108)) segment), you’ll need to enter them by pressing Ctrl+Shift+Enter instead of just Enter if you’re using Excel 2019 or earlier. Excel 365/2021 handles array formulas automatically with a regular Enter press.

Why This Fixes the Issue #

Your original formula returns the default 1900 date because the MIN function treats an entirely empty range as 0, which Excel interprets as the date serial number for 1900-01-00. By adding the IF(COUNTA(...)=0) check, we explicitly tell Excel to return a blank instead of falling back to that unhelpful default value when there’s nothing to calculate.

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