Q10 Perform The Following Statistical Function in MS EXCEL: (Perform Min 5-5 Functions For Each Formulas) A) Average

Download as pdf or txt
Download as pdf or txt
You are on page 1of 21

Practical 10

Q10 Perform the following Statistical Function in MS EXCEL: (perform


min 5-5 functions for each formulas)
a) AVERAGE
Definition: The most common function we usually use in our daily lives is the average (or
mean). The AVERAGE function simply returns the arithmetic mean of all the cells in a given
range
Syntax: AVERAGE (number1, [number2], …)

b) COUNT
Definition: We use the count function when we need to count the number of cells
containing a number. Remember ONLY NUMBERS! Let’s see the function.
Syntax: COUNT (value1, [value2], …)

c) COUNT A
Definition: While the count function only counts the numeric values, the COUNTA function
counts all the cells in a range that are not empty. The function is useful for counting cells
containing any type of information, including error values and empty text.
Syntax: COUNTA (value1, [value2], …)

d) COUNT IF
Definition: COUNT IF function applies one or more conditions to the cells in the given range
and returns only those cells that fulfill all of the conditions.
Syntax: COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2]…)

e) COUNT BLANK
Definition: The COUNTBLANK function counts the number of empty cells in a range of cells.
Cells with formulas that return empty text are also counted here but cells with zero values
are not counted. This is a great function for summarizing empty cells while analyzing any
data.
Syntax: COUNTBLANK (range)

f) MAX
Definition: The Excel MAX Formula is used to find out the maximum value from a given set
of data/ array. MAX function in Excel returns the highest value from a given set of numeric
values.
Syntax: MAX (number1, [number2], …)

g) MIN
Definition: MIN will return the minimum value in a given list of arguments. From a given
set of numeric values, it will return the smallest value.
Syntax: MIN (number1, [number2], …)

h) IF
Definition: Statistical IF functions provide you with the ability to evaluate the contents in a
cell location before including them in a mathematical calculation.
Syntax: IF (G7=15,"yes","no")

Note: Paste the screenshot and write the formula name ,definition and syntax
Practical 11
Q11 Perform the following Maths Function in MS-EXCEL:(perform
min 5-5 functions for each formulas)
a) ABS
Definition: The abs() function is used to return the absolute value of a given number. The
number may be positive or negative.
Syntax: ABS (number)

b) EXP
Definition: The Excel EXP function returns the result of the constant e raised to the power
of a number.
Syntax: EXP (number)

c) RAND
Definition: This function is used to returns a random number greater than or equal to 0
and less than 1.
Syntax: RAND()

d) RAND BETWEEN
Definition: The function will return a random integer number between the user-specified
numbers. It will return a random integer number every time the worksheet is opened or
calculated.
Syntax: RANDBETWEEN(bottom,top)

e) SQRT
Definition: The Excel SQRT function returns the square root of a positive number. SQRT
returns an error if number is negative.
Syntax: SQRT (number)
f) SUM
Definition: This function is used to adds all the values within a cell range.
Syntax: sum(cell address : cell address)

g) SUMSQ
Definition: The Excel SUMSQ function returns the sum of the squares of the values
provided. Values can be supplied as constants, cell references, or ranges.
Syntax: SUMSQ (number1, [number2], ...)
h) TRUNC
Definition: It removes the fractional part of a number and, thus, truncates a number to an
integer.
Syntax: TRUNC(number, [num_digits])

Note: Paste the screenshot and write the formula name, definition and syntax.
Practical 12

Q12 Perform the following Date and Time function in MS-Excel :


(perform min 5-5 functions for each formulas)
a) TODAY
Definition: One of the simplest ways to input today’s date into a cell is to use the TODAY
function. This places the current date into the cell dynamically. So, that cell would have
tomorrow’s date in it if you reopen the file tomorrow.
Syntax: TODAY()

b) DAY
Definition: The Excel DAY function returns the day of the month as a number between 1 to
31 from a given date.
Syntax: DAY (date)

c) DAYS
Definition: The DAYS function in Excel is a Date/Time function that is used for calculating
the number of days between two dates.
Syntax: DAYS (end_date, start_date)

d) TIME
Definition: The Excel TIME function is a built-in function that allows you to create a time
with individual hour, minute, and second components.
Syntax: TIME (hour, minute, second)

e) NOW

Definition: The NOW function is useful when you need to display the current date and time
on a worksheet or calculate a value based on the current date and time, and have that
value updated each time you open the worksheet.
Syntax: NOW()

Note: Paste the screenshot and write the formula name ,definition and syntax
Practical 13
Q13 Perform the following Text function in MS-Excel :(perform min
5-5 functions for each formulas)

a) CONCATENATE
Definition: The CONCATENATE function in Excel is designed to join different pieces of text
together or combine values from several cells into one cell.
Syntax: CONCATENATE(text1, [text2], …)

b) LENGTH
Definition: The Excel LEN function returns the length of a given text string as the number
of characters. LEN will also count characters in numbers, but number formatting is not
included.
Syntax: LEN (text)

c) FIND
Definition: The Excel FIND function returns the position (as a number) of one text string
inside another. When the text is not found, FIND returns a #VALUE error.
Syntax: FIND (find_text, within_text, [start_num])

d) LEFT
Definition: The Excel LEFT function extracts a given number of characters from the left side
of a supplied text string.
Syntax: LEFT (text, [num_chars])

e) RIGHT
Definition: The Excel RIGHT function extracts a given number of characters from
the right side of a supplied text string.
Syntax: RIGHT (text, [num_chars])

f) MID
Definition: The Excel MID function extracts a given number of characters from the middle
of a supplied text string.
Syntax: MID (text, start_num, num_chars)

g) SUBSTITUTE
Definition: The Excel SUBSTITUTE function replaces text in a given string by matching.
Syntax: SUBSTITUTE (text, old_text, new_text, [instance])

h) EXACT
Definition: The Microsoft Excel EXACT function compares two strings and returns TRUE if
both values are the same. Otherwise, it will return FALSE.
Syntax: EXACT (text1, text2)

i) PROPER
Definition: The PROPER function converts the first character to upper case and rests to
lower case. Basically, the PROPER function in excel is used to convert your input text to
proper case. It can be used to capitalize each word in a given string.
Syntax: PROPER (text)

j) UPPER
Definition: The Excel UPPER function converts a text string to all uppercase letters.
Numbers, punctuation, and spaces are not affected.
Syntax: UPPER (text)

k) TRIM
Definition: The Excel TRIM function strips extra spaces from text, leaving only a single space
between words and no space characters at the start or end of the text.
Syntax: TRIM (text)

Note: Paste the screenshot and write the formula name ,definition and syntax
Practical 14
Q14 Use the table created in question 10, prepare the BAR/COLUMN
chart to compare the result of students on the basis of marks
obtained by the students.
Note: Create the data with student's name, subject's name, marks obtained and total
marks obtained.
HIK BHO RAJ SAR ARU UM TAR UJW
A OMI AT A N AR A AL

SINGH
8

SINGH
7

RAWAT
6

ROLL NO.
DIXIT
5

ENGLISH (100)
KHAN
4

MATHS(100)
ARORA AVERAGE
3

NAWANI
2

ANS

SAXENA
1

0 500 1000 1500

Practical 15

Q15 Use the table created in question 10, prepare the PIE/DONUT
chart to compare the result of students on the basis of marks
obtained by the students.

Note: Create the data with student's name, subject's name, marks obtained and total
marks obtained.

ROLL NO.

1 ANSHIKA
2 BHOOMI
3 RAJAT
4 SARA
5 ARUN
6 UMAR
7 TARA
8 UJWAL

You might also like