ACIS 2504 Notes - Cynthia Easterwood Important Functions including Syntaxes of each. PDF

Preview

Summary

Cynthia Easterwood
Important Functions including Syntaxes of each. ...

Description

ACIS 2504 NOTES: CHAPTER 1 Creating templates from existing workbook  SAVE AS .xltx file o Goes into Personal Template under “New” Spellcheck  Shortcut F7  If you select single cell: Spellcheck whole sheet o Range of cells: A1+B1*C1^D1  37  Assume the following formula is in cell B3: =A2+C4+$D5  If the formula is copied to cell E2, what formula will appear in cell E2? o = D1+F3+$D4 o o Because B to E is 3 right. Then 1 up. So every letter moves 3 right. Every # moves up 1. Then the $D4 doesn’t matter becsuse $ means D doesn’t shift left/right. Only the 4 moves and it would be up 1. RICI Notation= Row 1 Column 1  R1C2 = B1

CHAPTER 2 Know what paste options are  Linked pictures update whereas pictures do not. Inserting new row/columns  Format is same as above if shift cells down  Format is the same as left if shift cells right Deleting Cells  Will automatically shift existing cells to the left Merge and Center  Excel deletes all text except whats in the most left cell  Use center across selection to not make it delete. CENTERS all text not get rid of it. o Dialouge  select it

Cell Styles  Premade tile style for tables. Can include formatting such as orders, fill, color, font size. Conditional Formatting (referred as data visualization)  Top bottom = Top 10/ Top 2, Bottom based off of % or #.  Can make new rule.  Manage Rules On tests: Will ask if something CAN be done not what button to click.

Class Example Icons  Specifying icons for certain numbers under conditional format: o Icon  More Rules  “New Formatting Rules”

Clearing Conditional Formatting:  Conditional Formatting  Clear Rules Top 3 Months  Top bottom rules o Top 10  Top 3  Green Fill with Dark green text Grand Ballroom own Conditional Formatting Values o Cond. Form.  New RuleUse formula with own formula

THIS CALCULATES IF EVERYTHING WAS $5/Ft. Edit = manage rules and change shit there

CHAPTER 2: Class Example Change Sq. Ft.  If clicking Comma = Accounting format w/o dollar sign  IF WANT COMMA:

Naming with 3D Reference (naming on diff sheet)  FORMULA: =Revenues!D4-Expenses!E9 COUNT  Count (counts numbers)  CountA = Could ranges containing any data type  Countblank = Count empty cells DATE FUNCTIONS  =TODAY() o Gives you computer date  =NOW() o Gives date and time on computer systems  Considered as Volatile because changes every day. Flash Fill and Text Functions (Data Cleansing)  Recognizes simple patterns

Removing Decimals  Find and Replace: o Click Format from Accounting with 2 decimal places to  Format accounting with 0 decimals Change Log  “Documentation”  Date.

CHAPTER 3: Defining names  Highlight all data and o Formula  Create From Selection  (Top Row) o Now going under top left name section, you can click on a title to highlight New Name  “scope” is where it applies. Whole workbook (means only can be one January)  Whole worksheet = multiple existence of same names on individual sheets. Delete Names  Go to “name manager (under formula tab) Naming Ranges Rules  Has to start with a letter, (_) or \ ONLY.  Can’t use space so underscores used in place

Changing Text Functions to  LOWER: all lower case  UPPER: All uppercase  Proper Cap first letter of words  TRIM: Removes extra spaces. Leaves 1 space  CLEAN: Removes non printable characters o =CLEAN(A2) NESTING

Putting one text function within another within another for fixing  Nesting works from inside out. (CPT) o FORMULA: =Trim(Proper(Clean(A2)))  Clean first because removes non-printable first, but leaves spaces. o If trim first, gets all spaces out but then clean = still more spaces after removal. Text Function: FIND  Finds specific character and cell and extract it. o FORMULA: =FIND (find_text,within_text,[start_num])  When finding for a space, have to use “ “ o FORMULA: =FIND(“ ”,D2)  Then use =LEFT(D2, E2-1) to find last name word) or =RIGHT 

 FORMULA: =LEN(text) Length – Find = How to get what you want for every cell.  PAGE 31 Slide CH3 Addresses  Number, street, and state should be in all different cells (3) TEXTJOIN  FORMULA: =TEXTJOIN(delimiter, ignore_empty, text1,[text2]…) o Ignore_empty: defult is TRUE CONCAT  No delimiter argument (delimiter says put a space) 

ISOLATING CITY FROM ADDRESS  FORMULA: =MID(D2,FIND(“,”,D2)+2,LEN(D2)-LEN(E2)-5) ISOLATING STATE FROM ADDRESS  =RIGHT(D2,2) o Starting on the right side, only grab 2 characters. CONTACT PERSON – combining names  FORMULA =TEXTJOIN(Delimiter, ignore_empty_text, text1,text2) =TEXTJOIN(“ “, true, H2, I2)

CHAPTER 3 EX. 2:  Text Function: LEFT/RIGHT  Allows extraction of first character of the cell o FORMULA: =LEFT(text,[num_chars]) o FORMULA: =Right(text,[num_chars])  (Default is one) MIDDLE o Extracts characters from left to right based on starting location  FORMULA: MID(text,start_num,num_chars) LENGTH o Calculates length of cell. Spaces counts too

CREATE FROM SELECTION  Creates names for each separate one faster than names box. Formula create from selection. VLOOKUP (HLOOKUP for horizontal values in table) 1. Lookup_Value 2. Table_array 3. Col_Index_num 4. [Range lookup] (optional). (default) True=approx. False is exact match If doing grades  Lowest to Highest. Put lowest possible value for each grade.  Score first, letter grade second  Lookup value has to be in the first column (V)

Logical Operations  Not Equal PMT FUNCTION Pmt: loan payment each peri. Rate: Interest rate per period Nper: total number of periods for repayment Pv: present value (principal – amount borrowed) Fv: future value (amount still owed after the last payment is made) Type: when payments are due (optional)

Loan payment default is beginning of month in excel.  =PMT(rate,nper,pv,[fv],[type]) Make present value neg. to make value positive. 

TRACE PRECEDENTS (source of cell) FormulasTrace Precedents  To find where the Cell came from TRACE DEPENDENT (where the cell is used in)  The 20 is used in 3 and 43 calculations Red lines = error. Blue = very nice CIRCULAR REFERENCE  Can’t add itself.  Can see it in the status bar 

TEST PREP: Text functions Screenshot: Which of the following formulas to do _____ Know how to use functions SPARKLINES Purpose of Spreadsheet (look at recent ppts) 1. What is the purpose of it? a. What will it be used for 2. Design and Plan it a. Flowchart/ pictures? 3. Create the sheet 4. Test for errors 5. Maintain/update WD(40)CTM.

ARRAYS: CTRL SHIFT ENTER or else it won’t work

MODE.SNGL  Excel displays the mode it finds FIRST on the list. So if list is bimodal but 9 is placed above 10 then 9 is displayed MODE.MULT  You have to select the # of cells you think the # of modes are.  If overshoot guess, then excel will display N/A. o Ex. Biomodal List  select 2 cells  =mode.mult(select list you want excel to look at) press CTRL SHIFT ENTER FORECAST ERROR  If guess is 10k but actually 8k o Forecast error would be -2k MEAN ABSOLUTE DEVIATION (MAD)  First calculate forecast error  take absolute value of Forecast error # with =ABS(number)  Calculate =AVERAGE(value from =ABS)  Aka average of =ABS(difference from F error) STDEV.S (sample STD) STDEV.P (population STD) Syntax: STDEV.S(number 1, number 2)  USE Differences for #, not absolute value ones)  DO NOT USE =ABS(#)  STDEV #’s usually larger than MAD b/c outliers RANK.EQ or RANK.AVG Syntax: (number, ref, [order])  Number: the rank # youre looking for  Ref: the table youre looking in  [Order] o 0 or leave blank = DESCENDING ORDER OF TABLE o Non 0 # = Table is in Ascending order RANK.EQ = First # it finds RANK.AVG = Takes averages of duplicate values

DATABASE FUNCTIONS (CH 6) Begins with ‘D” =DAVERAGE, =DMAX, =DMIN, =DSUM, SYNTAX: FunctionName(database, field, criteria)  Database: Column and Row w/ Headers. o Nameing it is easier  Field: Column used in calculation  Criteria o Has to include column headings o And criteria underneath o AND criteria = same row o OR Criteria = separate row

Highlight this for the Criteria section of syntax:  Wildcards  *(any # of characters) o Used when not know whole thing  ?(any single character) Applying it:

LOGICAL FUNCTIONS

 

AND: Everything has to be true to be true OR: Only one has to be true to be true

NOT Functions  Reverses everything =IFS ARGUMENT  Important to put values from highest to lowest, like letter grade options o =IFS(D2>=90,"A",D2>=80,"B",D2>=7 0,"C",D2>=60,"D",TRUE,"F") MATCH Function Match will return the row but index tells what is inside the cell INDEX MATCH Function SYNTAX: =INDEX(arrag,row_num,[column_num])  Returns what inside of the cell  INDIRECT FUNCTION  Looks what is inside a cell and uses it as reference. “if OFFSET Function 1st argument: Where we looking at 2nd argument: how many rows away we looking at 3rd argument: how many columns away we looking 4th argument: how many cells (up and down) to look at 5th argument: how many cells (across) to look at SYNTAX: (reference, row, column, [height], [length]) (+) Row = move down (+) Column = move right (-) Row = move up (-) Column = move lef

TEXT Function YYYY = 4 digit year DDDD = Day of the Week DD = MMMM = Month Name MM = The number of month Decimal to fraction =TEXT(A3,”#?/?”) NPV Remember to subtract initial investment for difference

EXAM 3 REVIEW CHAPTER 3 Data validation: ensures data is correct Data cleansing: Ensures imported data is free from wingdings, converting data into useful format Flash fill option: can make mistakes; NOT Dynamic. Text function: is better Text Functions: LOWER, UPPER, PROPER, TRIM(removes extra spaces from text. Always leave one space), CLEAN(removes non printable characters) TEXTJOIN(delimiter,ignore_empty normally true Used for cases with same delimiter CONCAT, CONCATNATE: used when have different delimiters NESTED IF:

LOOKUPS: Data must be ascending order A WATCH WINDOW: used for to see how values changed as changes are made in a worksheet Add expression to it, CHAPTER 5 Data Tables: Have “scalability” meaning has ability to automatically update more data and grow. Thanks to structured reference:

Table Guidelines: Have descriptive lables and columnheadings Each Column Heading should be unique Keep data type the same within columns Where is the custom autofilter? The damn arrow

Adding Total Row to Tables: When filtering data, total row data also adjusts to filter data What are slicers used for?_________________ SLICER VAIRABLES: Grouping: Categorical/goes into rows. Used for filtering Summary: Used for calculations. Aka the values in The Refresh vs Change Data Source. If added new data, use Change data source or else if updating data just use refresh. WHAT IF ANALYSIS DIFFERENTL: Data Tables: helps see changes in 1 to 2 variables and see effect on multiple formulas. Manipulate variables to see desired outcome Goal Seek: uses iterative process to reach target

Scenario Manager: Useful for reporting. Manipulates multiple variables to produce report. SOLVER vs GOAL-SEEK: Solver considers constraits on data. Like goal-seek it is target achieving ROUND vs ROUNDUP: Round = regular round. ROUNDUP= always round up regardless ROUNDDOWN = always round down regardless CONDITIONAL MATH FUNCTIONS:

Know countifs MODE.SNGL: Returns first and only one mode it finds. Statistical Calculations: CALCULATING MAD: Calc Forecast error:Actual Value-Mean or Forecasted Value Calculate ABS value of values Database Functions: Wildcard Characters: For charcters ?? *(any character Logical Functions: Put AND(TRUE,TRUE) before the parentheses IFS Function: The TRUE at the end means everything else that’s not specified = whatever comes after it in the syntax Know index match. Not error messages EXACT is ZERO OFFSET:...