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
ACT 4310
| Term | Definition |
|---|---|
| Operator Precedence | Within parentheses first: left to right with * and / before + and - |
| Strategy for developing a long formula | Create a piece at a time, verifying results of each piece before proceeding |
| Function SMALL (range, k) Function LARGE (range, k) | Smallest value in a range: SMALL (range, 1) |
| Function COUNT (range) | Count nonblank entries in a range |
| Replace blank cells with a value and color fill | [Find&Select] -> [GoToSpecial] -> [Blanks] -> select color -> enter value -> [Ctrl][Enter] |
| Insert column to left | Highlight column, right-click -> [Insert] |
| Rearrange columns | Click column header of column to move. In Home tab, [Clipboard], right-click column header of column to move to, right-click the column header of column to move -> Cut. Click column header to right of where column is to go. Right-click and select [Insert |
| Rescale values, e.g., from 5 to 10 or from 15 to 10 | Multiply original by the multiplier (whole number or fraction), e.g., for 5 to 10, multiply by 2, for 15 to 10, multiply by 2/3 |
| Average a range omitting smallest value | =(SUM(range)-SMALL(range,1))/ (COUNT(range)-1) |
| Absolute and relative references | Relative reference: A8 Absolute column only: $A8 Absolute row only: A$ Absolute column & row: $A$8 Press [F4] to cycle choices |
| Copy/propagate formula to bottom of range | Highlight formula cell(s) and move cursor to lower right corner. When cursor changes to a bold +, double-left click. |
| Shortcut: Display format Cells | [Ctrl]1 |
| Hardcoding (sure way to make errors!) | Typing values rather than using references to cells containing parameters |
| Parameters | Values that can vary over time, i.e., interest rates, weights. Using formula references to these values is parameterization. |
| Built-in functions | Examples: average, minimum, maximum, count, small, IF |
| Excel: IF function Access: IIF function | IF(logicalTest, [valueIfTrue], [valueIfFalse]) When logicalTest returns a value of TRUE, [valueIfTrue] goes in the cell. When logicalTest returns a value of FALSE, [valueIfFalse] goes in the cell. |
| Nested IFs and IIFs | In an IF/IIF function, [valueIfTrue] and [valueIfFalse] arguments can be IF/IIF functions (64 nested levels for Excel; unlimited for Access) |
| VLOOKUP Function | VLOOKUP(lookupValue, tableArray, ColIndexNum, rangeLookup) rangeLookup = FALSE for exact match rangeLookup = TRUE or omitted for approximate match |
| VLOOKUP table format for [tableArray] argument | First column: Values sorted ascending for looking up Columns after first: Values to extract |
| Change column width | Place and hold cursor on right border of column header and drag border to desired column width. |
| Blanks ignored in calculations | Excel ignores blank cells, in calculations and in formulas unless a function specifically treats them otherwise. Variants of the COUNT* function will identify blank cells. |
| Macro | Saved sequence of keystrokes for later execution to enable users to reduce time to perform repetitive tasks. |
| Create macro | 1. Practice keystrokes 2. Enable Developer tab: FILE tab -> Customize Ribbon, check DEVELOPER 3. In DEVELOPER tab -> Record Macro 4. Name macro (no spaces 5. Enter shortcut key -> OK 6. Enter keystrokes for macro 7. In DEVELOPER tab -> Stop Recordin |
| Execute macro | [CTRL] [SHIFT] letter |
| Sort data | 1. Highlight data range with headers 2. DATA tab -> Sort -> check “My data has headers” 3. Set Column pulldown menu to first sort field 4. For within level sorting, -> Add Level and select field 5. Reset sort order if needed |
| VLOOKUP Function | VLOOKUP(lookupValue, tableArray, ColIndexNum, rangeLookup) rangeLookup = FALSE for exact match rangeLookup = TRUE or omitted for approximate match |
| Create pivot table | 1. Highlight data range including headings 2. INSERT tab -> PivotTable -> OK 3. In PivotTable Fields, select field Row Labels 4. Drag fields to ∑ Values 5. For each ∑ Values field, pull down menu; select Value Field Setting 6. Format colum |
| Function CONCATENATE | Put cell contents together: CONCATENATE (comma-separated arguments) |
| Circular reference | A cell referencing itself directly or indirectly through a sequence of formulas, creating a logic error |
Created by:
Tina 100
Popular Accounting sets