IFS 返回錯誤值或 #N/A

A score of 72 comes back as an A, almost every row is a D, a blank gets an F, or a few cells show #N/A. Those are different problems. Adding TRUE at the end only fixes the last one.

Type this into A1:B9. In B6, type a leading apostrophe before 72 ('72) so the cell displays 72 but is stored as text, the way a score often arrives from a CSV or a web form. Leave B7 empty.

The bands are A for 90 or above, B for 80–89, C for 70–79, D for 60–69, and F below 60. A missing score should stay ungraded.

In C2, fill down to C9:

=IFERROR(IF(B2="","",IFS(--B2>=90,"A",--B2>=80,"B",--B2>=70,"C",--B2>=60,"D",TRUE,"F")),"Check score")

C2:C9 returns A, A, B, B, C, blank, D, F. The -- turns numeric text into a number, the IF leaves an empty score blank, the tests run from high to low, TRUE,"F" catches anything below 60, and IFERROR shows “Check score” when the input can’t be converted. I ran these checks in Google Sheets on 14 November 2026.

No condition matches, so IFS returns #N/A

Theo’s 59 in B9 matches none of these four tests, so this formula returns #N/A:

=IFS(B9>=90,"A",B9>=80,"B",B9>=70,"C",B9>=60,"D")

Add a final pair. =IFS(B9>=90,"A",B9>=80,"B",B9>=70,"C",B9>=60,"D",TRUE,"F") returns F. Wrapping the same four-test formula in IFNA also returns F. I’d use the TRUE pair: it states the default. IFNA will also replace an #N/A that came from inside a more complicated test; when to mask an error is in IFERROR vs IFNA.

Tests that start too low turn a 90 into a D

IFS stops at the first TRUE. =IFS(B3>=60,"D",B3>=70,"C",B3>=80,"B",B3>=90,"A",TRUE,"F") returns D for Luis’s 90, because B3>=60 is already TRUE. Every numeric score of 60 or more would get D with that order.

For >= bands, put the highest cutoff first: =IFS(B3>=90,"A",B3>=80,"B",B3>=70,"C",B3>=60,"D",TRUE,"F") returns A. If you write the tests as “below 60, below 70,” the order goes the other way, lowest first. Nested IF follows the same first-true-wins rule.

Imported 72 gets an A because it’s text

This is the one I’d look at first when hand-typed scores grade correctly and imported ones jump to the top band.

Priya’s B6 displays 72 but is text: =ISNUMBER(B6) returns FALSE. This formula returns A, not C:

=IFS(B6>=90,"A",B6>=80,"B",B6>=70,"C",B6>=60,"D",TRUE,"F")

In Google Sheets, =B6>=90 and even =B6>=1000 both returned TRUE. The comparison is not numeric. A text value ranks above a number, so the first test succeeds and IFS never looks at the later bands. I did not re-run those comparisons in Excel for this page.

Convert the score inside the tests. =IFS(--B6>=90,"A",--B6>=80,"B",--B6>=70,"C",--B6>=60,"D",TRUE,"F") returns C, and VALUE(B6) does the same. If B6 held a word such as absent, both conversions return #VALUE!; the opening formula’s IFERROR then shows “Check score”.

For a one-off import, I’d convert the column to real numbers rather than wrap every formula; converting numbers stored as text covers that. For a feed you re-import, keep the conversion in the formula.

Blank cells grade as F because they compare like zero

=B7<60 returns TRUE on the empty cell, and --B7 returns 0. An unguarded =IFS(B7>=90,"A",B7>=80,"B",B7>=70,"C",B7>=60,"D",TRUE,"F") therefore returns F: none of the positive cutoffs match, and the fallback treats the blank like a low score.

If a missing score should stay ungraded, test for it first. =IF(B7="","",IFS(B7>=90,"A",B7>=80,"B",B7>=70,"C",B7>=60,"D",TRUE,"F")) returns a blank. That also treats a formula that returns "" as missing; distinguishing that from a truly empty cell is in ISBLANK vs empty cells. A real score of 0 should still be F.

An exact 90 becomes B when the cutoff is >

=IFS(B3>90,"A",B3>80,"B",B3>70,"C",B3>60,"D",TRUE,"F") returns B for Luis’s 90, because B3>90 is FALSE and B3>80 is TRUE. If 90 belongs in the A band, use >=90. The same >= formula returns B for Omar’s exact 80 and D for Sofia’s exact 60.

TRUE does not catch an error inside a test

A final TRUE pair covers tests that come back FALSE. It does not catch a test that returns an error.

I put =1/0 in B11, which displays #DIV/0!. =IFS(B11>=90,"A",TRUE,"Not A") returns #DIV/0!, not “Not A”. Handle the failure in the test:

=IFS(IFERROR(B11>=90,FALSE),"A",ISERROR(B11),"Check score",TRUE,"Not A")

That returns “Check score” while B11 is an error, and “Not A” for Theo’s 59.

Use a threshold table once you have more than a few bands

IFS is fine for five grades. Once the list grows, I’d keep the cutoffs in a table. Enter this in E1:F6, with E2:E6 in ascending order:

In C2, fill down:

=IF(B2="","",XLOOKUP(--B2,$E$2:$E$6,$F$2:$F$6,"No tier",-1))

It returns the same A, A, B, B, C, blank, D, F. The -1 match mode takes an exact match or the next smaller threshold, so 87 becomes B. A score below 0 returns “No tier”.

XLOOKUP is in Microsoft 365, Excel 2021 and later, and Google Sheets, not in Excel 2019. There, =IF(B2="","",VLOOKUP(--B2,$E$2:$F$6,2,TRUE)) returned the same grades on this sample. The first column has to stay sorted ascending. If the B minimum moves from 80 to 82, you edit E5 instead of hunting through formulas. More on the lookup functions is in XLOOKUP vs INDEX MATCH vs VLOOKUP.

IFS itself is in Excel 2019, Excel 2021, Microsoft 365, and Google Sheets. On older Excel, a nested IF with the same --, blank check, and high-to-low order produced the same eight grades:

=IF(B2="","",IFERROR(IF(--B2>=90,"A",IF(--B2>=80,"B",IF(--B2>=70,"C",IF(--B2>=60,"D","F")))),"Check score"))