Practice 5: Math Operators and Conditional Formatting

← Back to ISMG-2050 Course Hub

Assignment Metadata

Module: Module 2 (Weeks 3 & 4)
Due Date: Sunday, September 14, 2026 at 11:59 PM MT
Starter Input: Your completed [your initials]Practice04.xlsx from Practice 4 (open and save a new copy)
Expected Submission: [your initials]Practice05.xlsx

Skills Covered

Math operators: +, -, *, /, ^ (exponentiation)
Order of operations (PEMDAS) in Excel formulas
Conditional formatting: threshold-based cell highlighting
Conditional formatting: icon sets and data bars
File extension type awareness (.xlsx vs .xlsm vs .csv)

Part 1: Salary Report — Math Operators (45 pts)

Open a new blank workbook and save as [your initials]Practice05.xlsx.
In cell A1, type Employee, B1 type Base Salary, C1 type Bonus %, D1 type Overtime Hours, E1 type Hourly Rate, F1 type Total Compensation.
In cells A3:A8, enter employee names: Alice, Bob, Carol, Dave, Eve, Frank.
In cells B3:B8, enter base salaries: 65000, 72000, 58000, 81000, 69000, 74000.
In cells C3:C8, enter bonus percentages: 0.05, 0.07, 0.04, 0.06, 0.05, 0.08.
In cells D3:D8, enter overtime hours: 12, 8, 0, 15, 20, 5.
In cell E3, enter the hourly rate formula: =B3/2080 (annual salary / 2080 standard work hours = hourly rate). Copy down to E4:E8.
In cell F3, compute total compensation using math operators: =B3+(B3*C3)+(D3*E3)
Breakdown: Base + (Base * Bonus%) + (Overtime Hours * Hourly Rate).
Copy F3 down to F4:F8.

Part 2: Conditional Formatting — Salary Thresholds (25 pts)

Select range F3:F8 on the Salary sheet.
Home > Conditional Formatting > Highlight Cells Rules > Greater Than: Enter 70000, choose Green Fill with Dark Green Text. This highlights total compensation above $70,000.
Select range B3:B8.
Home > Conditional Formatting > Data Bars > Gradient Fill - Blue.

Select range C3:C8.

Home > Conditional Formatting > Color Scales > Green-Yellow-Red. This shows bonus rate distribution visually.

Part 3: Math Operator Challenges (30 pts)

In a new sheet named "Operators", create the following demonstration table:

A1: type Expression, B1 type Formula, C1 type Result

Row 2: Addition / =50+30 / 80

Row 3: Subtraction / =100-47 / 53

Row 4: Multiplication / =12*8 / 96

Row 5: Division / =150/7 / 21.42857

Row 6: Exponentiation / =2^8 / 256

Row 7: Mixed Order (parentheses) / =(10+5)*3-4 / 41

Row 8: Complex formula with percentages / =1000*(1+0.05)^3 (compound growth) / 1157.625

In cell A10, type: File Extension Types. Below this, document in column B the file extension types you have encountered and used so far this course:

Rows 11\u201314: For each extension (.xlsx, .csv, .pdf, .xlsm), note its purpose (e.g., ".xlsx = standard workbook with formulas, .csv = plain-text tabular data, .pdf = final deliverable for submission").

Submission Criteria

File named exactly [your initials]Practice05.xlsx

Sheet1 (Salary): All math formulas correct, total compensation calculated accurately

Conditional formatting: threshold highlight at $70K+, data bars on salary column, color scale on bonus %

"Operators" sheet: All 8 expressions produce correct results using operators + - * / ^

Complex formula row demonstrates compound interest with exponentiation operator

File extension type documentation completed (at least .xlsx, .csv, .pdf, .xlsm)

AI Disclosure statement in a note below the Operators sheet

→ Back to Week 3\u20134 Homework Portal