Basic Formulas and Functions (SUM, AVG, MAX, MIN)0%

Basic Formulas and Functions (SUM, AVG, MAX, MIN)

Beginner12 min read•Updated: Jul 11, 2026

Basic Formulas and Functions (फॉर्मूले और बुनियादी फंक्शन्स)

The true magic of spreadsheets lies in automated mathematical computation. A spreadsheet allows you to calculate totals, averages, percentages, and statistical limits instantly across thousands of rows.

1. The Cardinal Rule: The Equals Sign (=)

CRITICAL RULE FOR CCC EXAM: Every single formula or function in LibreOffice Calc and MS Excel MUST begin with an equals sign (=)! If you type SUM(A1:A5) without =, the spreadsheet will treat it as simple plain text!

2. Difference Between Formula and Function

Formula (फॉर्मूला): A custom user-defined mathematical expression using operators:
Example: =A1 + B1 * 10
Function (फ़ंक्शन): A pre-defined, built-in formula provided by the spreadsheet software that accepts arguments and returns a calculated result:
Example: =SUM(A1:A10)

3. The Big 5 Core Functions

A. SUM() - योग (Total)

Calculates the total sum of all numbers in a range:

1=SUM(A1:A10)          --> Adds numbers from cell A1 through A10
2=SUM(A1, B1, C1)      --> Adds individual separated cells

AutoSum Shortcut: In Calc, press Alt + Sigma or in Excel press Alt + = to automatically detect adjacent numbers and insert the SUM formula!

B. AVERAGE() - औसत (Mean)

Calculates the arithmetic mean by dividing the sum of values by the count of numbers:

1=AVERAGE(B1:B5)       --> If values are 10, 20, 30, average is 20

C. MAX() - अधिकतम मान (Highest Value)

Finds the largest numerical value in a range:

1=MAX(C1:C100)         --> Finds the highest marks in a class

D. MIN() - न्यूनतम मान (Lowest Value)

Finds the smallest numerical value in a range:

1=MIN(C1:C100)         --> Finds the lowest marks in a class

E. COUNT() vs COUNTA()

=COUNT(A1:A10): Counts ONLY cells containing NUMBERS. Ignores blank cells and text!
=COUNTA(A1:A10): Counts ALL non-empty cells (Numbers + Text labels + Dates).
=COUNTBLANK(A1:A10): Counts only empty/blank cells.

4. Relative vs Absolute Cell Referencing ($ Sign)

When you copy a formula across rows:

Relative Reference (सापेक्ष - e.g. A1): By default, cell addresses change automatically relative to where you copy the formula (e.g. copying =A1+B1 down to the next row becomes =A2+B2).
Absolute Reference (स्थिर - e.g. $A$1): Placing a dollar sign ($) in front of the column letter and row number locks the cell address so it never changes when copied!
Shortcut Key to Toggle References: Press F4 (or Shift + F4 in LibreOffice Calc) to cycle between A1, $A$1, A$1, and $A1.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

With which character must all formulas and functions begin in LibreOffice Calc?

Question 2

Which function calculates the highest numerical value in a range of cells?

Question 3

What is the difference between COUNT() and COUNTA() in spreadsheets?

Question 4

What symbol is used in spreadsheets to create an Absolute (fixed/locked) cell reference (e.g. $A$1)?

Question 5

What is the output of the formula =SUM(5, 10, 15)?

Next Lesson

Sorting, Filtering, and Freezing Panes

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Editing, Formatting Cells, Rows, and Columns	Sorting, Filtering, and Freezing Panes

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Sorting, Filtering, and Freezing Panes

Basic Formulas and Functions (SUM, AVG, MAX, MIN)

Beginner12 min read•Updated: Jul 11, 2026

Study Materials

Basic Formulas and Functions (फॉर्मूले और बुनियादी फंक्शन्स)

1. The Cardinal Rule: The Equals Sign (=)

2. Difference Between Formula and Function

Formula (फॉर्मूला): A custom user-defined mathematical expression using operators:
Example: =A1 + B1 * 10
Function (फ़ंक्शन): A pre-defined, built-in formula provided by the spreadsheet software that accepts arguments and returns a calculated result:
Example: =SUM(A1:A10)

3. The Big 5 Core Functions

A. SUM() - योग (Total)

Calculates the total sum of all numbers in a range:

1=SUM(A1:A10)          --> Adds numbers from cell A1 through A10
2=SUM(A1, B1, C1)      --> Adds individual separated cells

AutoSum Shortcut: In Calc, press Alt + Sigma or in Excel press Alt + = to automatically detect adjacent numbers and insert the SUM formula!

B. AVERAGE() - औसत (Mean)

Calculates the arithmetic mean by dividing the sum of values by the count of numbers:

1=AVERAGE(B1:B5)       --> If values are 10, 20, 30, average is 20

C. MAX() - अधिकतम मान (Highest Value)

Finds the largest numerical value in a range:

1=MAX(C1:C100)         --> Finds the highest marks in a class

D. MIN() - न्यूनतम मान (Lowest Value)

Finds the smallest numerical value in a range:

1=MIN(C1:C100)         --> Finds the lowest marks in a class

E. COUNT() vs COUNTA()

=COUNT(A1:A10): Counts ONLY cells containing NUMBERS. Ignores blank cells and text!
=COUNTA(A1:A10): Counts ALL non-empty cells (Numbers + Text labels + Dates).
=COUNTBLANK(A1:A10): Counts only empty/blank cells.

4. Relative vs Absolute Cell Referencing ($ Sign)

When you copy a formula across rows:

Relative Reference (सापेक्ष - e.g. A1): By default, cell addresses change automatically relative to where you copy the formula (e.g. copying =A1+B1 down to the next row becomes =A2+B2).
Absolute Reference (स्थिर - e.g. $A$1): Placing a dollar sign ($) in front of the column letter and row number locks the cell address so it never changes when copied!
Shortcut Key to Toggle References: Press F4 (or Shift + F4 in LibreOffice Calc) to cycle between A1, $A$1, A$1, and $A1.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

With which character must all formulas and functions begin in LibreOffice Calc?

Question 2

Which function calculates the highest numerical value in a range of cells?

Question 3

What is the difference between COUNT() and COUNTA() in spreadsheets?

Question 4

What symbol is used in spreadsheets to create an Absolute (fixed/locked) cell reference (e.g. $A$1)?

Question 5

What is the output of the formula =SUM(5, 10, 15)?

Next Lesson

Sorting, Filtering, and Freezing Panes

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Editing, Formatting Cells, Rows, and Columns	Sorting, Filtering, and Freezing Panes

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next