单公式实现含特殊字符的团队时效分组求和及平均计算需求

Got it, let's tackle this problem directly. You need to compute the average turnaround time for each team, handling values with the > symbol by stripping it out first—all with one formula, no find/replace workarounds.

Assumptions #

Let's assume your team names are in column A (e.g., A2:A14) and the corresponding turnaround values are in column B (e.g., B2:B14).

The Formula #

For any target team, use this formula structure:

=SUMPRODUCT((A:A="TEAM_NAME")*(--SUBSTITUTE(B:B, ">", "")))/SUMPRODUCT((A:A="TEAM_NAME")*1)

Breakdown of the Formula #

Let's break down each part to understand how it works:

• --SUBSTITUTE(B:B, ">", ""): This first removes any > from the value (e.g., >21 becomes "21"), then uses the double unary (--) to convert the cleaned text into a numeric value. It handles negative numbers like -2 perfectly too—no extra steps needed.
• (A:A="TEAM_NAME"): Creates a boolean array where each entry is TRUE (1) if the row matches your target team, and FALSE (0) otherwise. This acts as a filter to only include rows for the team you care about.
• SUMPRODUCT((A:A="TEAM_NAME")*(--SUBSTITUTE(...))): Multiplies the filter array by the cleaned numeric values, then sums the result—giving you the total of all valid, cleaned turnaround times for the team.
• SUMPRODUCT((A:A="TEAM_NAME")*1): Counts how many data points exist for the target team, so we can divide the total by the count to get the average.

Example Calculations for Your Data #

Let's plug in your specific teams to show the results:

• Team A: =SUMPRODUCT((A:A="Team A")*(--SUBSTITUTE(B:B, ">", "")))/SUMPRODUCT((A:A="Team A")*1) → Total = 4 + (-2) + (-7) = -5; Average ≈ -1.67
• Team B: =SUMPRODUCT((A:A="Team B")*(--SUBSTITUTE(B:B, ">", "")))/SUMPRODUCT((A:A="Team B")*1) → Total = 30 + 1 + (-16) = 15; Average = 5
• Team G: =SUMPRODUCT((A:A="Team G")*(--SUBSTITUTE(B:B, ">", "")))/SUMPRODUCT((A:A="Team G")*1) → Total = 54 + 21 = 75; Average = 37.5

This formula works reliably for all your teams, handles both positive/negative numbers and values with > seamlessly, and stays within your requirement of a single formula without any pre-processing.

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