Excel Functions by eduCBA Global Online Training Experts

Author of lecture Excel Functions

eduCBA Global Online Training Experts

Customer reviews

Excerpts from the accompanying material

... a nested manner and in combination with other functions will save hours of time. WHAT DOES IT DO? This function tests a condition. If the condition is met, it is considered to be TRUE - If the condition is not met, it is considered as FALSE - Depending upon the result, one of two actions will be carried out SYNTAX = IF(Condition, Action If True, Action If False). The Condition is usually a test of two cells, such as A1=A2 ...

... the OR function would be used in conjunction with a function such as = IF SYNTAX = OR (Test1,Test2). Note that there can be up to 30 possible tests. When used by itself, it will show TRUE or FALSE. ...

... ARGE / SMALL / MAX / MIN / TRUNCABS. WHAT DOES IT DO? This function calculates the positive value of ...

... position in the list SYNTAX = SMALL (List Of Numbers To Examine, Position To Pick From) EXAMPLE 3 456 7 8 E F G H I Highest Value 800 = LARGE (C4:C8,1). Values 2nd Highest Value 250 = LARGE (C4:C8,2) 120 3rd Highest Value 120 = LARGE (C4:C8,3)800 4th Highest Value 120 = LARGE (C4:C8,4) 100 5th Highest Value 100 = LARGE (C4:C8,5) 120 250 3 ...

... data SYNTAX = MAX (Range1, Range2, Range3 ... through to Range30) EXAMPLE MIN WHAT DOES IT DO? This function picks the lowest value from a list of data SYNTAX ...

... COUNTA / COUNTBLANK / COUNTIF COUNT WHAT DOES IT DO? This function counts the number of numeric ...

... number, such as = COUNTIF (A1:A5, 100). To match a piece of text type the text in quotes, such as = COUNTIF (A1:A5 ,"Hello") To match using operators surround the expression with quotes, such as = COUNTIF (A1:A5,">100") 3 4 5 67 8 9 1011 12 C D E F G H Entries To Be Counted ...

... correct medal. The =IF has been used to filter out any positions above 3, as this would cause the error of #VALUE to appear, due to the fact the = CHOOSE has only three items in it. 3 4 56 7 8 9C D E F G H Index Value Result 1 Alan = CHOOSE (C4,"Alan","Bob","Carol") 3 Carol =CHOOSE (C5,"Alan","Bob","Carol") 2 Bob = CHOOSE (C6,"Alan","Bob","Carol") ...

... UP V LOOK UP WHY YOU NEED TO KNOW THIS? Used for data-mining of relevant stock specific data WHAT DOES IT DO? This function scans down the row headings at the side of a table to find a specified item. When the item is found, it then scans across to pick a cell entry SYNTAX = VLOOKUP (Item To Find, Range To Look In, Column To Pick From, Sorted Or Unsorted) ...

... SYNTAX = HLOOKUP (Item To Find, Range To Look In, Row To Pick From, Sorted Or Unsorted). The Item To Find is a single item specified by the user. The Range To Look In is the range of data with the column headings at the top. The Row To Pick From is how far down the column the function should look to pick from. The Sorted / Unsorted is whether the column headings are sorted. TRUE for yes, FALSE for no. ...

... TAX = MATCH (What To Look For, Where To Look, Type Of Match). The Type Of Match either 0, 1 or -1. Using 0 will look for an exact match. If no match is found, the #NA error will be shown. Using 1 will look for an exact match, or the next lowest number if no exact match exists. If there is no match or next lowest number, the error #NA is shown. The list of values being examined must be sorted for this to work correctly. Using -1 will ...

... $30.0 Tube Light 100 2000 Britts $1.8 205 $180.0 Broom 100 2000 ZeroK $0.8 105 $40.0 Broom 40 1000 ZeroK $0.1 205 $10.0 Brand These two cells are the Criteria range. Type the brand name: Britts The average cost of Britts is: 3.4 = DCOUNT (B3:I19,F3,E14:E15) EXCEL FUNCTIONS LEARNING OBJECTIVES Database Functions - DAVERAGE / DCOUNT / DSUM his chapter discusses the most time saving and complex functions provided by Excel. Take your time to digest the concepts as these will prove very handy. ...

... The second set of information is the actual record, or records, which are to be selected, such as ZeroK as a brand name EXAMPLE 3 45 6 789 1011 121314 1516 17 B C D E F G H I Product Wattage Life Hours Brand Unit Cost Box Quantity Boxes In Stock Value Of Stock Broom 200 3000 ZeroK $4.5 4 3 $54.0 Tube LIght100 2000 ZeroK $2.0 152 $60.0 Broom 80 1000 ZeroK $0.2 403 $24.0 Krit 100 unknown ZeroK $1.3 ...