Wahyu Widyananda, [Link]., M.T.

Computer for Worksheet Analysis

Basic Formula and Function Using Function Wizard

D-IV Study Program – Marketing Management

Business Administration Department
State Polytechnic of Malang
Learning Objectives

Able to apply basic formulas and functions in Microsoft Excel using the Function Wizard
to solve worksheet analysis case studies accurately and precisely
Basic concepts of formula in Excel
Function Wizard
Basic statistical functions for worksheet analysis
Date Time, Text, and Logical Functions
Case Study
Formula and Function Basics

Why Formula and Functions Matters in Worksheet Analysis

Formulas and functions help you:

• Calculate totals and results automatically
• Reduce manual errors
• Process business data faster
• Produce accurate information for reporting and
decision-making
Formula and Function Basics

Formula Basics Basic Operators in Excel

A formula in Excel always starts with: Excel uses operators to perform calculations:
• + addition
= (equal sign)
• - subtraction
• * multiplication
Examples: • / division
• ^ exponent
• () parentheses (priority)
Examples:
A1 = 2; B1 = 3; C1 = 5;

?
Formula and Function Basics

Understanding Cell References Relative References Absolute References ($)

Cell references tell Excel Relative reference change Absolute reference do not
which cells to use in a when copied change when copied
formula
Example:

Tax rate in B1 = 0.1

?
… = (Quantity * Unit Price) In E2 = C2*D2
Copy down → becomes:
In E3 = C3*D3
In E4 = C4*D4 Copy down the E2
and see the results
Formula and Function Basics

Mixed References ($)

Mixed reference lock only
row or column
Example:

Quantity Table (input data) Sales Table (Calculation Area)

• B3 refers to the quantity (input)

• B$2 refers to unit price (row fixed)
Formula and Function Basics

Common Formula Mistakes

• forgetting (=) at the beginning

• wrong cell reference
• mixing text and numbers
• inconsistent number format
• dividing by zero (#DIV/0!)
• copying formulas without fixing constants ($)
Function Wizard

What is Function Wizard?

Function Wizard is a built-in Excel tool that helps you:
• search for functions
• understand what a function does
• fill in the required arguments correctly
It reduces mistakes when using functions

You can open Function Wizard from:

Option 1: Option 2:

Click the fx
button next to Go to
the Formula Bar Formulas → Insert Function
Function Wizard

How to use Function Wizard (Steps)

• Click the target cell where the result will appear

• Click fx (Insert Function)
• Select a function category (e.g., Math & Trig, Statistical, Logical)
• Choose the function (e.g., SUM, AVERAGE, IF)
• Fill in the arguments (ranges or values)
• Click OK
Basic Statistical Function

Statistical Function for Worksheet Analysis

Statistical functions help you summarize and understand
business data quickly, such as:
• Total sales quantity
• Average sales value
• Highest and lowest transactions
• Number of transactions

Common Statistical functions:

• SUM
• AVERAGE
• MAX
• MIN
• COUNT
• COUNTA
• COUNTIF
Basic Statistical Function

• Total Quantity?
• Average Unit Price?
• Highest Total Sales?
• Lowest Total Sales?
• How many numeric quantity records?
• How many product entries?
• How many coffee transactions?
Date and Time Function

Date and Time Functions

We use date/time functions to analyze transaction dates,
such as:
• extracting year/month/day
• grouping data by month
• calculating time differences
• generating today’s date automatically

Date/time functions help you:

• organize transactions by month or year
• create reports by period
• check deadlines and time-based analysis
• standardize date formats in business worksheets
Date and Time Function

TODAY() WEEKDAY()
Returns the current date Returns the day number of the date
Useful for identifying weekday/weekend patterns
NOW()
TEXT() – for Date Display
Returns the current date and time
Convert a date into a readable month name
YEAR(), MONTH(), DAY()
Example : =TEXT(B2,"mmmm")
These functions extract parts of a date

DATE()
Creates a valid date from year, month, and day
Example : =DATE(2026, 1, 15)

DATEDIF()
Calculates difference between two dates
Example (B2 = Start Date, C2 = End Date) :
=DATEDIF(B2, C2, "d")
Text Function

Why Text Functions Matter?

Text functions help you clean and standardize data, such as:
• fixing inconsistent product names
• removing extra spaces
• extracting codes from text
• combining text for labels and reports
Text cleaning is important for accurate worksheet analysis

Common Text functions:

• LEN()
• LEFT() and RIGHT()
• MID()
• TRIM()
• UPPER(), LOWER(), PROPER()
• CONCAT()/TEXTJOIN()
Text Function

LEN() TRIM()
Returns the number character in a cell TRIM removes extra spaces in text (very useful for messy data)
Use case: Use case:
checking if product codes have the • fixing “ Coffee ” → “Coffee”
correct length
LEFT(text, num_chars) and RIGHT(text, num_chars) UPPER(), LOWER(), PROPER()

Extract characters from the left/right side Change text case

Use case: =UPPER(C2) → COFFEE
Extracting product codes, branch codes, =LOWER(C2) → coffee
or category labels =PROPER(C2) → Coffee
MID() Use case:
Extract characters from the middle of a text • standardizing product or customer names
=MID(C2,2,4) CONCAT()/TEXTJOIN()
Use case:
Meaning:
• creating labels like: 2026-01-03 - Coffee
• start from character 2, take 4 characters
Use case:
• extracting specific parts from an ID format
Logical Function

Why Logical Functions Matter?

Logical functions help Excel make decisions based on conditions, such as:
• classifying sales performance (Good / Low)
• applying discount rules
• checking data validity
• generating business decision labels automatically

Common Logical functions:

• IF()
• AND()
• OR()
Logical Function

IF() – Most Important

IF returns one value if a condition is TRUE, and another value if FALSE
Syntax: =IF(logical_test, value_if_true, value_if_false)
Example: =IF(D2>=5,"High","Low")
AND()
AND returns TRUE if all conditions are TRUE
Syntax: =AND(condition1, condition2, ...)
Example: =AND(D2>=5,E2>=20000)
OR()
OR returns TRUE if at least one condition is TRUE
Syntax: =OR(condition1, condition2, ...)
Example: =OR(D2>=10,E2>=40000)
Logical Function

Combining IF() and AND()

Example: give “Priority” label for important transactions
Assume:
• Quantity in D2 =IF(AND(D2>=5,E2>=25000),"Priority","Normal")
• Unit_Price in E2

Combining IF() and OR()

Example: flag transaction that need attention =IF(OR(D2=0,E2=0),"Check Data","OK")

Nested IF()
=IF(F2>=200000,"High",
Nested IF is used for multiple categories IF(F2>=100000,"Medium","Low"))
Practice Task

Applying Basic Formulas and Functions in a Transaction Workflow

Instruction
1) Dataset
Use this following dataset
[Link]
1674099438129999&rtpof=true&sd=true

2) Workbook Structure
1. Raw_Data
contains the original transaction data (no modification allowed)
2. Process
contains data processing using formulas and functions
3. Summary
contains summarized results
Practice Task

3) Process
Duplicate the Raw_Data worksheet and rename it as Process, then complete the following steps:

1. Transaction Date Extraction

Add the following columns:
• Transaction Day
• Transaction Month

Extract the day and month information from the Transaction Date column

2. Text Standarization
Add the following columns:
• Channel Standard

Standarize the values in the Sales Channel column, by applying text functions to the Sales Channel
column.
(e.g. Online, Offline, Marketplace)
Practice Task

3. Discount Type Identification

Add the following columns:
• Discount Type

Notes:
• Discount contains % → Percent
• Discount equals 0 → None
• Otherwise → Amount

4. Discount Value Calculation

Add the following columns:
• Discount Value

Notes:
• Percent → Unit Price * Discount
• Amount → Discount
• None → 0

Use one consistent formula that can be copied to all rows

Practice Task

5. Transaction Value Calculation

Add the following columns:
• Net Unit Price
• Total Sales

Notes:
• Net Unit Price = Unit Price – Discount Value
• Total Sales = Net Unit Price * Quantity

6. Transaction Validation
Add the following columns:
• Transaction Status

Notes:
• If Net Unit Price < 0 → Invalid
• Otherwise → Valid
Practice Task

7. You may Rearrange the columns in the Process worksheet so that the final column order follows the
logical transaction workflow (from raw data to calculated results)

For example:
- invoice no
- transaction date
- transaction day
- transaction month
- item name
- sales channel
- channel standard
- quantity
- unit price
- discount
- discount type
- net unit price
- total sales
- transaction status
Practice Task

4) Summary
Create a new worksheet named Summary.
This worksheet is used to display aggregated results, not detailed transaction data

Notes
• Do not copy the transaction table into this worksheet
• Place summary labels in column A and calculated values in column B
• Leave at least one empty row between the worksheet title and the summary values

Suggested Summary Layout

Cell Content • Type the worksheet title, Sales Transaction Summary

A1 Sales Transaction Summary • Type the summary labels, Total Quantity, Total Transactions, Average Unit Price,
A3 Total Quantity Average Net Unit Price, Total Sales Value, Average Sales per Transaction
A4 Average Unit Price • Enter the corresponding formulas using data from the Process worksheet
A5 Total Transactions
B3 (formula)
B4 (formula)
B5 (formula)