# Excel Workout 18 - IF, AND & OR Functions!

Welcome to Excel Workout #18!
Difficulty Level:

This week’s challenge is designed to test your knowledge on If, And & Or Functions.

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.

• 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

Period
This workout will be released on Thursday May 25, 2023 , and the author’s solution will be posted on Wednesday May 31, 2023.

Challenge #17.xlsx (8.3 MB)

Good luck,

Ilgar Zarbaliyev

1 Like
Summary

Rating 2022
=IF(AND(B2>=300,B2<=600,C2=“B”),“A”,C2)
ABC Rating 2022

A =COUNTIFS(\$D\$2:\$D\$25,G2)
B =COUNTIFS(\$D\$2:\$D\$25,G3)
C =COUNTIFS(\$D\$2:\$D\$25,G4)

Small or Big Rating

=IF(OR(A2=“San Marino”,A2=“Monaco”,A2=“Liechtenstein”),\$G\$8,\$G\$9)

Group Rating
Small =COUNTIFS(\$E\$2:\$E\$25,G8)
Big =COUNTIFS(\$E\$2:\$E\$25,G9)

1 Like

Here is my file (changed the name to Challange #18…)

Challenge #18.xlsx (1.7 MB)

1 Like

Rating 2022
`=IF(AND(B2>=300, B2 <=600, C2="B"), "A", C2)`

Group
`=IF(OR(A2=A\$16,A2=A\$15,A2=A\$17),G\$8,G\$9)`

1 Like

Rating 2022:
=IF(AND(AND(\$B2>=300,\$B2<=600),\$C2=“B”),“A”,\$C2)

Group:
=IF(OR(\$A2=“San Marino”,\$A2=“Monaco”,\$A2=“Liechtenstein”),\$G\$8,\$G\$9)

Rating 2022 Count:
A=COUNTIFS(\$D\$2:\$D\$25,\$G2)
B=COUNTIFS(\$D\$2:\$D\$25,\$G3)
C=COUNTIFS(\$D\$2:\$D\$25,\$G4)

Group Count:
Small=COUNTIFS(\$E\$2:\$E\$25,\$G8)
Big=COUNTIFS(\$E\$2:\$E\$25,\$G9)

EnterpriseDNA Excel Challenge #18 Solution Muhammad Basharat Asghar.xlsx (8.3 MB)

1 Like

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.

Here is my solution to Workout #18:

• 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!

1 Like

Here is my solution to the workout

Rating 2022:
=IF(AND(B2>=300,B2<=600,C2=“B”),“A”,C2)

Group Category:
=IF(OR(A2=“San Marino”,A2=“Liechtenstein”,A2=“Monaco”),“Small”,“Big”)

Category
A:=COUNTIFS(D:D,G2)
B: =COUNTIFS(D:D,G3)
C: =COUNTIFS(D:D,G4)

Category:
Small: =COUNTIFS(E:E,G8)
Big: =COUNTIFS(E:E,G9)

File Attached:
Excel Workout Challenge #18 Keith Smith.xlsx (8.3 MB)

Thanks for the workout.
Keith

1 Like
• 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)

My excel workout file - Excel Workout 18 - IF, AND & OR Functions!.xlsx (8.3 MB)

1 Like