嵌套IF的QUARTILE数组公式返回值异常求助

Let's break down why your formula is returning an unexpected quartile value and fix it step by step.

Core Issue #

Your manual calculation of the 25th percentile (0.8685) is correct for the 6 target values, but the formula is pulling from a different dataset—this means your conditional filtering isn't working as intended.

Step 1: Verify Your Conditional Filter #

First, let's confirm exactly which values your formula is feeding into QUARTILE.

1. In a blank cell, enter this array formula (press Ctrl+Shift+Enter):

   =IF(((F2:$F$10=$W$4)*($Q$2:$Q$10=$W$3))*($E$2:$E$10=W$2),IF($O$2:$O$10<>"",$O$2:$O$10))
   

2. Look at the resulting array (you may need to expand the cell or use the formula bar to see all values). If it includes more than your 6 target values, or misses some, your conditions are matching unintended rows.

Quick Check for Match Count #

Use SUMPRODUCT to count how many rows meet all your criteria:

=SUMPRODUCT(--((F2:$F$10=$W$4)*($Q$2:$Q$10=$W$3)*($E$2:$E$10=W$2)*($O$2:$O$10<>"")))

This should return 6. If not, go row-by-row to check:

• Does F[Row] equal $W$4?
• Does Q[Row] equal $W$3?
• Does E[Row] equal W$2?
• Is O[Row] not blank?

Step 2: Simplify Your Formula Logic #

Your original formula uses multiplication for AND conditions, which works, but nested IFs are easier to debug. Replace your formula with this array version (still press Ctrl+Shift+Enter):

=QUARTILE(IF(F2:$F$10=$W$4,IF($Q$2:$Q$10=$W$3,IF($E$2:$E$10=W$2,IF($O$2:$O$10<>"",$O$2:$O$10)))),1)

This nested structure makes it clearer where each condition is applied, reducing the chance of accidental logic errors.

Step 3: Upgrade to FILTER (Excel 365/2021+) #

If you're on a modern Excel version, ditch the array formula entirely for FILTER—it's far more readable and doesn't require CSE:

=QUARTILE(FILTER($O$2:$O$10,($F$2:$F$10=$W$4)*($Q$2:$Q$10=$W$3)*($E$2:$E$10=W$2)*($O$2:$O$10<>"")),1)

You can even test the FILTER part alone to confirm it returns exactly your 6 target values:

=FILTER($O$2:$O$10,($F$2:$F$10=$W$4)*($Q$2:$Q$10=$W$3)*($E$2:$E$10=W$2)*($O$2:$O$10<>""))

Why Your Manual Calculation Matches the Expected Result #

For reference, your 6 sorted target values are:
0.867040346, 0.868997877, 0.914032128, 0.981207615, 0.984750004, 0.988983643

Using QUARTILE.INC (which is what the legacy QUARTILE function uses), the 25th percentile is calculated as:

• Position = (n+1)*0.25 = (6+1)*0.25 = 1.75
• Value = 0.867040346 + 0.75*(0.868997877 - 0.867040346) ≈ 0.8685

This confirms your manual math is correct—so fixing the filtering will get your formula to match.

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