CALCULATIONS vs FORMULAS vs FUNCTIONS
CALCULATIONS FORMULA FUNCTION
1 28 28
2
3
4 SLOW SLOW FAST
5 NOT DYNAMIC DYNAMIC DYNAMIC
6
7
Syntax Order or Structure
Virat scored Runs
Subj + Verb + Obj
1 2 3 4 5
Syntax of a Function = Function name ( Argument/Arguments )
Order Player Name Player Code Team Role Runs Balls
1 Rohit Sharma (c) Ro1Ba India Batsman 9 5
2 Virat Kohli Vi2Ba India Batsman 76 59
3 Rishabh Pant † Ri3Wi India WicketKeeper 0 2
4 Suryakumar Yadav Su4Ba India Batsman 3 4
5 Axar Patel Ax5Al India All-Rounder 47 31
6 Shivam Dube Sh6Al India All-Rounder 27 16
7 Hardik Pandya Ha7Al India All-Rounder 5 2
8 Ravindra Jadeja Ra8Al India All-Rounder 2 2
9 Extras Extras 7
1 Reeza Hendricks Re1Ba South Africa Batsman 4 5
2 Quinton de Kock † Qu2Wi South Africa WicketKeeper 39 31
3 Aiden Markram (c) Ai3Al South Africa All-Rounder 4 5
4 Tristan Stubbs Tr4Al South Africa All-Rounder 31 21
5 Heinrich Klaasen He5Ba South Africa Batsman 52 27
6 David Miller Da6Ba South Africa Batsman 21 17
7 Marco Jansen Ma7Al South Africa All-Rounder 2 4
8 Keshav Maharaj Ke8Bo South Africa Bowler 2 7
9 Kagiso Rabada Ka9Bo South Africa Bowler 4 3
10 Anrich Nortje An10Bo South Africa Bowler 1 1
11 Extras Extras 9
SR
180 SUM Total runs scored in the match 345
128.81
0
75
151.61 COUNT How many players played 20
168.75 COUNTA How many players played 20
250 COUNTBLANK How many blanks 2
100
80 AVERAGE Average runs scored 17.25
125.8
80 SUMIF Total runs scored by India 169
147.61
192.59 SUMIFS Total runs scored by Indian All-rounder 81
123.52
50 COUNTIF How many players are of India 8
28.57 How many players scored more than 30 5
133.33
100
COUNTIFS How many South African Batsmen 10
How many players scored >20 and <30
AVERAGEIF Average runs of Indian Players
AVERAGEIFS Average runs of South African Bowlers
MAX What is the Highest Score
MIN What is the least Score
MAXIFS What is the Highest Score of SA Batsman
MINIFS What is the least Score of Indian All-roun
LARGE What is the 5th Highest Score
SMALL What is the 8th Least Score
RANK What is the Rank of 31 (Desc)
What is the Rank of 31 (Asc)
MEDIAN
MODE
Description
Using Mouse - Select and Scroll
Selecting First Cell, using : and then selecting Last Cell
Selecting First Cell, use Shift + Ctrl + Down arrow
COUNT function will count only Numericals
COUNTA function will count Text, Numericals & Combination of both
COUNTBLANK function will count only the blank cells
Calculates the Average (Sum/Count)
Calculates SUM with applied one condition
Calculates SUM with applied multiple conditions (upto 127)
Calculates COUNT with applied condition
Calculates COUNT with applied multiple conditions
Calculates AVERAGE with applied condition
Calculates AVERAGE with applied multiple conditions
Calculates MAX VALUE from a range
Calculates MIN VALUE from a range
Calculates MAX VALUE from a range with applied multiple conditions
Calculates MIN VALUE from a range with applied multiple conditions
Returns the nth largest value from a range
Returns the nth smallest value from a range
Returns the rank of a number from a list/range
Returns the median (middle most number when sorted)
Returns the most frequently occurred number
Questions
Total Score of both teams
Total Score of India
Total Score of Indian Batsmen
Highest score in match
Highest score by Indian
Highest score by Indian All rounder
Least score by South African
Average score of India
Average score of Indian Batsman
Data INT ROUND ROUNDUP ROUNDDOWN
10.2456
5.2
4.6
8.7
9.6
2.3
6.5
4.8
1.5
Data POWER ^ ROMAN ARABIC
2
3
4
5
6
7
8
9
Data SQRT Data ABS
4 -4
9 -9
16 -16
25 -25
36 -36
49 -49
64 -64
81 -81
REPT
