Functional Skills



Excel Formulas and FunctionsFinding a total/adding numbersFor all numbers in a row or column=sum(first cell:last cell)For selected numbers in a row or column=sum(first cell, next cell, next cell)There are a range of other tasks you can complete using this procedure=average=medianAny time you type = followed by letters, options for various formulas/functions will appear.Basic formulas >4 (greater than 4)<4 (less than 4)>=4 (greater than or equal to 4)*4 (contains 4)* (multiply)More complex calculations <= Level 2IF (something is true and if it isn’t)Nested IFCOUNT IFSUM IF (find the sum of something where you find it in a row as it corresponds to its numbers)Lookup (searching for something in a column to tell you something about it)VLookup (as above)IF…Logical test: it rains on SaturdayValue if true: I go to the moviesValue if false: I go to the beach Example: =if(B4>150,Sell,Keep)LOOKUP…“I want you to find me the price of a certain drink from all the drinks in a list!” In the above example you need to find the price of drinks; the price is dependent on a drinks code. We’re basically going to ask Excel to find the code on the left in the list of codes on the right and then give us the corresponding prices.Example =Lookup(ST,BB:WW,5.10:5.00)SUMIF…In a list find me all the listings for Bar Italia, then add up all the income at Bar Italia In the above example we have a chart to complete. In the chart you need to list the total income from different drinks at each club. Three clubs selling different drinks. So you’ll say find from these list of bars, their corresponding bar in the box and their corresponding prices.NESTED IF…“If it rains I’m staying home, If it’s sunny I’m going to the beach, If I get paid I’m buying a surfboard and if the surfs up I’m surfingA nested if function is like a normal IF function, but this time you have different possibilities scenarios and therefore different possible outcomes. In the above example we want to display different values depending on the drinks code. If ‘N’ matches D we want it to display 5.50, if N matches F, we want it to display 0.00, etc. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download