Excel Workout 18 - IF, AND & OR Functions!

[IlgarZarbaliyev]

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.

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.

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

[QuadriAtharuOlayinka]

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)

Quadri Atharu - Challenge #17.xlsx (8.3 MB)

[ClaudiaRoxo]

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

Challenge #18.xlsx (1.7 MB)

[Tural]

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)

Challenge #18_Tural_Mammadov.xlsx (8.3 MB)

[BasharatAsghar]

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)