CHAPTER # 6 QUESTION BAN

1 -20
2 WORKSPACE = GROUP OF WORKBOOKS
3 = is used at the start of every formula
4 to divide =A2/A1
5d
6 =WEEKDAY(TODAY();) error
7 SHORTKEY TO APPLY FORMULA IN WHOLE SHEET = F9
8 MOD
9 sum_range = are actual cells to be added if their corresponding cells in range match
10 SUMIF(DEFINE RANGE)
11 Functions Of SumIfs = 1) used to add cells that meet multiple criteria. 2) Sum range is the third argument in sumif fu
12 " " and "?" can be used as criteria
13 to find ? And * we use ~
14 third argument in sumif is sum_range
15 Count only counts numbers
16 to find count "Count(range)"
17 Counta counts everything including empty space date alphabets
18 diff b/w count and countif is criteria
19 Criteria can be in number,expression,cell reference
20 Countif(A1:A5,"*") will count number of cell containing text
21 For True/False If is used
22 First argument in If is logical text second true third false
23 If col_index_num <1 , Vlookupwill give value error
24 If lookup value is smaller than the smallest value in the first column of table vlookup will give #N/A error
25 V in Vlookup stands for vertical
26 Vlookup searches for a value in the first column of a table array
27 Average can be calculated for all/multiple cells or two diff cells
28 Average measures mean
29 SUBTOTAL Will include hidden values if function_num is used
30 total range of ref1 in subtotal is from 1 to 254
31 Function_num for average is 1 or 101
32 a
33 d
34 b
35
36 Count doesnot count empty cells
37 Trim removes all spaces except for the default one
38 Average doesnot include empty cells
39 =AVERAGEA(N35:N39)=2.4
40 SUBTOTAL COUNTS HIDDEN CELLS
41 a
42 Financial is used for calculating loans,depriciation rate of return etc
43 SLN FUNCTION RETURNS THE depreciation on its asset
44 NPV FUNCTION Discount is applied on net of cumulative payments and income
45 Count if counts on particular criteria
46 c
47 b
48 Round function is used to round off numbers
49
50 d
STION BANK

e third argument in sumif function. 3)Up to 127 criteria/range pairs are allowed in sumifs function

give #N/A error

6 2.4
4
2
0
ABC
isblank() SYNTAX OUTPUT
=ISBLANK(B3) 1
=IF(ISBLANK(B4),"blank","filled") BLANK
iserror()
20 =ISERROR(B6) 0
#DIV/0! =IF(ISERROR(B7),"error","no error") ERROR
#N/A =IF(ISERROR(B8),"error","no error") ERROR
5 =IF(ISERROR(B9),"error","no error") NO ERROR
iserr()
20 =ISERR(B11) 0
#DIV/0! =IF(ISERR(B12),"error","no error") ERROR
#N/A =IF(ISERR(B13),"error","no error") NO ERROR
5 =IF(ISERR(B14),"error","no error") NO ERROR
Islogical()
20 =ISLOGICAL(B16) 0
0 =IF(ISLOGICAL(B17),"TRUE","FALSE") FALSE
1 =IF(ISERR(B13),"TRUE","FALSE") FALSE
0 =IF(ISERR(B19),"TRUE","FALSE") FALSE
Islogical()
1 =ISLOGICAL(B16) 0
0 =IF(ISLOGICAL(B17),"TRUE","FALSE") FALSE
1 =IF(ISERR(B13),"TRUE","FALSE") FALSE
0 =IF(ISERR(B19),"TRUE","FALSE") FALSE
ISNA()
#N/A =ISNA(B26) 1
0 =IF(ISNA(B27),"TRUE","FALSE") FALSE
ISNONTEXT()
RAYAN =ISNONTEXT(B29) 0
69 =IF(ISNONTEXT(B30),"TRUE","FALSE") TRUE
ISNUMBER()
RAYAN =ISNUMBER(B32) 0
69 =IF(ISNUMBER(B33),"TRUE","FALSE") TRUE
ISREF()
=ISREF(A7) 1
=ISREF(b34) 1
IFERROR()
25/0 =IFERROR(B38,"EXCEPTION") EXCEPTION
25/5 =IFERROR(B38,"EXCEPTION") 5
ABS()
-3 =ABS(-3) 3
69 =ABS(69) 69
CEILING()
7,2 =CEILING(7,2) 8
11,11 =CEILING(11,4) 12
FLOOR()
7,2 =FLOOR(7,2) 6
11,11 =FLOOR(11,4) 8
INT()
17.9 =INT(17.9) 17
17.2 =INT(17.2) 17
mod()
(5,2) =MOD(5.2) 1
22,5) =MOD(22.5) 2
ROUND()
(13.7589,2) =ROUND(13.7589,2) 13.759
22.5890,3) =ROUND(22.5899,3) 22.59
exact()
SAME,sAMe =EXACT("sAMe","sAMe") 1
Pak,paK =EXACT("Pak","paK") 0
left()
Pakistan =LEFT("Pakistan",3) Pak
Pakistan =LEFT("Pakistan",1) P
lower()
PAKISTAN =LOWER("PAKISTAN") pakistan
pAKIsTaN =LOWER("pAKIsTaN") pakistan
mid()
PAKISTAN =MID("PAKISTAN",1,2) PA
pAKIsTaN =MID("PAKISTAN",3,3") KIS
PROPER()
pakiStAn =PROPER("pakiStAn") Pakistan
aCC 121 =PROPER("aCC 121") Acc 121
right()
PAKISTAN =RIGHT("PAKISTAN",1) N
Pakistan =RIGHT("Pakistan",4) stan
teXT()
17.69 =TEXT(17.69,"PKR #.##") Err:502
2323.899 =TEXT(2323.899,"PKR #.#") Err:502
trim()
longaccountend =TRIM(" LONG ACCOUNT END") LONG ACCOUNT END
lumber one =TRIM (" Lumber ONE") Lumber ONE
upper()
paKISTan =UPPER("paKISTan") PAKISTAN
len()
PAKISTAN,ASIA =LEN("PAKISTAN,ASIA") 13

PAKISTAN,PROPER("ZINDABAD
0
CEILING FLOOR
7 8 8 7 8 0
28 4 28 28 4 28
-28 3 -27 -28 3 -30
-29 -13 -39 -29 -13 -26
29 -5 Err:502 29 -5 Err:502
ABC 2 #VALUE! ABC 2 #VALUE!
If either argument is nonnumeric,CEILING returns the #VALUE! Error value.
If number is negative,and significance is negative,the value is rounded down,away from zero.
If number is negative,and significance is positive,the value is rounded up towards zero.

MOD ROUND
7 8 7 28.1212 2 28.12
28 4 0 23.887 1 23.9
-28 3 2 87.0867 0 87
-29 -13 -3 56.7192712 0 57
28 0 #DIV/0! 3789.232 -3 4000
ABC 2 #VALUE! 250.97 -2 300
If num_digits is greater than 0 (zero),then number is rounded to the specified number of decimal place
If num_digits is 0, the number is rounded to the nearest integer.
If num_digits in less than 0,the number is rounded to the left of the decimal point
1 12
2
3
4
5
6
7
8
9
10
ue.
n,away from zero.
owards zero.

fied number of decimal places.

mal point
AND + IF
STUDENT MARKSHEET
REG. NO NAME BC QT I.T ENG TOTAL PER (%) GRADE
1 GHOST AH 72 87 51 56 266 66.5 B
2 DANCREW 51.2 65 59 54 229.2 57.3 C
3 LEVINHO 77 56 60 80 273 68.25 B
4 SEVOU 51 76 79 55 261 65.25 B
5 PANDA 54 68 99 75 296 74 A
6 DYNAMO 80 88 97 87 352 88 A+
7 SHROUD 39 73 29 40 181 45.25 FAIL
REMARKS
PASS
PASS
PASS
PASS
PASS
PASS
FAIL
5
SUM =SUM(D7:D8)
10 =SUM(D7:D8)
20 =SUM(D7:D9,TRUE)
1 =SUM(D7:D10,FALSE)
0 =SUM(D7:D11)
a =SUM(10,"a",TRUE)
1-Jan =SUM(D7:D13)

VALUES FORMULA
15000 =SUBTOTAL(1,D14:F24)
28000 =SUBTOTAL(2,D14:F24)
41000 =SUBTOTAL(3,D14:F24)
54000 =SUBTOTAL(4,D14:F24)
67000 =SUBTOTAL(5,D14:F24)
80000 =SUBTOTAL(6,D14:F24)
93000 =SUBTOTAL(7,D14:F24)
106000 =SUBTOTAL(8,D14:F24)
119000 =SUBTOTAL(9,D14:F24)
132000 =SUBTOTAL(10,D14:F24)
145000 =SUBTOTAL(11,D14:F24)

FORMULA RESULT
=AVERAGE(F17:F27) 80000
=MEDIAN(2,4,2,6,8) 4
=MODE(1,3,3,7,8,9) 3
=COUNT(F17:F27) 11
=COUNTA(F17:F27) 11
=PERCENTILE 3
=PERCENTILE 3
=PERCENTILE 3
SUM =SUM(D7:D8) 15
=SUM(D7:D8) 15
=SUM(D7:D9,TRUE) 16
=SUM(D7:D10,FALSE) 15
=SUM(D7:D11) 15
=SUM(10,"a",TRUE) #VALUE!
=SUM(D7:D13) 43846

RESULT FUNCTION_NUM
80000.00 1=AVERAGE
11.00 2=COUNT
11.00 3=COUNTA
145000.00 4=MAX
15000.00 5-MIN
111910131300934000000000000000000000000000000000000000.00 6=PRODUCT
43116.12 7=STDEV.S
41109.61 8=STDEV.P
880000.00 9=SUM
1859000000.00 10=VAR
1690000000.00 11=VARP
COUNT
5 =COUNT(D8:D13) 3
7 =COUNT(D5:D10,200,4,5) 6
1 =COUNT(D5:D10,,"b",TRUE,FALSE) 6
1/2/1900 =COUNT(D5:D10,FALSE) 4
1 =COUNT(D5:D10,"1") 4
ABCDEFGHIJKLMNOP =COUNT(D5:D10)

COUNTIF
5 =COUNTIF(D18:D21,"5") 1
7 =COUNTIF(D18:D23,"7") 1
1 =COUNTIF(D18:D23,"1") 1
5 =COUNTIF(D18:D23,"5") 2
COUNTA
5 =COUNTA(M5:M10) 6
7 =COUNTA(M5:M10,200,4,5) 9
1 =COUNTA(M5:M10,,"b",TRUE,FALSE) 10
1/18/2020 =COUNTA(M5:M10,FALSE) 7
1 =COUNTA(M5:M10,"1") 7
ABCDEFGHIJKLMNOP =COUNTA(M5:M10) 6

ACHA WALA COUNTIF