Grade (10 ) 1CT/Information sheet/ Functions &Formula Formulae ‘A formula uses basi arithmetic calculation. There are Plus, minus, divide, and multiply , for example =BS+B6. There is no limit to the number of arithemetic calculations that can be used for example=(BS+R6)/*3/100-(D1-D2). Functions ‘A Function isa ready-made formula representing a complex calculation For Example um(A5:B10) or =Average (Cosi).it is reserved Word within the spreadsheet software the processes a serie of predefined formulae. Types of Fuctions Function Name ‘SUM(B3IE3) Calculates the total of values within a range, “T =sum( 2 | Spam Rel | [IMIF(SRS25:SRS98, BIT,SFS25:SFS98) UMIF((SBS25:SBS98,"<0") ‘Adds up all values that mest a eriterion wit range. Flronge, eters, [um enged “=AVERAGE (B3:E3) > Calculates the averageof values within o range. 2 meknenee, pani ~AVERAGEIF (SDS4:SDS64,G3,SESUSESG4) |, AVERAGE (85S3:555200,">0") Calculates the average of all values that meet a ceterion within a range. 3.) [AVERAGEFrange ctr fverage.rnge)=COUNT(REES ) Count the number of numeric(number) values in a list. 2: -COUNT( 3. [COUNTIvalue, value, .) =COUNTA(BSES) ‘Works in similar way to the count function Rather than counting just the number of numeric cr text values displayed in the cells, it will not count any blank cells. 5 v 1 izcoumy 3 | [COUNTAaue, Falvey =) =COUNTIFSBSSSESS, HT") Counts the values that meet a criterion within a range. (2 =COUNTF; et 3 [COUNTIF range, ctr) =COUNTBLANK(BS:E3) Use the COUNTBLANK function to count blank cells in a range. OUNTBLANK( ‘COUNTBLANK(range) IF(AI=S,A2"0.05,"n0 discount") An IF function contains a pair of brackets and within the brackets three parts, cach separated by a comma. The first partis a condition he other two parts are what to do ifthe condition is met , and shat to do if itis not met =F 3 [Hoga est flue rue re # Fed 4 =VLOOKUP(B8,JXDEST csv 'SAS2:SBSI12,0) Isa function that performs a vertical look up of | data .this should be used when the values that you ish to compare your data with are stored in a lecolumn. ILOORUF( 3. [WOOK tick lg ale ay colina age ooh 4 LOOKUP(B8.JXDEST .csv!SAS2:SBS11.2) Ica flnetion that performs a horizanttal lock up of data .this should be used when the values that you swish to compare your data with are stored in a single row. T HLOOKUP( 3] HOO lage be ry, ow dena, angele =LOOKUP(BT,SASS:SASI2,SBS3:SBSI2) 1s used to look upa value using data in the first row or the first columa of a range of cells and returns a relative value .For our purposes. this is probably the least usefil of the three formula LOOKUP] ‘OOKU" feokep_valie, skip vec, FEAST LOoKUP tok)=XLOOKUP(B3,SBS2:3B58,SAS2.SA38,"Not found” 0,1) is a new function in Exce! that can be used to pecform cither a horizontal or a vertical look up of data. This is similar to HLOOKUP and VLOOKUP,but is more powerful and flexible than cither ofthese. It will also reference data stored in rows/columns before the lookup value. It therefore allows backward referencing within an array. The values to be looked up can be stored to either the right or left or above or below the lookup array. The look up data can be stored either in the same file or ina different file. frcon] LOOK oot a oskup a eum a our, ate, searh =MIN(B3:E3) Calculates the smallest value within a range. =MIN{ 3.) [MN@umberl, [numbed -) =MAXGEES) Calculates the largest value within a range. 3 TIF(AND(G7>14,F7>7)," Extra Larges" ") =AND(AI>0,A1<10) eee) isa logical function used to require more 4 than one condition at the same time. AND returns either TRUE or FALSE. XAL="y") place specified. 4 PPER(AZ) converts & text string to al s Uppercase Iter Eason 3] 1 UPPER text) =LOWER(A2) convensall wppenacletcsina [A 8 text string to lowercase. AIH 625512 Function What itdaes =ROUND(AT2) Rounds the contents of Alto two decimal paces =ROUND(AT.1) 026 Rounds the contents of AT to one decimal pace. Note that the sevond figure Sin {62.55 has forced the previous figure to be rounded up =ROUND(ATD) a Rounds the contents of Al to one decimal pace. Note that the first Figure Sin £62.55 12has forced the previous figure to be rounded up =ROUND(AT1y o ‘Rounds the contents of Alto the nearest 10 “The negative value for decimal places allows this function to round numbers in tens hundreds... =ROUNDXAT,2) 100 Rounds the conteaiso Alto the nearest 10D Note that the figure 6 has forced the previous figure to be roundedup from Oto 1 Explain, using examples, the difference between function and a formula in a spreadsheet Four from: [A formula is a statement written by the user te he calculated. An equation | formula can contain values/raferences to cellsidafined names Examples =A1#B1 ete {A function is a piece of code designed fo retuin specific values. Pass parametersivariabies to functions. Function has a pre-defined names in the softvareireserved word, They are used inside formulas. A builtin operation Examples SUM/AVERAGE! NOW) ete To gain full marks candidates need to mention both a function and formula,