Personal computers in business, Summaries of Computer Science

Download Personal computers in business and more Summaries Computer Science in PDF only on Docsity!

lOMoARcPSD|

P. McWilliams,

Personal computers in business

Module 1 and Module 2 ● Workbook- collection of worksheets ○ Worksheet- single sheet in a workbook file that lets you enter and manipulate data, perform calculations with data, and analyze data Module 3 ● Spreadsheets help analyze data, represent data through charts, model real world situations, perform what is analysis ● Steps ○ Determine purpose ■ What decision will be made? What's the question? ■ What criteria will be used to evaluate? ● What calculations/data ■ Who uses it? ○ Design ■ More time on design means less time fixing ■ Design so it is easy to understand and maintain/update ○ Create ■ Include documentation ● Make all elements descriptive and well explained so all can understand ○ File name, date, who created, purpose/objectives, input, where did data come from, calculations, output, instructions, change log* ■ Include instructions ■ Separate input, analysis, output ■ Break down complex formulas ■ Used named ranges ■ Only enter data source once ■ Do not put numbers directly into formulas ■ Data validation and error checks ■ Test the spreadsheet as you build it ■ Make sure it looks nice ○ Test ■ Don't wait until the end ■ Use sample data ○ Maintain/update ■ Data/formulas may change overtime ■ Document changes and save a copy before changes ● Error checks should be built in ○ Link all error checks ○ Formula that adds up errors and display a message if they exist ● READ 2 ARTICLES- EXAM 1

Conventions for Naming Ranges

• Names must start with a letter, or \ (backslash)
• Names should provide specific meaning to the range being named
• Spaces cannot be used Absolute Value formula ABS(number) INT(Number) Rounds down to the nearest integer ROUND(Number,Num_Digits) Rounds to a specified number of digits Average formula and usage AVERAGE(Number1,[Number 2]...) Returns the mean from a set of numbers COUNT formula and usage =COUNT(Value1,[Value2]...) Counts the number of cells in a range that contain numbers COUNTA formula and usage =COUNTA(Value1,[Value2]...) Counts the number of cells in a range that are not empty Upgrade to remove ads Only $2.99/month COUNTBLANK formula and usage =COUNTBLANK(Range) Counts the number of empty cells in a range MEDIAN formula and usage =MEDIAN(Number1,[Number2]...) Returns the number in the middle of a set of numbers MAX formula and usage =MAX(Number1,[Number2]...) Returns the largest number from a set of numbers MIN formula and usage =MIN(Number1,[Number2]...) Returns the smallest number from a set of numbers

MODE.SNGL formula and usage =MODE.SNGL(Number1,[Number2]...) Returns the value that occurs most often within a set DATE formula and usage =DATE(Year,Month,Day) Returns the number that represents the date iN Microsoft Excel date-time code DATEDIF formula and usage =DATEDIF(Date1,Date2,Interval) Returns the time unit specified between two dates, including the two dates (inclusive) DAY formula and usage =DAY(Serial_Number) Returns the day of the month, a number from 1 to 31 MONTH formula and usage =MONTH(Serial_Number) Returns the month, a number from 1 to 12 TODAY formula and usage =TODAY() Returns the computer system date Upgrade to remove ads Only $2.99/month DATEDIF Function What do Date 1 and Date 2 and Interval represent? Date 1: Starting Date (older date) Date 2: Ending Date (more recent date) Interval: Days, Months, or Years (D, M, Y) Common Time Functions HOUR MINUTE SECOND NOW HOUR formula and usage =HOUR(Serial_Number) Returns the hour in a time value, from 0 to 23

=MID(text,start_numb,num_chars) Returns a string extracted from text beginning in position start_num that is num_chars long RIGHT formula and usage =RIGHT(text,[number_chars]) Returns a string num_chars long from the right side of text TRIM formula and usage =TRIM(text) Returns text with any leading, trailing, or extra spaces between words removed Why flash fill is awesome Makes data cleansing easier and faster HLOOKUP function and usage =HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup]) Finds lookup_value in the top row of table_array and returns a value from row_index INDEX function and usage =INDEX(array,row_num,[column_num]) Returns a value from array by indexes specified as row_num and column_num MATCH function and usage =MATCH(lookup_value,lookup_array,[match_type]) Finds lookup_value in lookup_array VLOOKUP function and usage =VLOOKUP(lookup_value,table_array,col_index_num,[Range_lookup]) Finds lookup_value in the first column of table_array and returns a value from column_index PMT usage Calculates periodic payment for a loan based on a constant interest rate and constant payment amounts IPMT Usage Calculates periodic interest payment for a loan based on a constant interest rate and constant payment amounts PPMT usage Calculates periodic principal payment for a loan based on a constant interest rate and constant payment amounts NPV usage

Calculates the net present value based on a discount interest rate, a series of future payments, and future income IF function and usage =IF(logical_test,[value if true],[value if false]) Returns one of two values, depending on whether the statement is true or false IFERROR function and usage =IFERROR(value,value_if_error) Returns a specified value if a function or formula returns an error; otherwise, it returns the value of the function or formula AND function and usage =AND(logical1,logical2...) Returns true if logical1, logical2...logicaln all return true OR function and usage =OR(logical1,logical2...) Returns true if any one of logical1,logical2...logicaln return true #NAME

• Indicates that text in a formula is not recognized
• Excel treats unrecognized text as a named range that does not exist
• Often due to missing quotes around a text string or mistyping a function or range name #REF!
• Indicates a reference that Excel cannot find
• Often due to changes like a deleted worksheet, column, row, or cell #N/A
• Indicates that a value is not available in one or more cells specified
• Common causes include functions that try to find a value in a list, but the value does not exist #VALUE!
• Occurs when the wrong type of argument or operand is used, such as entering a text value when the formula requires a number #DIV/0! This is a division by 0 error or by a cell that contains no value Pie Chart purpose Depicts the relationships of the parts to the whole Column Chart purpose

AVERAGEIF function =AVERAGEIF(range,criteria,[average_range]) AVERAGEIFS function =AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2) Database Function format =DXXX(database, field,criteria) In Database functions, where would AND/OR elements go in terms of the criteria range?

• AND functions would be on the same line
• OR functions would be on separate lines INDIRECT function =INDIRECT(ref_text,[A1]) Scalability The ability to grow and manage more data Slicer Window used for quickly filtering data in an interactive way SUBTOTAL function =SUBTOTAL(function_num,ref1,[ref2]...) SUBTOTAL hidden functions 1 - 3 1: AVERAGE 2: COUNT 3: COUNTA SUBTOTAL hidden functions 4 - 8 4: MAX 5: MIN 6: PRODUCT 7: STDEV.S 8: STDEV.P SUBTOTAL hidden functions 9 - 11 9: SUM 10: VAR.S 11: VAR.P Pivot Table

• Interactive table to extract, organize, and summarize source data
• Used for data analysis and looking for trends and patterns
• Automatically sorts, counts, totals, or gives averages of the data Grouping Variable (Pivot Table)
• A field used to categorize or group for the purpose of comparison
• Placed along top or left side of PivotTable
• Positioned in the COLUMNS or ROWS areas at the bottom of the PivotTable Fields Pane Summary Variable (PivotTable)
• Data that can be aggregated by summing, counting, or averaging
• Positioned in the VALUES area at the bottom of the PivotTable Fields pane Filter Area
• Filters data on the basis of the field or fields placed in that area
• Positioned in the FILTERS area a the bottom of the PivotTables Field Table If new fields are added to source data, the Pivot Table must be... REFRESHED Pivot Charts
• Graphical representation of a PivotTable
• When one is changed, the other is automatically changed
• PiovtTables have drop-down elements with filtering options KEYBOARD SHORTCUT: Down one row ENTER KEYBOARD SHORTCUT: Up one row SHIFT + ENTER KEYBOARD SHORTCUT: To column A of the current row HOME KEYBOARD SHORTCUT: To column A, row 1 (cell A1) CTRL + HOME KEYBOARD SHORTCUT: To the last cell, highest number row and far-right column, that contains info CTRL + END