Look up and color letter grades
FormulasEasyDeterministic scoringLatest run
Passed by 11 of 12 products on their best model. Failed: Gemini in Sheets.
Prompt
Write a formula that looks up each student's letter grade from the grading scale sheet
Highlight any F grades in red and A grades in green
Students
Starting sheet · rows 1–21 of 22| A | B | C | D | |
|---|---|---|---|---|
| 1 | StudentID | Name | Score | Letter Grade |
| 2 | S001 | Aisha Okonkwo | 92 | |
| 3 | S002 | Brandon Thistlethwaite | 67 | |
| 4 | S003 | Camila Fuentes | 45 | |
| 5 | S004 | Derek Johansson | 83 | |
| 6 | S005 | Elena Petrov | 78 | |
| 7 | S006 | Femi Adeyemi | 95 | |
| 8 | S007 | Grace Liu | 71 | |
| 9 | S008 | Hector Ramirez | 58 | |
| 10 | S009 | Ingrid Sorensen | 88 | |
| 11 | S010 | James McCallister | 62 | |
| 12 | S011 | Karina Volkov | 97 | |
| 13 | S012 | Leo Tanaka | 74 | |
| 14 | S013 | Maya Gupta | 51 | |
| 15 | S014 | Nathan Blackwood | 86 | |
| 16 | S015 | Olivia Ferreira | 79 | |
| 17 | S016 | Priya Sharma | 93 | |
| 18 | S017 | Quinn O'Brien | 66 | |
| 19 | S018 | Ravi Krishnamurthy | 82 | |
| 20 | S019 | Sofia Delacroix | 47 | |
| 21 | S020 | Tyler Hutchinson | 89 |
Scale
Starting sheet| A | B | C | |
|---|---|---|---|
| 1 | Grading Scale Reference | ||
| 2 | Updated Fall 2024 | ||
| 3 | Min Score | Max Score | Grade |
| 4 | 0 | 59 | F |
| 5 | 60 | 69 | D |
| 6 | 70 | 79 | C |
| 7 | 80 | 89 | B |
| 8 | 90 | 100 | A |
A passing output
Autosheet in Excel · Opus 5.5Excel and Sheets · All types · All models · All outputs
Pass criteria
All must be met, checked automatically.Students!D2:D21hold the expected values, from formulas- Highlight F red (3 accepted ways)
- Highlight A green (3 accepted ways)
Passed 20
Autosheet in Excel Opus 5.5 · Medium
Passed32s · 11¢
Autosheet in Sheets Opus 5.5 · Medium
Passed48s · 14¢
Autosheet in Excel Sonnet 5.5 · Medium
Passed19s · 5¢
Claude in Excel Opus 5.5 · High
Passed31s · 28¢
Autosheet in Excel Haiku 5.5 · High
Passed32s · 0.6¢
Claude in Sheets Opus 5.5 · Medium
Passed48s · 14¢
Claude Code + Sheets MCP Opus 5.5 · Medium
Passed52s · N/A
Autosheet in Excel GPT-6.1 Sol · Medium
Passed48s · 11¢
Claude Code + Autosheet MCP Opus 5.5 · Medium
Passed2m 1s · N/A
ChatGPT + Autosheet MCP GPT-6 · Medium
Passed1m 21s · N/A
Autosheet in Sheets Haiku 5.5 · High
Passed1m 14s · 0.6¢
ChatGPT in Excel GPT-6 Astra · Medium
Passed1m 8s · $1.17
Claude in Excel Haiku 5.5 · High
Passed30s · 1¢
Claude in Sheets Haiku 5.5 · High
Passed1m 5s · 2¢
Autosheet in Excel GPT-6 Luna · High
Passed52s · 1¢
Copilot in Excel Auto
Passed1m 3s · N/A
ChatGPT + Drive MCP GPT-6 · Medium
Passed1m 27s · N/A
ChatGPT in Excel GPT-6 Luna · High
Passed1m 57s · 2¢
ChatGPT in Sheets GPT-6 Astra · Medium
Passed1m 15s · 88¢
ChatGPT in Sheets GPT-6 Luna · High
Passed2m 22s · 2¢
Failed 2
Autosheet in Excel GPT-6 Astra · Low
Failed18s · 15¢
Why it failed: 20 cells are wrong, such as D2: empty instead of "A".
Gemini in Sheets Auto
Failed5m · N/A
Why it failed: Still working at the 5-minute time limit. 20 cells are wrong, such as D2: empty instead of "A". D2:D21 hold no formula, and 1 other problem.