BISM – Excel Notes PDF

Preview

Summary

Excel Notes...

Description

BISM – EXCEL NOTES EXAM FORMAT Coverages  Everything after mid-sem  No BPMN material in final exam- only lecture theory from WK 5 onwards  EXCEL is tested o MCQ’s  IF, CONCATENATE, NOW, DSUM, VLOOKUP, AND, AVERAGE, operator precedence, operators, and what was in the Assignment (e.g. Name Manager use/purpose) o Short Answer  Focus on Information Systems Development (Question 1 – Part B – 15 marks) and Business Intelligence (Question 2 – Part B – 15 marks)  Question 3- Excel is tested on ONE short answer question (15 marks)  (a),(b),(c),(d)-3 formulas needed- one in each  Also (d) a theory/practice question on Excel  Focus on IF, CONCATENATE, OR, MAX, SUM, TEXT, WEEKDAY, NOW, IFERROR, VLOOKUP    PART A: (total 15 marks)  30 MCQ- 15 MCQ on Excel, 15 are text/lecture theory question  0.5 marks per MCQ PART B: (total 45 marks)  3 Short Answer Question  Each question worth 15 marks  Each-short answer question (1 to 3) comprises of sub-parts  Must answer all questions

WEEK 3 

The INSERT FUNCTION - we can use it for HELP o Can select built-in function and different categories o ‘Window into all built-in functions excel has’ – how to write the function o



More Excel functions and formulas (SUM, AVERAGE, COUNT, MAX, MIN, DAY, MONTH) – and consider the parameters/arguments these functions can accept/take (some take NONE)



Errors (syntactic and semantic) - error messages



The concept of a return value



Conditional formatting (WE WANT TO DRAW ATTENTION TO VARIOUS CELLS



Using CONCATENATE to produce headings (Personnel Details as at 'today's date'), with dates - introduce 2 ways, including TEXT function o Joining multiple arguments together in one cell= concatenate o Excel represents dates as numbers o EXAMPLE 1: =CONCATENATE(“My”,“_”,”Car”) o EXAMPLE 2: =”My”&”_”&”Car” =TODAY()-E4)/365 to determine years



Relative addressing- no dollar symbols Absolute- all dollar symbols Mixed- mixed in the middle NAMED RANGE FORMULA TAB- DEFINE NAMES

WEEK 5 IF Functions (logical function category)

Comparison operator(above): A symbol that indicates the relationship between two values 

The argument value_if_true is optional. The square brackets in the syntax description define the argument as optional. This argument (if coded) represents the value returned if the logical_test argument evaluates to TRUE.



The argument value_if_false is optional. This argument (if coded) represents the value returned if the logical_test argument evaluates to FALSE.



In huge majority of cases, we shall code both value_ if_true and value_if_false. So we can safely make the following most important statement for our work

  

Our IF functions will always have three arguments. The first argument logical_test will always be evaluated. The 2nd argument value_if_true will only ever be evaluated if the logical_test evaluates to TRUE. The 3rd argument value_if_false will only ever be evaluated if the logical_test evaluates to FALSE.



A “nested’ IF function- an IF function written/nested within Excel Addressing (show MIXED addressing) Relational operators (remember: operators – built-in functions – formulas) IF functions (several examples – and assignment discussion)- SEE ABOVE AND functions (syntax – from the INSERT FUNCTION operator - and operation)  Shows either TRUE or FALSE result  Use when you want an IF Function with multiple tests being conducted OR functions (syntax – from the INSERT FUNCTION operator - and operation

WEEK 6   

Finish LOGICAL functions (IF, AND, OR) Named Ranges VLOOKUP functions – theory and practice

VLOOKUP functions We can use the VLOOKUP function to search the first column of a range of cells, and then return a value from any cell on the same row of the range. For example, suppose that we have a list of employees contained in the range A2:C10. The employees' ID numbers are stored in the first column of the range, as shown in the following illustration.

If we know the employee's ID number, we can use the VLOOKUP function to return either the department or the name of that employee. To obtain the name of employee number 38, we can use the formula =VLOOKUP(38, A2:C10, 3, FALSE). This formula searches for the value 38 in the first column of the range A2:C10, and then returns the value that is contained in the third column of the range and on the same row as the lookup value ("Axel Delgado"). The V in VLOOKUP stands for vertical. VLOOKUP is matched with another function called HLOOKUP – where the H stands for horizontal. The only difference between the two is how the lookup table is structured. HLOOKUP uses a lookup table structured as rows (VLOOKUP has a table structured as columns). HLO OKUP EXAMPLE:

VLOOKUP Syntax Syntax- how we write a function so excel understands it VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) The VLOOKUP function syntax has the following four arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure): lookup_value This argument must be provided by you. The value to search in the first column of the table or range. The lookup_value argument can be a value or a reference. If the value you supply for the lookup_value argument is smaller than the smallest value in the first column of the table_array argument, VLOOKUP returns the #N/A (stands for “value not available”error value. table_array This argument must be provided by you. The range of cells that contains the data. You can use a reference to a range (for example, A2:D8), or a range name. The values in the first column of table_array are the values searched by lookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase texts are equivalent. col_index_num This argument must be provided by you. The column number in the table_array argument from which the matching value must be returned. A col_index_num argument of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. 

If the col_index_num argument is: Less than 1, VLOOKUP returns the #VALUE! error value.

Greater than the number of columns in table_array, VLOOKUP returns the #REF! error value. range_lookup This argument is optionally provided by you (if you do not provide it, Excel will automatically assume the value to be TRUE. A logical value (i.e. value can be either TRUE or FALSE). This argument specifies whether you want VLOOKUP to find an exact match or an approximate match *USUALLY LOOKING UP NAMES USE FALSE, LOOKING UP NUMBERS USE TRUE* *CALLED A ‘LOGIC TYPE’*  If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. 

If range_lookup is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.



If range_lookup is FALSE, the values in the first column of table_array do not need to be sorted.



If the range_lookup argument is FALSE, VLOOKUP will find only an exact match. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.

TAX CALCULATION

--- VLOOKUP Table array = Taxtable

 

We also consider an example in which we shall combine MULTIPLE VLOOKUP function calls to produce an overall Excel formula for TAX CALCULATION. The TAX TABLE above describes a differential tax calculation – it is not FLAT TAX. We must understand WHAT is happening in the calculation BEFORE we can write the Excel formula!

The formula for Tax Payable = Fixed Tax [PLUS] (Excess [MULTIPLIED BY] Percentage on Excess)

Fixed Tax is a VLOOKUP (from column 2)

WEEK 7--- SEE LEC (TICK)- Assignment Stuf

WEEK 8 Filtering Database Advanced Filtering o Specifies complex criteria to limit which records are included in the result set of a query  Normally used to filter listing of clients for example o Found under DATA tab- Advanced o Can CLEAR Advanced Filtering- (Button above advanced filtering) o  AND/OR Advanced filtering o First row only – AND filtering  i.e Filtering for First name AND Last name of a client o First & Second row – OR Filtering  i.e Filtering for First name of one and client or First name of another client *If you use OR filtering and only select one row, excel will still filter it but you get back your whole worksheet* 



DATABASED FUNCTIONS: o DSUM  3 arguments- =DSUM (Database, field, criteria)  Use to get accurate sum after list has been filtered  =DSUM(Client_Listing, "Salary",Filter_AND) o DAVERAGE o DCOUNT  =DCOUNT (Database, field, criteria)  =DCOUNT(Client_Listing, "Date", Filter_AND)



Using IF or IFERROR to catch obvious problems in filtering

o If use IF, use DSUM as test  E.G: =IF(DSUM(A3:F27,6,A34:F36)=0, "No records found",DAVERAGE(A3:F27,6,A34:F36)) o OR use IF ERROR as it is easier  IF ERROR takes two arguments  Runs the function, if it works then IF ERROR doesn’t apply  E.G: =IFERROR(DAVERAGE(A4:F27,6,A34:F36),"No records found")

WEEK 9- SEE LEC 

In EXAM write ‘we have declared a named range Product Table (Prod_Table)

Q1. The Total Order Cost/Kg of each order (heading “Total Order Cost/Kg”) is calculated based on the data contained within the table. The Total Order Cost/Kg figure is the sum of Price/Kg, Freight/Kg and Insurance/Kg. You will develop a formula combining multiple VLOOKUP functions to produce this data. You must use the appropriate named ranges when using VLOOKUP functions. The resulting data should be formatted as Currency with two decimal places. Total_Order_Cost = Price/Kg of product + Freight/Kg of product + Insurance/Kg of product Each of these is a VLOOKUP - we are looking up for an EXACT MATCH - we only want a "per Kg" value. What about ERRORS? TRUE instead of False? Wrong table coordinates to VLOOKUP? Missing value(s) in lookup table?

(3 marks) ½ marks taken off for each error Potential errors: Wrong table coordinates, TRUE instead of FALSE, Missing values in VLOOKUP table #N/A= name not in the table Solution: =VLOOKUP(D2,Prod_Table,2,FALSE)+VLOOKUP(D2,Prod_Table,3,FALSE) +VLOOKUP(D2,Prod_Table,4,FALSE)

Q2. The Preferred Purchaser Discount % (heading “Preferred Purchaser Discount %”) is allowed to certain favoured customers of the business. These customers are listed in the Preferred Customer Table. The indexing in this table is the concatenation of three values: the first 3 characters of the customer name, the underscore character, and the customer suburb (e.g., Smi_Aspley). You must retrieve the appropriate preferred customer discount rate from this table. You must also recognise that only a small percentage of customers are given “preferred” status. This means that a lookup of the table may not always return a positive result. You will develop a formula combining VLOOKUP, &, LEFT and IFERROR. You must use the appropriate named range within your VLOOKUP function. The resulting data should be formatted as Percentage with zero decimal places. Use CONDITIONAL FORMATTING to highlight peferred customer entries. 1. We want to firstly create the "lookup value". This will be the following three items CONCATENATE: 1) the left three characters of the purchaser name (LEFT(A2,3) 2) the undersore value "_" 3) the customer suburb (cells B2:B15) 2. We use the value constructed in (1) as the lookup value in a VLOOKUP. 3. We want to allow for an error (because not all customers are in the table) - we use IFERROR 4. Finally we use CONDITIONAL FORMATTING

Solution: =IFERROR(VLOOKUP(LEFT(A2,3)&"_"&B2,Cust_Table,2,FALSE),0)

Possibility of discount: (Could be in exam) =E2*F2*(1-G2) Total Order Cost= Volume Ordered(kg) * Total Order Costs/Kg * (1-Purchaser Discount )  If no discount the ‘1’ stays , if there is discount execute above formula

DGET Function  Syntax: DGET(database, field, criteria)  Extracts from a database a single record that matches the conditions you specify  Might ask what arguments DGET will take – will not ask to write it out/put into a formula - MCQ



Q1. In this example, we shall be searching for 'Purchaser Name'. Last week we did this by ADVANCED FILTERING. This week we shall be using DGET (paragraph 9 of Excel Assignment). DGET is within the database function group (within the Insert Function Operator) It extracts a single value from a column of a list or database that matches conditions you specifiy. Syntax : DGET(database, field, criteria) IF no record matches the criteria, DGET returns the #VALUE! error value (numeric value 3) IF more than one record matches, DGET returns the #NUM! error value (numeric value 6) We can test the error value via the call ERROR.TYPE(error_val). Error type will return the NUMERIC VALUE.



=IFERROR(DGET(Listing,8,Criteria)," Enter data in A17")

ERROR. TYPE Syntax: =ERROR.TYPE(#VALUE!)

WEEK 10 Build a simple ‘dashboard’  Will be asked MCQ about Dashboard  Wouldn’t need to know in depth – NO SHORT ANSWER



Table Slicer o Allows user/analyst to ‘unpack’ and simplify data o

 PRAC EXAM Q: The graphic below shows sales for Monday, Tuesday and Wednesday. A formula is needed in cell B4 to achieve the following business function: If there are zero sales figures in the range B1:B3, show the message “Nil Sold” in cell B4. If there are sales in the range B1:B3, show the message as per the structure currently displayed in B4 (i.e. 'Total sold: 5 for 05/05/2018' where 5 represents the actual number of sales calculated via the SUM function, and 05/05/2018 is the date of the current day as calculated by the built-in functions TEXT and TODAY (using the formatting string “dd/mm/yyyy”). Please ensure you produce the spacing as shown in the example format. You must use a combination of IF, CONCATENATE, SUM, TEXT, and TODAY functions for the formula. (Part a - 4 marks total)



Concatenate reads TODAY function as number of days since 1/1/1900

SOLUTION: =IF(SUM(B1:B3)=0,"Nil Sold",CONCATENATE("Total sold: ", SUM(B1:B3)," for ",TEXT(TODAY(),"dd/mm/yyyy")))

WEEK 11 

LEC CONTAINS EXAM PREP Q’S (MCQ + PRACTICAL)

MCQ Examples: Which of the following statements best describes the diference between an Excel formula and an Excel function? • a user writes a formula, Excel provides a function as “built-in” • a formula always starts with an = sign, a function never needs an = sign • a formula never uses operations, a function always uses operations • a user writes a function, Excel provides formulas as “built-in”

Which of the following accurately relates to ‘dates’ data in Excel? • Excel internally stores all dates as numeric data. • Excel can subtract one ‘date’ from another ‘date’. • the today() function returns the current date and the today() function takes zero arguments. • all of the above.

*WILL MOST LIKELY GET SIMILAIR TO Q3 IN FINAL EXAM* Question 1: We want a formula in D10 that calculates the total charge of the order. Question 2: The business now introduces a discount for high-valued customers. These customers are shown in a table (High-Valued Customers). The discount offered varies according to the value of business flowing from the customer. We want the formula in Question 1 adjusted accordingly. Question 3: Explain the SYNTAX of VLOOKUP - your answer should cover: the number of arguments, the type of each argument, an explanation of each argument (including any conditions EXCEL places on the arguments, or the use of the arguments) Question 4: If we coded (in question 1 or 2) the fourth argument as TRUE. What would this mean and what would the results be for Brown, Jones, and Charlton? (explain your answer)

WEEK 12 SEE EXCEL file on BB  Goes through using TEXT, TODAY

Where is conditional formatting found? HOME/Conditional Formatting/Highlight Cell Rules/Text that Contains PRACTICE MCQ’s The function Weekday returns what type of data? 1. either the text string “Saturday” or “Sunday” 2. one of the text strings “Monday” to “Friday” inclusive 3. a number 1…5 representing the weekday of the week 4. a number 1…7 identifying the day of the week of the input date In cases where we are using DAVERAGE to average the records returned via advanced filtering, we must plan for the EXCEL error: 1. value not available 2. zero records returned by filtering 3. division by zero

4. no data returned by AND/OR filtering The TEXT function returns: 1. a text representation of the input data 2. a logical representation of the input data 3. the logical result of evaluating the input test 4. the current day’s date formatted in a specific number format The OR function is accurately described as: 1. taking 1…255 arguments 2. returning TRUE only if all argument conditions evaluate to TRUE 3. returning FALSE if any input argument condition evaluates to FALSE 4. all of the above

WEEK 13 - REVISION  Goes through layout of Exam and Prac Exam General Notes: MGTS1201- Past exam papers 255 arguments in total on excel – 2^8-1

UCTT – How to Approach Excel Questions U- Understand the Problem C – Conceptual solution – Algebraic equation T- Translate to excel T[T]TUT12 =C11*(VLOOKUP(B11,OCT,2,FALSE)+VLOOKUP(B11,OCT,3,FALSE))*(1IFERROR(VLOOKUP(A11,HVC,2,FALSE),0))

Square brackets []- means optional, if leave nothing excel assumes TRUE What function does, what the outputs will be, argument name (recisely as they appear in ecel), data types for those, any errors that can arise form the function ERROR.TYPE- will determine the numeric error type – Output: numbers 1-8 depending on error type , N/A- if thing inside is working- no error code to get from it...