Scribd
Upload
3 views8 pages

Advanced Spreadsheet Formulas & Functions

The document provides an overview of using formulas and functions in spreadsheets, including definitions and examples of both. It illustrates practical applications through a sales record for a supermarket, detailing steps for calculations, sorting data, and using the filter function. Key examples include calculating total sales, discounts, and net sales, as well as organizing data for analysis.

Uploaded by

Thom Huwa
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
3 views8 pages

Advanced Spreadsheet Formulas & Functions

The document provides an overview of using formulas and functions in spreadsheets, including definitions and examples of both. It illustrates practical applications through a sales record for a supermarket, detailing steps for calculations, sorting data, and using the filter function. Key examples include calculating total sales, discounts, and net sales, as well as organizing data for analysis.

Uploaded by

Thom Huwa
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd

ADVANCED Form 4 - Phwezi

SPREADSHEETS
Review: What is a Formula?
● A formula performs a calculation using cell
references, numbers, and operators.

● Always starts with = (equals sign).


● Examples:
○ =A1 + B1
○ =C2 * 10
○ =(A1 + A2 + A3)/3
Review: What is a Function?
● A function is a built-in formula that performs a specific
task automatically.
● Examples:
○ =SUM(B2:B10) – adds up values in range
○ =AVERAGE(C2:C10) – calculates mean
○ =MAX(D2:D10) – finds highest value
○ =COUNT(A2:A100) – counts how many cells contain
numbers
Using Formulas and Functions
for Practical solutions
Let’s consider a Sales Record for a supermarket:

Item Category Unit Price Quantity Sold Total Sales Discount Net Sales

Apples Fruit 500 80

Bread Bakery 1000 45

Milk Dairy 1200 60

Soap Household 1500 30

Sugar Grocery 900 70

Juice Beverage 2000 55


Example: Calculating with
Formulas and Functions
Steps:
1. In Total Sales column → =C2*D2
2. Copy formula down the column.
3. In Discount, use a condition:
=IF(E2>70000, E2*0.05, 0)
4. In Net Sales, calculate:
=E2 - F2
Example: Calculating with
Formulas and Functions
5. Find summary values below the table:

○ Total Sales: =SUM(E2:E7)

○ Average Net Sales: =AVERAGE(G2:G7)

○ Highest Net Sale: =MAX(G2:G7)


Sorting Data
Purpose: Organize data for better analysis.
Example Tasks:
● Sort by Category (A–Z)
● Sort by Net Sales (Largest to Smallest)
Steps:
1. Highlight entire dataset.
2. Go to Data → Sort.
3. Choose the column and order.
4. Click OK.
Using the Filter Function
Purpose: Display only the records that meet specific criteria.
Example:
● Show only Fruit items.
● Display products with Net Sales > 50,000.
Steps:
1. Select header row.
2. Go to Data → Filter.
3. Click drop-down arrow on a column header.
4. Choose filter conditions.

You might also like