Unit 3 IF function - Part 1
Nested IF statement
You can also nest IF functions to perform more complex logical tests.
For example, suppose you have a cell containing a student's score in cell A1, and you want to display "A" if the score is greater than or equal to 90, "B" if it's greater than or equal to 80, "C" if it's greater than or equal to 70, and "D" if it's greater than or equal to 60.
You can use the following nested IF formula:
=IF(A1>=90, "A", IF(A1>=80, "B", IF(A1>=70, "C", IF(A1>=60, "D", "F"))))
This formula checks the score against multiple conditions in sequence. If the score meets the first condition (A1>=90), it returns "A"; otherwise, it moves on to the next condition (A1>=80), and so on.
In this dataset:
Column A contains the test scores of the students.
Column B will contain the letter grades assigned based on the test scores using the nested IF formula provided earlier.
Example 2
You work as an administrative assistant at a small office supplies company. Your company offers discounts on bulk purchases to its customers. However, the discount rate varies depending on the total amount spent by the customer. Your task is to create an Excel spreadsheet to calculate the discount amount for each customer based on their total purchase.
For example, if a customer's total purchase amount is greater than or equal to $500, they are eligible for a 10% discount. Otherwise, they receive no discount.
The formula might look like this:
=IF(B2>=500, B2*0.1, 0)
Where B2 is the total purchase amount for the customer. If the total purchase amount is greater than or equal to $500, the discount amount is calculated as 10% of the total purchase amount. Otherwise, no discount is applied.
g
MIN and MAX functions with arrays in Microsoft Excel allows you to find the smallest an largest values respectively within a range of cells. A
![](https://assets.zyrosite.com/cdn-cgi/image/format=auto,w=464,h=160,fit=crop/YD04o0Gj5ouVgKWy/screenshot-2024-02-25-1.08.23-pm-YNqNMq8n6Jf21wJb.png)
![](https://assets.zyrosite.com/cdn-cgi/image/format=auto,w=328,h=320,fit=crop/YD04o0Gj5ouVgKWy/screenshot-2024-02-25-1.08.23-pm-YNqNMq8n6Jf21wJb.png)
![](https://assets.zyrosite.com/cdn-cgi/image/format=auto,w=464,h=160,fit=crop/YD04o0Gj5ouVgKWy/screenshot-2024-02-25-1.25.21-pm-AoPN4Pvg4Qu1Jaxq.png)
![](https://assets.zyrosite.com/cdn-cgi/image/format=auto,w=328,h=320,fit=crop/YD04o0Gj5ouVgKWy/screenshot-2024-02-25-1.25.21-pm-AoPN4Pvg4Qu1Jaxq.png)
![](https://assets.zyrosite.com/cdn-cgi/image/format=auto,w=464,h=160,fit=crop/YD04o0Gj5ouVgKWy/screenshot-2024-02-25-1.25.40-pm-dOqNZqynpWtyWkz7.png)
![](https://assets.zyrosite.com/cdn-cgi/image/format=auto,w=328,h=320,fit=crop/YD04o0Gj5ouVgKWy/screenshot-2024-02-25-1.25.40-pm-dOqNZqynpWtyWkz7.png)
Assignment
Scenario: You work as an administrative assistant at a small retail store. Your task is to categorize customers based on their purchase history and loyalty status to determine the type of discount they are eligible for.
Create an IF statement and Dataset (using any suitable details) to show how you would complete such a task.
Submit your answer to actira.tt@gmail.com