IF Function
The IF function in Excel is a logical function that allows you to perform different calculations or actions based on a specified condition. It evaluates a condition and returns one value if the condition is true, and another value if the condition is false.
AND Function
The AND function in Excel is a logical function that checks if all conditions specified in a formula are true. It returns TRUE if all conditions evaluate to TRUE, and FALSE if any of the conditions evaluate to FALSE.
OR Function
The OR function in Excel is a logical function that checks if at least one of the conditions specified in a formula is true. It returns TRUE if any of the conditions evaluate to TRUE, and FALSE only if all conditions evaluate to FALSE.
Task
To ascertain the 2022 Rating, evaluate the following
condition: If the number of projects falls between 300 and
600 (inclusive) and the 2021 Rating is B, then assign the
rating as A; otherwise, retain the 2021 Rating. To verify the
accuracy, compare the compare the values in cells H2:H4
with the corresponding values in cells I2:I4.
To determine the group membership based on the
condition, analyze the Group column. If the country is the
San Marino,Monaco, or Liechtenstein, assign it to the Small
group (referenced from cell G8). For all other countries,
assign them to the Big group (referenced from cell G7). To
validate the solution, compare the values in cells H8:H9
with the corresponding values in cells I8:I9.
Submission
Reply to this post with your formula code and solution file. Please be sure to blur or hide your formula code.
Thank you for participating in the Excel Challenge related to IF, AND & OR Functions! I hope you found this challenge to be a fun and engaging way to improve your Excel skills and learn more about how to work with IF, AND & OR Functions in Excel.
To find Rating 2022:
=IF(AND(B2>=300,B2<=600,C2=B),A,C2)
To fond Group:
=IF(OR(A2=San Marino,A2=Liechtenstein,A2=Monaco ),Small, Big)
Challenge #18 with Solution.xlsx (8.3 MB)
Once again, thank you for participating in this Excel Challenge, and I look forward to seeing you in future challenges and learning opportunities!
Formula for “Rating 2022” column: =IF(AND(B2>=300,B2<=600,C2=“B”),“A”,C2)
Formula to count number of occurrence of A: =COUNTIF($D$2:$D$25,$G2) and using relative referencing calculating the same for B and C by changing $G2 to $G3 and $G4 respectively.
Formula for “Group” column: =IF(OR(A2=“San Marino”,A2=“Monaco”,A2=“Liechtenstein”),$G$8,$G$9)
CFormula to count number of occurrence of small in the dataset: =COUNTIF($E$2:$E$25,$G8)
