不使用LET()函数，通过数组将文本值转换为数字计算平均值（Excel公式实现问询）

If you're using Excel 365 or 2021 (which supports dynamic array functions like FILTER, SWITCH, and LET), here's a clean, readable formula that handles all your requirements:

=LET(
    age_values, FILTER(2:2, 1:1="Age"),  // Gets all cells in row 2 where header is "Age"
    encoded, SWITCH(age_values, "Young", 0, "Old", 1, "Super Old", 2, "Should Be Dead", 3),  // Maps text to numbers
    avg, AVERAGE(encoded),  // Calculates average of encoded values
    IFERROR(
        IFS(
            avg = 3, "Should Be Dead",  // Exact average of 3 = all "Should Be Dead"
            avg > 2, "Super Old",       // Averages between 2 and 3 (not including 3)
            avg > 1, "Old",             // Averages between 1 and 2
            TRUE, "Young"               // Averages 0 to 1 inclusive (matches your example)
        ),
        "No Age columns found"  // Handles case where there are no "Age" columns
    )
)

How to use this: #

1. Paste this formula into the first cell of your "Average" column (e.g., cell A2).
2. Drag it down to apply to all rows in your dataset.

For older Excel versions (pre-365/2021): #

If you don't have dynamic array support, you'll need to use an array formula (enter with Ctrl+Shift+Enter instead of just Enter):

=IFERROR(
    IF(
        AVERAGE(SWITCH(INDEX(2:2,SMALL(IF(1:1="Age",COLUMN(1:1)),ROW(INDIRECT("1:"&COUNTIF(1:1,"Age"))))),"Young",0,"Old",1,"Super Old",2,"Should Be Dead",3))=3,
        "Should Be Dead",
        IF(
            AVERAGE(SWITCH(INDEX(2:2,SMALL(IF(1:1="Age",COLUMN(1:1)),ROW(INDIRECT("1:"&COUNTIF(1:1,"Age"))))),"Young",0,"Old",1,"Super Old",2,"Should Be Dead",3))>2,
            "Super Old",
            IF(
                AVERAGE(SWITCH(INDEX(2:2,SMALL(IF(1:1="Age",COLUMN(1:1)),ROW(INDIRECT("1:"&COUNTIF(1:1,"Age"))))),"Young",0,"Old",1,"Super Old",2,"Should Be Dead",3))>1,
                "Old",
                "Young"
            )
        )
    ),
    "No Age columns found"
)

Key Notes: #

• The formula automatically finds all "Age" columns regardless of their position in the sheet.
• It handles edge cases like missing "Age" columns (returns a friendly message) and exact averages (e.g., all values are "Should Be Dead" returns that text instead of "Super Old").
• The interval mapping follows your example and logically extends to cover all possible averages:
  • 0 ≤ average ≤ 1 → Young
  • 1 < average ≤ 2 → Old
  • 2 < average < 3 → Super Old
  • average = 3 → Should Be Dead

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