Help
Options
Didn't know it?
click below
click below
Knew it?
click below
click below
Don't Know
Remaining cards (0)
Know
retry
shuffle
restart
0:00
Embed Code - If you would like this activity on your web page, copy the script below and paste it into your web page.
Normal Size Small Size show me how
Normal Size Small Size show me how
ISTM Excel Q's
| Question | Answer |
|---|---|
| TODAY() | returns the current date will update every time that Excel recalculates the cells in the worksheet; date will update every time you open the workbook and probably more often than that |
| NOW() | returns the current date and time formatted as a date and time |
| DAY(serial_number) | returns the day portion of a date (a number between 1 and 31) requires a number as the only argument |
| MONTH (serial_number) | returns the month portion of a date ( a number between 1 and 12) requires a number as the only argument |
| YEAR (serial_number) | returns the year portion of a date (a number between 1900 and 9999 requires a number |
| WEEKDAY (serial_number, [return_type]) | returns the day of the week for a date the [return_type] allows the user to define the day on which the week starts |
| WEEKNUM (serial_number, [return_type]) | returns the week of the year for a date the [return_type] allows the user to define the day on which te week starts |
| HOUR (serial_number) | returns the hour portion of a time as a number from 0 to 23 |
| MINUTE (serial_number) | returns the minute portion of a time as a number from 0 to 59 |
| SECOND (serial_number) | returns the second portion of a time as a number from 0 to 50 |
| LEN (text) | returns the length, in number of characters of a block text |
| SEARCH (find_text,within_text,[start_num]) | returns the position of a specific character, word or phrase within a block of text |
| LEFT (text,[num_chars]) | returns a specified number of characters starting from the beginning of a block of text text argument allows you to specify the block of text from which you return the specified number of characters |
| MID (text,start_num,num_chars]) | returns a specified number of characters starting from the end of a block of text |
| UPPER (text) | converts a block of text to all upper-case characters |
| LOWER (text) | converts a block of text to all lower-case characters |
| PROPER (text) | converts a block of text to title-case (the first letter of each new word is capitalized) |
| CONCATENATE (text1,[text2],…) | combines blocks of text allows you to combine text stored in cells in the worksheet or text that is defined within quotes as an argument to the function |
| SUBSTITUTE (text,old_text,new_text,[instance_num]) | replaces specified characters, words, or phrases within a block of text with new characters, words, or phrases |
| SEARCH (find_text,within_text,[start_num]) Define the arguments | find_text is used to specify the characters you would like to find within the block of text within_text argument is used to specify the block of text you wanted searched [start_num] argument to tell the Excel where to start searching the block of text |
| Chart Design tab | contains the items you would use to change the chart type, select or modify the data depicted in the chart, select from various templates for the layout and color scheme for the chart, and select the location of the chart in the workbook |
| Add Chart Element" icon | within the Chart Design tab allows you to change important detail elements of your chart such as: titles, legends, data labels, and the axes of your chart |
| Axes | is used to format the appearance of each of the axes in your chart. You can choose how to display the units for each axis and the intercept for each axis |
| Chart Title and Axis Titles | allow you to change where and how each of the titles in your chart will appear. You can also use these menu items to remove a title from your chart |
| Legend | allows you to change the positioning and the display options for the chart legend. |
| Data Labels and Data Table | items to display the numeric values for the various elements of your chart and a trendline can be added to demonstrate statistical relationships in your data |
| Formatting | modify the colors and textual elements of your charts |
| Column Charts | used to compare different categories of items or the same category over time depict the types of items across the horizontal axis and the values for each category as a "column" rising into the vertical axis |
| When should you remove the legend? | When using a Column chart |
| Column charts are very similar to? | Bar charts, which is rotated 90 degrees to the right |
| Stacked column chart | breaks the column bars into segments that represent subcategories that are consistent across the columns |
| A stacked column chart is well suited to situations | when you need to compare categories (or the same category over time) and the categories can be decomposed into smaller common segments across the columns |
| Pie charts | used to compare the parts of category to the whole |
| Line chart | to compare the values of a particular category over time particularly helpful for uncovering trends in a dataset |
| Model | a physical representation of a larger or more complicated thing or idea |
| Spreadsheet model | generally used to represent the logic of a more complicated logical process |
| Every good spreadsheet model will have three common elements: | changeable inputs, outputs, and intermediate calculations |
| changeable inputs | the elements of the model that the user will change every time a new application is processed |
| Model outputs | represent the outcome of the process that is modeled in the spreadsheet |
| Intermediate Calculations | comprises the area of the worksheet where the logical steps required to complete the process are modeled |
| Monthly Income | The monthly income is based on the annual income inputted by the user. The monthly income is the annual income divided by 12. |
| Acceptable Income Servicing Debt | maximum dollar amount that the applicant can spend each month on debt to be approved for the loan. It is calculated by multiplying the monthly income by the acceptable percentage of monthly income allowed to go towards debt defined in the model inputs |
| Monthly Debts | This is included from the inputs to be used as part of the maximum payment calculation in the model |
| Max Payment | This is the total loan payment the applicant can make and still be approved for the loan. It is calculated as the acceptable income servicing debt minus the other monthly debts the applicant must pay |
| Monthly Payment | This is the monthly payment for the proposed loan. It is calculated using the PMT function that accounts for the loan amount, interest rate, and term of the loan as defined in the inputs section of the model |
| Scenario manager | useful for examining the impact of several inputs to a spreadsheet model changing at the same time allow you to create a number of different scenarios based on different states of the same set of inputs |
| Goal seek | allows the user to specify the desired value for an outcome cell and select an input cell that Excel should modify to achieve the desired outcome |
| Data Tables | we can specify a number of different possible states for one or two input variables and construct a table in our worksheet that calculates an outcome for our model based on each state of the input variables |
| Summary worksheet (of Scenario manager) | presents an overview of the current state of the spreadsheet model (Current Values) and each of the scenarios created in scenario manager for that model |
| PivotTables | are used to organize and summarize large amounts of data allow us to change how we summarize data to look at it in different ways—that’s where the name comes from We can look at the data one way and then easily “Pivot” or change how we examine the data |
| The four elements of a PivotTable are | Filters, Columns, Rows, and Values |
| Filter | is used to screen the data as it is placed in a PivotTable |
| Columns and Rows | are used to define how the data will be summarized and arranged on the PivotTable |
| Values | defines which data will be displayed on the PivotTable |
| When #### show up on excel what does that mean? | The cell is not big enough to hold numeric answer |
| Bar Chart | visually compare values across vertical or horizontal lines |
| Pie Chart | proportions of a whole |
| Line Chart | display trends over time |
| FV | Future value calculates the future value of an investment based on a constant interest rate |
| Ctrl A | grabs all the data |
| Ctrl . | takes you to the 4 corners of the spreadsheet |
| CSV | comma separated value |
| Conditional formatting | highlight, draw attention to makes it easy to highlight certain values or make particular cells easy to identify |
| v look up | short for 'vertical' lookup designed to work with data that is organized into columns. For a specified value, the function finds (or 'looks up') the value in one column of data, and returns the corresponding value from another column. |
| Syntax | arguments in excel formula; rules in a language |
| PMT | payment function |
| workbook | an excel file that stores all the information, previous calculations, and analyses you have completed |
| worksheet | contains the actual data and calculations organized into a collection of cells arranged in the form of a table |
| A1 | Both the column and row references are "relative" and will change when the reference is copied and pasted to other cells |
| $A1 | The column reference is "absolute" and will remain constant when copied and pasted to other cells. The row reference is "relative" and will change when copied and pasted to cells in other rows of the worksheet. |
| A$1 | The column reference is "relative" and will change when copied and pasted to cells in other columns in the worksheet. The row reference is "absolute" and will remain constant when copied and pasted to other cells. |
| $A$1 | Both the column and row references are "absolute" and will remain constant when the reference is copied and pasted to other cells. |
| COUNT(value1, [value2], …) | counts the number of cells in a range of cells that contain numbers |
| COUNTA(value1, [value2], …) | counts the number of cells in a range of cells that are not blank |
| AVERAGE(number1, [number2], …) | calculates the simple average of a set of numbers |
| MAX(number1, [number2], …) | returns the largest value in a set of numbers |
| MIN(number1, [number2], …) | returns the smallest value in a set of numbers |
| RATE (nper, pmt, pv, [fv], [type], [guess]) | calculates the interest rate earned for an investment given the number of payments made as part of the investment, the payment amount, and the current value of the investment. |
| EFFECT (nominal_rate, npery) | calculates the annual percentage rate for an interest rate given the number of times per year that interest is charged. |
| NPER (rate, pmt, pv, [fv], [type]) | calculates the number of payments that will be made to pay off a loan given the interest rate, payment amount, and original loan amount |
| PMT (rate, nper, pv, [fv], [type]) | calculates the payment amount for a loan given the interest rate, number of payments to be made to pay off the loan, and the original loan amount. |
| PV (rate, nper, pmt, [fv], [type]) | calculates the current value (accounting for compounding interest) of an investment given the interest rate, number of payments to be made, and the amount of the payment. |
| FV (rate, nper, pmt, [pv], [type]) | calculates the future value of an investment given the interest rate, number of payments to be made, and the amount of the payment. |
| Trace Precedents" and "Trace Dependents" | insert arrows into the spreadsheet review the syntax of more than one function or formula at the same time |
| Boolean logic is based on only two values | 0 and 1 which are represented by the values FALSE and TRUE |
| The operator "AND" | combines Boolean expressions to determine if ALL of the combined expressions are TRUE if any of the expressions combined by the "AND" operator is FALSE then the result of the combined "AND" expression will be FALSE. |
| The operator "OR" | combines Boolean expressions to determine if ANY of the combined expressions are TRUE "OR" will be FALSE only if all of the combined expressions are FALSE |
| The IF statement | =IF(logical_test,[value_if_true],[value_if_false]) |
| (logical_test) | comparing two values |
| (value_if_true) | is used to specify the result of the IF function if the Boolean expression result is TRUE |
| (value_if_false) | is used to specify the result of the IF function is the Boolean expression is FALSE. |
| The COUNTIF function | allows you to determine the number of cells within a range of cells that contain a specific value =COUNTIF(range,criteria) |
| The SUMIF function | is used to calculate the total for a set of values that match a specific criterion =SUMIF(range,criteria,[sum_range]) |
| The AVERAGEIF function | function is used to calculate the average for a set of values that match a specific criterion =AVERAGEIF(range,criteria,[average_range]) |
| VLOOKUP | when you need to find things in a table or a range by row. For example, look up a price of an automotive part by the part number, or find an employee name based on their employee ID. |
| VLOOKUP Syntax | =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) |
| HLOOKUP | assumes that the reference table has been rotated 90 degrees to the right. HLOOKUP will look in the first row of the reference table for a match to the "ookup_value then return the corresponding value from the row specified by the row_index_num argument |
Created by:
bella_grace11
Popular Business sets