All classes Excel Beginner Syllabus · Episode 4 of 10

Checking access...

Want to go deeper?

Join one of our courses or ask us directly on WhatsApp.

View the full 10-episode syllabus View Excel Courses Ask on WhatsApp

Recommended classes for you

Picked from the software, topic, and level of “Beginner Excel Class #4: IF Function: Pass or Fail”.

10 episodesFree preview

Excel · Intermediate

Excel Intermediate

10 video lessons with PDF guides, 1 h 1 min in total. Starts with: Text Functions: Clean and Split Your Data.

1 h 1 min 35 0

View class

10 episodesFree preview

Excel · Advanced

Excel Advanced

10 video lessons with PDF guides, 56 min in total. Starts with: Named Ranges and Cross Sheet Formulas.

56 min 39 0

View class

Need more than a video?

Other ways we can help with Excel.

Learn with a mentor

Scheduled Microsoft 365 & Azure courses with live guidance and a certificate.

Course

Course/Training Excel Macro — Implementing Excel Macros in an Interactive Dashboard for Employee Performance Monitoring and Evaluation

★ 5.0 (12)

Course

Course/Training Microsoft Excel 2016 Fundamental

★ 5.0 (9)

Course

Course/Training Excel — Interactive Excel Dashboards

★ 5.0 (6)

See all in Microsoft 365 & Azure →

Have it built for you

No time to learn Excel? Our team can do the project for you.

Service

Development Service: Excel — Microsoft Excel Macro VBA

★ 5.0 (11)

Service

Development Service: In-House Excel Macro & VBA Training — Data Automation

★ 5.0 (7)

Service

Development Service: Excel Macro — Excel VBA Programming Master Class

★ 5.0 (4)

See all in Microsoft 365 & Azure →

Get notified about new Excel classes

At most one email a week. Unfollow anytime with one click.

Transcript

The full narration of this video. The current sentence is highlighted while the video plays; click any sentence to jump there.

Show transcript

Introduction

This is the final result we will create, a student score list complete with final scores, pass or fail status, A to D grades, and the student column. Everything is calculated automatically by Excel using the IF function. Let's start. Hello, welcome to the Excel for beginners tutorial, the IF function. With IF, Excel can make its own decisions. We will calculate the final scores of students, determine if they pass or fail, assign grades, and count the number of students who passed. This is the detail of 14 students' scores, including task scores, midterm scores, and final exam scores.

Step 1: Student score data

The final score, status, and grade columns are still empty. The final score is calculated from tasks 30%, midterm 3, and final exam 40%. Let Excel do the calculation.

Step 2: Final score with ROUND

Click on cell E2. Type equals round, open parenthesis, B2 times 30%, plus C2 times 30%, plus D2 times 40%, semicolon, zero, close parenthesis. Round rounds the result, and the number zero means no decimal places. Note, on this computer, the separator for the formula is a semicolon, not a comma. Press Enter, then double-click the fill handle to copy it to all students. Now the awaited function, IF.

Step 3: IF function for pass or fail

The IF function has three parts, the condition, the result if true, and the result if false. Click on F2, then type equals IF, open parenthesis. E2 greater than or equal to 75, semicolon, quote pass, semicolon, quote fail, close parenthesis. Text inside the formula must be enclosed in quotes. Press Enter and drag down. What if there are more than two options?

Step 4: Nested IF for grade classification

We will use nested IF. In the second, type IF E2 greater than or equal to 90, the result is A. If not, IF again, E2 greater than or equal to 80, the result is B. If not, IP again, 70, result is C, otherwise D. Don't forget to close all parentheses at the end. Go down, and all students get a grade.

Step 5: Two conditions at once with AND

Sometimes the conditions are more than one. For that, there is the N function, which is true only if all conditions are met. In H1, type scholarship. In H2, type IP, open parenthesis, N, open parenthesis, E2 greater than or equal to 85, semicolon, B2 greater than or equal to 80, close parenthesis, semicolon, eligible, semicolon, dash. Students who are eligible are those with high final scores and diligent assignments.

Step 6: Summary with COUNTIF

Now let's make a summary. He 17, type number of passed, and in B17 type count IP, open parenthesis, F2 to F15, semicolon, quote passed. Count IP counts how many cells meet the criteria. Do the same for not passed in row 18. Then in row 19, calculate the class average with Average.

Step 7: Clean column width and center alignment

Let's tidy up the table. Select columns A to H, then double-click the column boundary line so the width fits. Select E to G and press Ctrl E to center-align. Now our grade table is easy to read. Let's check the power of IP.

Step 8: Test it, changing the score changes the result automatically

Change the first student's score to 100. See, the final score increases, the grade and the grade level also change, and the summary numbers below are updated. Save with Ctrl S.

Done! IF lets Excel decide on its own

Done. We have used round, IP, nested IP, N, and count IP to process student grades. Thank you for watching. See you in the next tutorial series. Want to master Excel for work and business? Cerdas Inovasi Digital offers Excel and Office courses, template creation services, reports, and dashboards, as well as sells the practice files for this tutorial. Contact us via WhatsApp or visit cerdasinovasidigital.com Thank you for watching. See you in the next tutorial series.

Ask the class material

Type a question or keyword; we find the part of this episode that explains it and jump the video there.

My notes

Notes are saved with the current video time and are only visible to you.

Questions & answers

Stuck on a step? Ask here; the instructor and other learners can answer.

No questions yet.

Your instructor

Cerdas Inovasi Digital Team

Software developers, BIM & 3D engineers, and AI practitioners

53 video classes1,296 episodes

We build software, BIM models, games and AI automations for clients every day, and turn that hands-on work into step-by-step classes: every step is shown on screen and comes with a PDF guide you can keep.

See our clients →

Memuat

Transcript

The full narration of this video. The current sentence is highlighted while the video plays; click any sentence to jump there.

Show transcript

Introduction

Step 1: Student score data

The final score, status, and grade columns are still empty. The final score is calculated from tasks 30%, midterm 3, and final exam 40%. Let Excel do the calculation.

Step 2: Final score with ROUND

Step 3: IF function for pass or fail

Step 4: Nested IF for grade classification

Step 5: Two conditions at once with AND

Step 6: Summary with COUNTIF

Step 7: Clean column width and center alignment

Step 8: Test it, changing the score changes the result automatically

Change the first student's score to 100. See, the final score increases, the grade and the grade level also change, and the summary numbers below are updated. Save with Ctrl S.

Done! IF lets Excel decide on its own

Your instructor

Cerdas Inovasi Digital Team

Software developers, BIM & 3D engineers, and AI practitioners

53 video classes1,296 episodes

See our clients →

Recommended classes for you

Need more than a video?

Transcript

0:03Introduction

0:44Step 1: Student score data

1:00Step 2: Final score with ROUND

1:34Step 3: IF function for pass or fail

2:04Step 4: Nested IF for grade classification

2:33Step 5: Two conditions at once with AND

3:04Step 6: Summary with COUNTIF

3:43Step 7: Clean column width and center alignment

4:08Step 8: Test it, changing the score changes the result automatically

4:23Done! IF lets Excel decide on its own

Ask the class material

My notes

Questions & answers

Your instructor

Recommended classes for you

Need more than a video?

Transcript

0:03Introduction

0:44Step 1: Student score data

1:00Step 2: Final score with ROUND

1:34Step 3: IF function for pass or fail

2:04Step 4: Nested IF for grade classification

2:33Step 5: Two conditions at once with AND

3:04Step 6: Summary with COUNTIF

3:43Step 7: Clean column width and center alignment

4:08Step 8: Test it, changing the score changes the result automatically

4:23Done! IF lets Excel decide on its own

Ask the class material

My notes

Questions & answers

Your instructor

Introduction

Step 1: Student score data

Step 2: Final score with ROUND

Step 3: IF function for pass or fail

Step 4: Nested IF for grade classification

Step 5: Two conditions at once with AND

Step 6: Summary with COUNTIF

Step 7: Clean column width and center alignment

Step 8: Test it, changing the score changes the result automatically

Done! IF lets Excel decide on its own

Introduction

Step 1: Student score data

Step 2: Final score with ROUND

Step 3: IF function for pass or fail

Step 4: Nested IF for grade classification

Step 5: Two conditions at once with AND

Step 6: Summary with COUNTIF

Step 7: Clean column width and center alignment

Step 8: Test it, changing the score changes the result automatically

Done! IF lets Excel decide on its own