Download Files: Start Excel File: http://ift.tt/2q2E39T
Download Files: Finished Excel File: http://ift.tt/2EnqTHt
Entire page with all Excel Files for All Videos: http://ift.tt/1zHY6GI about how to
In this video learn how to rearrange a product code using Excel Spreadsheet Functions and Power Query Functions. Also see that Flash Fill does not seem to work. Learn the Power Query equivalent for LEFT, RIGHT and MID Functions.
Topics:
Topics:
1. (00:11) Introduction
2. (01:36) See an example of Flash Fill and how it does not seem to work
3. (03:04) See Excel Spreadsheet Formula that uses the Functions MID, RIGHT and LEFT.
4. (06:07) See Power Query (M Code Functions) Custom Column Formula that uses the Functions Text.End, Text.Middle and Text.Start.
5. (10:33) Summary
Month: December 2017
Excel & Business Math 03: Excel’s Golden Rule: Formula Inputs, What If Analysis and Excel Models
Download Excel File: http://ift.tt/2lifvF5
Download pdf Notes: http://ift.tt/2loU9VI
Entire Class Web Site: http://ift.tt/2BjafuY
In this video learn how to apply Excel’s Golden Rule to build efficient Excel Formula Solutions & Excel Models.
Excel & Business Math Class (Busn 135) taught by Michael Girvin at Highline College / Mike Girvin at excelisfun Channel at YouTube Channel.
Topics in Video:
1. (00:01) Introduction
2. (00:22) Excel’s Golden Rule
3. (02:17) What If Analysis (First Discussion)
4. (02:49) What is an Excel Model
5. (03:13) Example #1: Build Student Extra Credit Score Table
6. (07:08) What If Analysis Example
7. (08:01) Example #2: Build Month Expense Multi-step Calculation Table
8. (17:29) Excel Model Example
9. (20:19) Example #3: Create a Payroll Table
10. (25:15) Why Excel’s Golden Rule is so Important
11. (29:25) Summary
Next Video: Excel & Business Math 04:
Excel & Business Math 02: Copying Formulas with Relative & Absolute Cell References
Excel & Business Math 02: Copying Formulas with Relative & Absolute Cell References
Excel & Business Math Class taught by Michael Girvin at Highline College.
Download Excel File: http://ift.tt/2BOo4xC
Download pdf Notes: http://ift.tt/2CffafT
Entire Class Web Site: http://ift.tt/2BjafuY
Topics in Video:
In this video learn about how to use Relative and Absolute Cell References in formulas that you want to copy. See four examples that teach you the difference between Relative and Absolute Cell References.
Next Video: Excel & Business Math 03
Excel Magic Trick 1473: Sequential Number Incrementor for Just The Middle Number: AA-0009-17
Download Files: http://ift.tt/2BAGcz3
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Learn about how to create a formula to create a product code where the middle part of the code must increment as it is copied down a column. See the Functions ROWS and TEXT. ROWS is good for incrementing numbers inside formulas – it is better than the ROW function because it allows structural changed to the spreadsheet whereas ROW Function does not. Also see the TEXT Function to format numbers with Custom Number Formatting and convert them to text.
Excel Magic Trick 1474: Excel Twinkling Christmas Tree With Star & Formulas as Presnets!
Download Files: http://ift.tt/2iF8Tie
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Learn about how to create a Twinkling Christmas Tree with a Star on Top in Excel using the RAND and CHAR functions, the Conditional Formatting Scales and Conditional Formatting Icons.
Happy Holidays from excelifun!
Excel Magic Trick 1472: Count Between (or Above and Below) Upper & Lower Limits (7 Examples)
Download Files: http://ift.tt/2BAGcz3
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Learn about how to
Topics:
1. (00:11) Introduction
2. (01:58) Ex 1: Between Upper & Lower Limits: =COUNTIFS Function
3. (04:45) Ex 2: Between Upper & Lower Limits: =DCOUNT Function
4. (07:00) Ex 3: Between Upper & Lower Limits: =COUNTIFS so we can copy down a column
5. (09:44) Ex 4: Above & Below an Upper and Lower Limit: Two COUNTIFS
6. (13:33) Ex 5: Above & Below an Upper and Lower Limit: DCOUNT Function
7. (14:22) Ex 6: Above & Below an Upper and Lower Limit: SUMPRODUCT & COUNTIFS in an array formula
8. (19:50) Ex 7: Above & Below an Upper and Lower Limit: Two COUNTIFS so we can copy down the column
9. (21:09) Summary
COUNTIFS Count Between Upper and Lower Limits
COUNTIFS Count Above and Below Upper and Lower Limits
Counting With Criteria
Counting With Conditions
Excel & Business Math 01: Introduction to Excel. Why We Use Excel for Math. First Formula.
Excel & Business Math Class taught by Michael Girvin at Highline College / Mike Girvin at excelisfun Channel at YouTube Channel.
Download Excel File: http://ift.tt/2Bja2Ic
Download pdf Notes: http://ift.tt/2z0ULFQ
Entire Class Web Site: http://ift.tt/2BjafuY
Topics in Video:
1. (00:01) Introduction
2. (00:34) Class Web Site, Class Files & USB Drive to store files from class
3. (01:56) Download Excel & pdf Files. Look at different Browsers: Chrome, Internet Explorer, Mozilla
4. (03:05) Create Folder & Save Files using Save As & Windows Explorer
5. (05:49) Why we use Excel for Business Math
6. (09:46) Conventions for Class
7. (13:18) What does Excel do?
8. (14:35) Excel Workbook Layout
9. (17:38) Move Between Sheets
10. (19:00) Zoom in and out = Ctrl + Roll Wheel on Mouse
11. (20:00) Ribbon Tabs
12. (20:38) QAT – Quick Access Toolbar
13. (21:20) First Excel Formula: Total Sales
14. (31:33) Selecting a Range with the Selection Cursor
15. (31:40) Number Formatting as a Façade to display numbers in a different way that the Actual Number in the cell
16. (34:06) Second Excel Formula: Net Income. Also see how to copy a formula to the side (not down a column)
17. (35:32) Verify that Number Formatting is NOT misleading
18. (36:52) Review PDF Notes ()
19. (37:52) Summary
Next Video: Excel & Business Math 02
Excel Magic Trick 1471: Array Formula: Add Daily Rainfall 5 AM to 5 AM Next Day: (Most Automatic)
Download Files: Start Excel File: http://ift.tt/2ACcTLV
Download Files: Finished Excel File: http://ift.tt/2yoi8ZB
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Learn about how to
In this video learn how to use Array Formulas to automate the reporting process for Rain Totals where the staring time for a day period changes.
Topics:
1. (00:01) Introduction and description of reporting requirements
2. (02:08) Date and Time Number Formatting and Time Formula for Time Equivalent for Date Start Time.
3. (03:32) Count Unique Date Array Formula. See the functions FREQUENCY, SUM and INT. See two versions of this formula. One version converts positive numbers with the Logical Test Greater than Zero and a Double Negative. The other uses the SIGN Function.
4. (13:34) Array Formula to Extract a List of Unique Dates that assumes that we may not have a complete list of sequential dates, and there is a variable Start Time. See the functions AGGREGATE, ROW, ROWS, IF, SIGN, FREQUENCY, SUM and INT.
5. (26:02) Formula to add rain totals using SUMIFS & IF Functions and criteria based on the start date-time.
6. (28:39) Array Formula to Extract a List of Unique Dates that assumes we HAVE a complete list of sequential dates. See the functions, AGGREGATE, IF, ROWS and INT.
7. (34:09) Re-organize Order of Logical Tests in IF Function to create a more efficient formula.
8. (36:03) Add new data and test formulas and reporting system.
9. (36:50) Summary
Related Videos:
Excel Magic Trick 1469: Add Daily Rainfall 5 AM to 5 AM Next Day: Helper Column & PivotTable
Excel Magic Trick 1470: Power Query (Get & Transform) Add Daily Rainfall 5 AM to 5 AM Next Day
Excel Magic Trick 1470: Power Query (Get & Transform) Add Daily Rainfall 5 AM to 5 AM Next Day
Download Files: Start Excel File: http://ift.tt/2BT9DI6
Download Files: Text Files for Import: http://ift.tt/2ALvzpk
Download Files: Finished Excel File: http://ift.tt/2BUj1eu
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Learn about how to take a rainfall data set with Time-Date Stamps and add rain totals for each day and have the ability to change the definition of the 24 hour period by subtracting an Hour Increment. See how to use Power Query (Get & Transform) to import Multiple Text Files and create a Custom Column and use the Group By feature to create the final report. Then see that the report will update when we get new Text Files.
Topics:
1. (00:11) Introduction and description of process of Importing Text Files, Creating an Excel Parameter, Create Custom Column, Group By to create Report and Update after new Text Files arrive.
2. (02:08) Look at Text Files
3. (02:46) Create a Parameter in Excel and Import it into Power Query
4. (06:03) Importing Text Files using From Folder feature
5. (09:39) Look at Custom Function that Power Query automatically creates
6. (11:35) Convert Dates to Decimal Numbers so we can use Column in a Formula
7. (12:42) Create Custom Column to subtract Start Time
8. (13:44) Remove Other Columns and Rearrange Columns
9. (14:15) Use Group By feature in Power Query and compare and contrast PivotTable in Excel to Group By in Power Query
10. (15:55) Load Report to Excel Sheet
11. (16:35) Change Parameter in Excel and refresh Power Query Report
12. (17:06) Load new Files to Folder and Refresh Power Query Report
13. (18:43) Verify that Excel Files NOT imported
14. (19:13) If Folder Path Chages, Change it in Source Step
15. (19:54) Summary
Related Videos:
Excel Magic Trick 1469: Add Daily Rainfall 5 AM to 5 AM Next Day: Helper Column & PivotTable
Excel Magic Trick 1471: Add Daily Rainfall 5 AM to 5 AM Next Day: Array Formula (Most Automatic)
Excel Magic Trick 1469: Add Daily Rainfall 5 AM to 5 AM Next Day: Helper Column & PivotTable
Download Files: http://ift.tt/2iF8Tie
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Learn about how to take a rainfall data set with Time-Date Stamps and add rain totals for each day and have the ability to change the definition of the 24 hour period.
Topics:
1. (00:11) Introduction to data set and the task at hand
2. (01:30) Look at Time Values and determine what Time Value should be subtracted from each Date-Time to get correct category.
3. (03:32) Add Data Validation List to Time Cell so we only subtract the correct whole hour.
4. (04:55) Helper Column Formula to subtract hour amount. See Relative Cell References and Absolute Cell Reference. See the INT Function.
5. (06:47) Create PivotTable
6. (08:05) What happens if we change Number of Hours to Subtract? PivotTable MUST be refreshed
7. (08:35) Summary
Related Videos:
Excel Magic Trick 1470: Add Daily Rainfall 5 AM to 5 AM Next Day: Power Query
Excel Magic Trick 1471: Add Daily Rainfall 5 AM to 5 AM Next Day: Array Formula (Most Automatic)
Office 2016 Video #42: Access 2016: Export Tables, Queries & Reports to Excel, PDF or Text File
Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download files: http://ift.tt/2jxY9m1
Learn how to export Tables, Queries and Reports From Access 2016 to Excel, Text File or PDF File.
Office 2016 Video #40: Access 2016: Building Queries in Access (15 Examples)
Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download files: http://ift.tt/2jxY9m1
Topics in this Video:
1. (00:01) Introduction
2. (01:20) What is a Query?
3. (02:10) Query 01 Description and Sell Price
4. (06:50) Query 02 Product Records From SC
5. (08:40) Query 03 On Hand = 10
6. (09:57) Query 04 Sell Price $10 AND On Hand 8 (AND Logical Test)
7. (12:40) Query 05 Price Between $5 And $10 (AND Logical Test)
8. (16:05) Query 06 Sell Price 5 AND 10 (AND Logical Test)
9. (18:03) Rename Queries
10. (19:15) Query 07 Supplier Code Toy Records SC Or AP (OR Logical Test)
11. (20:43) Query 08 Ave Sell Price (Aggregate Calculation)
12. (22:28) Query 09 Ave Sell Price Group By Supplier
13. (23:17) What is Group By?
14. (24:23) Query 10 Min & Max Sell Price Group By Supplier
15. (25:18) Query 11 Inventory Value (Formula or Expression)
16. (28:50) Query 12 Gross Profit for each Product (Formula or Expression)
17. (30:40) Query 13 Products that Contain *rail* (Contains Query)
18. (32:20) Query 14Product Re-Order Phone List (Query based on Two Tables)
19. (35:04) Query 15 Records by Supplier (Parameter Query)
20. (36:45) Summary