On Demand Webinar
Webinar Details $219
- Webinar Length: 100 Minutes
- Guest Speaker: Mike Thomas
- Topic: Business Skills, Software, Finance, Human Resources, Taxation and Accounting
- Credit: CPE 2.0, ATATX 1.5, ATAOP 1.5
-
- Creating basic formulas: addition, subtraction, division, multiplication
- Using parentheses in formulas - the what and why
- Copying a formula - the gotchas you need to know about
- Make formulas logical and understandable by assigning names to your important cells
- An introduction to functions: SUM, AVERAGE, COUNT and SUBTOTAL
- The SUMIF and COUNTIF function: Add up and count based on criteria
- Use TODAY, DATEDIF and NETWORKDAYS to calculate and manipulate dates
- Use CONCATENATE and TEXTJOIN to combine text from multiple cells
- Introduction
- How To Calculate Sales & Income 00:03:52
- How To Calculate Total Profit 00:09:40
- Working with Brackets/Parenthesis 00:13:23
- Copying Formulas 00:20:31
- How To Use Dollar Signs In A Formula 00:26:59
- Absolute 00:27:28
- Copying Formulas Down A Column 00:29:48
- Keeping The Cell Reference Fixed - Dates 00:32:2
- Getting a Hash Value Error 00:38:44
- Using One Vs. Two Dollar Signs 00:40:45
- Fixed Cell/Column Example 00:42:22
- Multiple Currencies 00:48:08
- Function Key For Dollar Sign 00:51:19
- Seeing Formulas In Cells 00:53:43
- Formula Vs. Function 00:54:37
- The SUM Function 00:56:31
- The AVERAGE Function 00:58:52
- Function Recap - Another Way To Use Functions 01:00:53
- The SUBTOTAL Function 01:04:43
- Calculating The Average Value Of A Sale 01:12:19
- The COUNTIFS Function 01:17:51
- The SUMIFS Function 01:
- The COUNTA Function 01:30:56
- The CONCAT Function 01:32:22
- Table Design 01:36:57
- The NETWORKDAYS Function 01:37:43
- Presenter Closing 00:40:21
- Presentation Closing 01:40:52
- Absolute Reference 00:27:28, 00:30:24, 00:53:26
- AVERAGE 00:58:52
- Cell 00:09:17, 00:22:15, 00:24:18, 00:26:52, 00:28:40, 00:34:34, 00:45:27, 00:56:56, 01:19:01, 01:32:15, 01:39:59
- Cell Reference 00:24:19, 00:32:22
- Column 00:29:48, 00:39:36, 00:42:05, 00:56:53, 01:39:59
- CONCAT Function 01:32:22
- COUNTA Function 01:30:56
- COUNTIFS 01:17:43, 01:29:57
- Filter 01:06:58, 01:08:19, 01:11:22, 01:19:46
- Format 00:59:14, 00:58:48
- Formula 00:00:09, 00:03:02, 00:13:11, 00:19:15, 00:25:25, 00:28:45, 00:33:14, 00:42:21, 00:48:10, 00:57:03
- Function 00:03:04, 00:19:16, 00:40:00
- NETWORKDAYS 01:39:03
- PEMDAS 00:17:11
- Row 00:41:59, 01:08:06, 01:12:07, 01:34:22
- Spreadsheet 00:03:55, 00:42:31
- SUBTOTAL 01:04:44, 01:08:37
- SUM 00:56:32, 00:59:47, 01:08:11
- SUMIFS 01:17:40
- Table 01:36:57
AVERAGE : Returns the average (arithmetic mean) of the arguments.
Absolute Reference : Absolute references in Excel are a direct link to a specific cell or range of cells that remain fixed if you copy or drag the formula. Absolute references are represented by $ symbols. A $ before a column letter freezes the column, while a $ before the row number freezes the row number. You can freeze the column letter and/or row number when needed.
CONCAT Function: The CONCAT function was introduced in the Office 365 version of Excel 2016. It's not available to users of perpetual licensed versions of Excel 2016 or earlier versions of Excel. This function supersedes the CONCATENATION function and is used to combine multiple pieces of text into one. An alternative to CONCAT and CONCATENATE is using ampersands to join pieces of text together into one.
COUNTA Function: The COUNTA function returns the number of blank cells within a given range of cells.
COUNTIFS: The COUNTIFS function is a built-in function in Excel that is categorized as a Statistical Function. It can be used as a worksheet function (WS) in Excel. The COUNTIFS function allows you to stipulate multiple criteria, hence the plural.
Cell: In spreadsheet applications, a cell is a box in which you can enter a single piece of data. The data is usually text, a numeric value, or a formula. The entire spreadsheet is composed of rows and columns of cells.
Cell Reference: A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate. There are three types: Relative, Absolute, and Mixed
Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.
Filter: The Filter feature in Excel allows you to show or hide rows within a list of data by making selections from drop-down lists. The Filter feature is available on the Data tab of all versions of Excel as well under the Sort & Filter command on the Home menu.
Format: When we format cells in Excel, we change the appearance of a number without changing the number itself. We can apply a number format (0.8, $0.80, 80%, etc) or other formatting (alignment, font, border, etc). By default, Excel uses the General format (no specific number format) for numbers.
Formula: A formula is an expression which calculates the value of a cell.
Function: Functions are predefined formulas and are already available in Excel.
NETWORKDAYS: Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.
PEMDAS: PEMDAS is an acronym for the words parenthesis, exponents, multiplication, division, addition, and subtraction. Given two or more operations in a single expression, the order of the letters in PEMDAS tells you what to calculate first, second, third, and so on, until the calculation is complete.
Row: A row is the range of cells that go across (horizontal) the spreadsheet/worksheet. Rows are identified by numbers e.g. row 1, row 5. Examples of use. A row might contain the headings of a table e.g. product ID, product name, price, number sold.
SUBTOTAL: A worksheet function that allows you to sum, average, count, and other otherwise analyze data on just the visible cells within a given range.
SUM: Microsoft Excel defines SUM as a formula that “Adds all the numbers in a range of cells”. This definition clearly points that Sum function has a job to add numbers and the arguments can be supplied using combinations of both numbers and range of cells. =SUM The SUM function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUM function can be entered as part of a formula in a cell of a worksheet.
SUMIFS: A look-up function in Excel that allows you to add up numbers based upon up to 127 criteria that you specify. Unlike VLOOKUP, the SUMIFS function can add up two or more values and returns zero (instead of #N/A) if no match is found.
Spreadsheet: Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation or computation capabilities, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. Excel forms part of the Microsoft Office suite of software.
Table: A table is an arrangement of data in rows and columns, or possibly in a more complex structure. Tables are widely used in communication, research, and data analysis. Tables appear in print media, handwritten notes, computer software, architectural ornamentation, traffic signs, and many other places.
