Skip to content
Educora
EN
AzərbaycancaEnglishРусскийTürkçe
My learningEducora StudioSettings
BeginnerGrades 8–922 min8 / 59

Spreadsheet functions

The eleven functions of the DİM programme — SUM, AVERAGE, MAX, MIN, COUNT, DATE, TIME, TODAY, PI, RADIANS, RAND: what they return, how they treat empty and text cells, how they nest; computing their values on a fragment.

Check yourself
In this lesson you will learn
  • Calculate SUM, AVERAGE, MAX, MIN and COUNT on a given fragment, taking empty, zero and text cells into account
  • Evaluate nested functions and formulas with functions from the inside out
  • Find differences of dates and times with DATE, TIME and TODAY
  • State what PI, RADIANS and RAND return and the range of values of RAND formulas

DİM’s 2027 entrance programme lists eleven spreadsheet functions by name: SUM, AVERAGE, MAX, MIN, COUNT, DATE, TIME, TODAY, PI, RADIANS, RAND. In the exam they appear inside address and chart tasks: “which cells does this SUM add?”, “what is the AVERAGE?”, “which formula results is the chart built on?”. In the lesson “Spreadsheets: addresses, ranges and formulas” you wrote formulas yourself; a function is a ready-made formula — you call it by name and it returns the result. In this lesson you learn what each function returns, how it treats empty and text cells, and how functions work inside each other.

What a function is and how to write it

Definition
Function

A ready-made formula with a name. Its arguments — numbers, addresses, ranges or other functions — go inside brackets, and the function returns one result: =SUM(B2:B9), =MAX(A1,C5).

The separator between arguments depends on the regional settings: a comma with English settings (=MAX(A1,C5)), a semicolon where decimals are written with a comma, as in Azerbaijan, Russia and Türkiye (=MAX(A1;C5)). The mini spreadsheet in this lesson accepts both. Functions without arguments still need their brackets: =PI(), =TODAY(), =RAND(). Several ranges can be given together: =SUM(A1:A3,C5) adds A1, A2, A3 and C5. DİM tasks write functions with their English MS Excel names; the Russian and Turkish versions of Excel use other names (for example СУММ and TOPLA for SUM).

inserts =SUM(…) automatically (AutoSum)Alt+=
opens the Insert Function dialogShift+F3
recalculates the sheet: RAND gives a new numberF9

Statistical functions: SUM, AVERAGE, MAX, MIN, COUNT

FunctionWhat it returns
SUMthe sum of the numbers
AVERAGEthe arithmetic mean of the numbers
MAX, MINthe largest and the smallest number (0 if there are no numbers)
COUNTthe number of cells that hold numbers (a date is a number too)

The key rule: inside a range these five functions take only numbers. Empty cells and text are ignored, while 0 is a number and counts. That is why AVERAGE does not include empty cells in the divisor: an empty cell and a cell holding 0 are not the same.

AVERAGE(D) = SUM(D) / COUNT(D)AVERAGE(D) = SUM(D) / COUNT(D)
where:
  • Da range (or a list of arguments)
  • COUNT(D)how many numbers D holds — empty and text cells are not counted

The mean is the sum divided by the number of cells that hold numbers, not by the number of all cells.

ABC
18x4
209
363
Fragment for Examples 1 and 2: B1 holds the text x, A2 and C3 are empty.
Example 1. Functions on a fragment

For the fragment above, calculate =SUM(A1:C3), =COUNT(A1:C3), =AVERAGE(A1:C3), =MAX(A1:C3), =MIN(A1:C3).

Show solution
The range has 9 cells, but the only numbers are 8, 4, 0, 9, 6, 3 (B1 is text, A2 and C3 are empty).
SUM = 8 + 4 + 0 + 9 + 6 + 3 = 30.
COUNT = 6 (the 0 counts too).
AVERAGE = 30 / 6 = 5 (not 30 / 9 ≈ 3.33!).
MAX = 9, MIN = 0.
Example 2. Nested functions

For the same fragment, calculate:
1) =SUM(A1:B3)/COUNT(A1:B3) and =AVERAGE(A1:B3)
2) =MAX(SUM(A1:A3),SUM(C1:C3))
3) =MAX(A1:C3)-MIN(A1:C3)
4) =SUM(A1:C1,C2)

Show solution
1) The numbers in A1:B3 are 8, 0, 6, 3 → sum 17, count 4, 17 / 4 = 4.25. AVERAGE gives 4.25 too — the formula is the definition of AVERAGE.
2) Inner functions are calculated first: SUM(A1:A3) = 8 + 6 = 14, SUM(C1:C3) = 4 + 9 = 13; MAX(14, 13) = 14.
3) 9 − 0 = 9 (the range of the values).
4) The numbers in A1:C1 are 8 and 4, plus C2 = 9: 8 + 4 + 9 = 21.
Example 3. Finding a cell from the mean

B1 = 6, B2 = 10, and B4 holds the text x. Which number must go into B3 so that =AVERAGE(B1:B4) equals 9? What would AVERAGE be if B3 stayed empty?

Show solution
The text in B4 is not counted, so the numbers are B1, B2 and B3 — three of them.
(6 + 10 + B3) / 3 = 9 → 16 + B3 = 27 → B3 = 11.
If B3 were empty, only two numbers would remain: (6 + 10) / 2 = 8.
Wrong path: dividing by 4 (6 + 10 + B3 = 36 → B3 = 20) — a text cell is not part of the divisor.

Dates and times: DATE, TIME, TODAY

Excel stores a date as a whole number: 1 January 1900 is day 1, and every next day adds 1. A time is a fraction of a day: 12:00 = 0.5, 06:00 = 0.25. The cell shows a date or a time, but it holds a number. That is why the difference of two dates is the number of days between them, and adding 7 to a date gives the day a week later.
DATE(year, month, day) builds a date. A month above 12 rolls over into the next year: =DATE(2026,13,1) is 1 January 2027. TODAY() returns today’s date; it has no arguments and updates every time the file is opened.

Example 4. Calculating with dates

1) What is =DATE(2026,9,30)-DATE(2026,9,1)?
2) What does =DATE(2026,12,31)-DATE(2026,1,1)+1 calculate?
3) Which date does =DATE(2026,13,1) give?
4) If TODAY() returns 30 September 2026, which date is =TODAY()+100?

Show solution
1) 30 − 1 = 29 days (a difference; counting both end days would give 30).
2) The number of days in 2026: 364 + 1 = 365 (2026 is not a leap year).
3) Month 13 is month 1 of the next year → 1 January 2027.
4) October has 31 days, November 30, December 31: from 30 September to 31 December is 92 days, 8 more → 8 January 2027.
TIME(h, m, s) = h/24 + m/1440 + s/86400TIME(h, m, s) = h/24 + m/1440 + s/86400
where:
  • h, m, shours, minutes, seconds
  • 24, 1440, 86400hours, minutes and seconds in a day

TIME returns a fraction of a day (from 0 to 1). Multiply by 24 to get hours, by 24 · 60 to get minutes.

Example 5. The TIME function

1) Which numbers do =TIME(6,0,0) and =TIME(18,30,0) return?
2) What is =TIME(1,30,0)*24?
3) A lesson starts at 08:30 and ends at 10:05. What does =(TIME(10,5,0)-TIME(8,30,0))*24*60 calculate?

Show solution
1) 6/24 = 0.25; 18/24 + 30/1440 = 0.75 + 0.0208… ≈ 0.7708.
2) 1 hour 30 minutes = 1.5 hours: (1/24 + 30/1440) · 24 = 1.5.
3) The difference is 1 hour 35 minutes; · 24 · 60 turns it into minutes: 95 minutes.

PI, RADIANS and RAND

PI() returns π accurate to 15 digits: 3.14159265358979. The area of a circle is =PI()*B1^2, its circumference =2*PI()*B1. RADIANS(angle) converts degrees to radians. This matters because Excel’s trigonometric functions (such as SIN) expect radians: =SIN(30) is the sine of 30 radians, while =SIN(RADIANS(30)) gives 0.5.

RADIANS(α) = α · π / 180RADIANS(α) = α · π / 180
where:
  • αthe angle in degrees
  • π / 180π / 180one degree in radians ≈ 0.01745

180° = π radians. For the reverse conversion Excel has the DEGREES function.

Example 6. PI and RADIANS

1) B1 holds the radius 5. What are =PI()*B1^2 and =2*PI()*B1?
2) What are =RADIANS(180), =RADIANS(90), =RADIANS(60)?
3) Find the value of =RADIANS(45)*4/PI().

Show solution
1) π · 25 ≈ 78.54; 2 · π · 5 = 10π ≈ 31.42.
2) 180° = π ≈ 3.1416; 90° = π/2 ≈ 1.5708; 60° = π/3 ≈ 1.0472.
3) RADIANS(45) = π/4; (π/4) · 4 / π = 1.

RAND() returns a random real number from 0 to 1: 0 ≤ x < 1 (it can be 0 but never 1). Every time the sheet is recalculated — when any cell changes or F9 is pressed — a new number appears. So you cannot say in advance what a RAND cell will show, only in which interval its value lies.

=RAND()*(b − a) + a → a ≤ x < b
where:
  • a, bthe ends of the interval; RAND() · (b − a) stretches the length, + a shifts it

For a whole number add the integer-part function INT: =INT(RAND()*6)+1 gives 1 to 6, like a dice.

Example 7. The range of RAND values

Which values can these take?
1) =RAND()*10
2) =RAND()*5+2
3) =INT(RAND()*6)+1

Show solution
1) 0 ≤ RAND() < 1; times 10 gives 0 ≤ x < 10: [0, 10).
2) Times 5 gives [0, 5); adding 2 gives [2, 7).
3) RAND()*6 lies in [0, 6); INT takes the whole part: 0, 1, …, 5; +1 → 1, 2, 3, 4, 5, 6.
Interactive
Loading simulation…
The mini spreadsheet supports SUM, AVERAGE, MAX, MIN, COUNT, PI, DATE and TODAY; try RAND, TIME and RADIANS in Excel or Google Sheets.

Functions are also the basis of charts: a chart is often built on the results of functions and formulas — see the lesson “Charts in spreadsheets and their elements”. Professional use of these functions is covered in the Excel course lessons “Core functions: SUM, AVERAGE, MIN, MAX, COUNT” and “Dates and text: professional functions”.

Key points

  • A function is written =NAME(arguments); functions without arguments still need brackets: =PI(), =TODAY(), =RAND().
  • In a range SUM, AVERAGE, MAX, MIN and COUNT take only numbers: empty and text cells are ignored, 0 counts; AVERAGE = SUM / COUNT.
  • A date is a day number (1 = 1 January 1900), a time is a fraction of a day: the difference of two dates is a number of days, and TIME(h, m, s) = h/24 + m/1440 + s/86400.
  • PI() ≈ 3.14159265358979; RADIANS(α) = α · π / 180 — Excel’s SIN and COS expect radians.
  • RAND() gives 0 ≤ x < 1 and changes on every recalculation; =RAND()*(b−a)+a lies in [a, b).

Check yourself

12 questions. Every correct answer earns XP.

1 / 12
Which function returns how many cells of a range contain numbers?