'%20stroke='%23C6EEFF'%20stroke-width='14'%20stroke-linejoin='round'%20d='M256%2052c86%200%20154%2068%20154%20154%200%2056-30%2092-60%20124-21%2022-33%2040-35%2066H197c-2-26-14-44-35-66-30-32-60-68-60-124%200-86%2068-154%20154-154z'/%3e%3c!--%20inner%20glow%20behind%20the%20filament%20--%3e%3ccircle%20cx='256'%20cy='250'%20r='104'%20fill='url(%23dpGlow)'/%3e%3c!--%20filament:%20glowing%20loop%20--%3e%3cpath%20fill='none'%20stroke='%23DFF6FF'%20stroke-width='18'%20stroke-linecap='round'%20stroke-linejoin='round'%20filter='url(%23dpSoft)'%20d='M212%20322v-52c0-30%2020-46%2044-46s44%2016%2044%2046v52'/%3e%3cpath%20fill='none'%20stroke='%23EAFBFF'%20stroke-width='9'%20stroke-linecap='round'%20stroke-linejoin='round'%20d='M212%20322v-52c0-30%2020-46%2044-46s44%2016%2044%2046v52'/%3e%3c!--%20glass%20sheen%20(reflective)%20--%3e%3cpath%20fill='%23FFFFFF'%20opacity='0.32'%20d='M150%20106c26-28%2062-44%2096-46-52%2016-84%2062-90%20118-4%2038%204%2074%2022%20106-40-24-66-64-66-116%200-24%206-44%2038-62z'/%3e%3c!--%20base%20cap:%20outlined%20like%20the%20glass%20so%20the%20plug%20end%20reads%20clearly%20--%3e%3crect%20x='210'%20y='398'%20width='92'%20height='20'%20rx='10'%20fill='url(%23dpCap)'%20stroke='%23C6EEFF'%20stroke-width='10'/%3e%3crect%20x='220'%20y='424'%20width='72'%20height='14'%20rx='7'%20fill='%231B3FA8'%20stroke='%23C6EEFF'%20stroke-width='8'/%3e%3crect%20x='228'%20y='444'%20width='56'%20height='14'%20rx='7'%20fill='%2316337F'%20stroke='%23C6EEFF'%20stroke-width='8'/%3e%3crect%20x='240'%20y='464'%20width='32'%20height='12'%20rx='6'%20fill='%2312307A'%20stroke='%23C6EEFF'%20stroke-width='7'/%3e%3c/svg%3e)
Real-World Excel Project 2: Water quality alert sheet
Scenario
You are a data analyst. A city water department has a problem: test readings are kept in a sheet nobody checks in time. As an environmental data assistant, you are tasked to make a status dashboard that shows readings above the average. Sheet: column A = test_id, B = river, C = ph.
Question
Write the formula in the editor, press Check my answer, then explore the dashboard to answer the client's problem.
Your task
- Read the client's problem
- Press Check my answer
- Use the filter to find the group that needs attention
- Your project is saved and published to your portfolio
Advanced · COUNTIF & SUMIF — question 10 of 30
1. Choose your dataset
Pick an industry and a size. Your code and results change to match.
💡 Explain my whole code, line by line (hover any word in the editor for a tip)
🌈 In short — no coding words
Altogether: this formula looks at the cells you named and works out one answer.
Dataset & schema
water_tests
The tiny starter data. Bigger data scrolls.
Fields
| A: test_id | B: river | C: ph |
|---|---|---|
| W1 | Vaal | 7 |
| W2 | Orange | 6 |
| W3 | Vaal | 8 |
| W4 | Tugela | 5 |
Environment data only. No other industry's data is used.
$ Press “Check my answer” — results appear here.Your next steps
Each item turns green when your answer includes it.
- Uses a dashboard
- Uses the sheet's columns B and C