Tag: How to

Excel & Business Math 32: Rounding Time Calculations to Nearest 5 or 15 minutes using MROUND

Posted on by Annie D

Download Start Excel File: http://ift.tt/2BIoisJ
Download pdf Notes: http://ift.tt/2Cc8QGG
Entire Class Web Site: http://ift.tt/2BjafuY

In this video learn how to use the MROUND Function to round a time calculation to the nearest 5 minutes or nearest 15 minutes.
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:14) How To use MROUND for Payroll or Product Pricing
3. (06:13) MROUND on a Payroll Time Sheet
4. Day Shift or Night Shift Payroll calculations. See MROUND, MOD and ROUND Functions to make time calculations rounded to the neatest 5 minutes and then calculate Gross Pay for each employee
5. () Summary

Next Video: 33 Excel & Business Math

View on YouTube

Excel & Business Math 31: Night Shift Hours Worked Formula for Payroll Time Sheet (3 Examples)

Posted on by Annie D

Download Start Excel File: http://ift.tt/2EHj4jT
Download pdf Notes: http://ift.tt/2CCS6DT
Entire Class Web Site: http://ift.tt/2BjafuY

In this video learn about three different formulas for n Excel Payroll Time Sheet with the Night Shift Employees work past midnight and have a Time Out that is smaller than the Time In.
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:13) What is Night Shift?
3. (01:12) Look at Time Values for Night Shift and Negative Times in Excel
4. (05:18) Formula # 1 using IF Function
5. (09:41) Formulas #2 using Logical Formula or Boolean Formula
6. (13:29) Formula #3 using MOD Function
7. (14:57) Complete Payroll Time Sheet for Day or Night Shift using all three formulas and the ROUND Function.
8. (19:43) Summary

Next Video: 32 Excel & Business Math

View on YouTube

Excel & Business Math 30: Payroll Time Sheets, IF Function, Sheet Reference for Overtime & Gross Pay

Posted on by Annie D

Download Start Excel File: http://ift.tt/2CafxIX
Download pdf Notes: http://ift.tt/2BGZgdF
Entire Class Web Site: http://ift.tt/2BjafuY

In this video learn how to create Daily Time Sheets for the whole week, then use Sheet References, the IF Function and the ROUND Function to calculate the correct Gross Pay, including Overtime, for each Employee.
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. (01:04) Formula For Daily Employee Total Hours Worked
3. (04:57) Summary Table of Employee Hours for the Week
4. (07:47) Sheet Reference to pull Employee Weekly Hour Total to Gross Pay Sheet
5. (11:38) IF Function to calculate the correct Regular Hours for each Employee
6. (15:52) Overtime Hours Worked Formulas
7. (16:46) Regular Hours Gross Pay with ROUND Function
8. (18:26) Overtime Gross Pay with ROUND Function
9. (19:56) Total Gross Pay for each Employee for the week
10. (20:14) Summary

Next Video: 31 Excel & Business Math

View on YouTube

Excel & Business Math 29: Time Number Formatting & Time Calculations in Excel for Payroll Table

Posted on by Annie D

Download Start Excel File: http://ift.tt/2ol0qmU
Download pdf Notes: http://ift.tt/2C9onHg
Entire Class Web Site: http://ift.tt/2BjafuY
In this video learn about Time Number Formatting and learn about Time Calculations in Excel to calculate Total Number Of Hours between two time values. Also see a Payroll table that allows us to calculate Hours Worked, Gross Pay, Deductions and Net Pay.
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:51) What is Excel Time Number Format?
3. (07:40) Excel Formula for Total Hours between two-time values
4. (11:27) Excel Formulas for Total Hours between four-time values, Time In and Time Out including Lunch break
5. (15:20) Payroll table with formulas for: Hours Worked, Gross Pay, Deductions, Net Pay, including the correct use of the Round Function
6. (18:51) Summary

Next Video: 30 Excel & Business Math

View on YouTube

Excel Magic Trick 1478: Conditional Format Row When Event Code is a Duplicate AND Task = “Special”

Posted on by Annie D

Download Files: Start Excel File: http://ift.tt/2FaY1mx
Entire page with all Excel Files for All Videos: http://ift.tt/1zHY6GI about how to
In this video learn how to use Conditional Formatting to format the row in a data set and how to use the COUNTIFS and AND Functions to create a Logical Formula that will deliver a TRUE when the Event Key is a Duplicate AND the Task Column equals “Special”.

View on YouTube

Excel Magic Trick 1477: COUNTIFS & AND Functions for Duplicate Event Codes AND Task = “Special”

Posted on by Annie D

Download Files: Start Excel File: http://ift.tt/2FaY1mx
Entire page with all Excel Files for All Videos: http://ift.tt/1zHY6GI about how to
In this video learn how to use the COUNTIFS and AND Functions to create a Logical Formula that will deliver a TRUE when the Event Key is a Duplicate AND the Task Column equals “Special”.

View on YouTube

Excel & Business Math 28: IF Function, IS Functions & Building a Check Register Other IF Tricks too

Posted on by Annie D

Download Start Excel File: http://ift.tt/2C1u5ea
Download pdf Notes: http://ift.tt/2ByEwVd
Entire Class Web Site: http://ift.tt/2BjafuY
In this video learn how to build a check Register using the IF Function and the ISBLANK Function
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. (01:08) Double Vertical Borders for Checkbook Register
3. (03:16) Data Validation to ONLY allow an X in the Reconciliation Column
4. (04:38) Conditional Formatting with Logical Formula and Mixed Cell Refence to format the row yellow when the item has been reconciled
5. (08:20) Checkbook Balance Formula
6. (11:07) First look at the IS Function: ISNUMBER
7. (14:07) First look at the IF Function
8. (15:20) Checkbook Balance Formula #1 using IF Function, ISNUMBER Function and a Zero Length Text String.
9. (16:37) First look at “Show Nothing”, Double Quote, Double Quote, Zero Length Text String.
10. (18:06) Test IF Function. Use Formula Evaluator to see how the IF Function puts One of Two Things in the cell.
11. (21:00) Checkbook Balance Formula #2 using IF Function, ISBLANK Function and a Zero Length Text String
12. (24:48) Difference between using ISNUMBER or ISBLANK for the Logical Test.
13. (25:38) ISFORMULA & FORMULATEXT to show formula when formula is entered into a cell.
14. (28:35) Test to see if Zero Length Text String is in cell using the ISBLANK, ISTEXT and LEN Functions
15. (30:44) Summary

Next Video: 29 Excel & Business Math

View on YouTube

Excel & Business Math 27: Bank Reconciliation Made Easy

Posted on by Annie D

Excel & Business Math 27: Bank Reconciliation Made Easy
Download Start Excel File: http://ift.tt/2Bxb4zg
Download pdf Notes: http://ift.tt/2o0wqgO
Entire Class Web Site: http://ift.tt/2BjafuY

In this video learn about checks and Bank Reconciliation. Learn about the concepts of why and how we perform bank reconciliation. Also learn about how to show two worksheets from the same workbook side-by-side. Also learn hoe to create Conditional Formatting and data Validation to format or highlight a row in a checkbook register.
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. Parts of a Checks
3. Types of Endorsements
4. Bank Services & Fees
5. Bank Reconciliation Example 1: Make sure both sides are actually Equal.
6. Bank Reconciliation Example 2: Make sure both sides are actually Equal and enter the bank statement entries that are not in our records, into our records
7. Bank Reconciliation Example 3: Bank Reconciliation with a Bank Reconciliation Form
8. How to show two worksheets from the same workbook side-by-side: View, New Window, Arrange All
9. How to use Conditional Formatting and Data Validation to format or highlight row that has an “X” for “item has been reconciled”.
10. () Summary

Next Video: 28 Excel & Business Math

View on YouTube

Excel & Business Math 26: Is Rate of Change Same for Increase by $10 and Decrease by $10?

Posted on by Annie D

Download Start Excel File: http://ift.tt/2EChEpR
Download pdf Notes: http://ift.tt/2swOsfi
Entire Class Web Site: http://ift.tt/2BjafuY

In this video learn about how the Rate of Change for a $10 increase and $10 decrease are different.
Excel & Business Math Class (Busn 135) taught by Michael Girvin at Highline College / Mike Girvin at excelisfun Channel at YouTube Channel.

Next Video: 27 Excel & Business Math

View on YouTube

Excel & Business Math 25: Percentage Formulas to Help Calculate a Budgeted Forecast for Next Year

Posted on by Annie D

Download Start Excel File: http://ift.tt/2EmtGR5
Download pdf Notes: http://ift.tt/2EXNyLl
Entire Class Web Site: http://ift.tt/2BjafuY

In this video learn how to use Excel Percentage Formulas to estimate next year Net Income Statement, create a Budgeted Forecast for Next Year.
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:48) Formula 1: Rate = Part /Base: Expenses As a % of Revenue
3. (04:26) Formula 2: End = Begin*(1+ROC): This Year Revenue
4. (05:15) Formual3: Part = Rate * Base: Expenses As a % of This Year Rev
5. (06:47) Amazon Example all three formulas
6. (08:16) Summary

Next Video: 26 Excel & Business Math

View on YouTube

Excel & Business Math 24: COUNTIFS Function & Part/Base for Probabilities & Frequency Distributions

Posted on by Annie D

Download Start Excel File: http://ift.tt/2G3RUQv
Download pdf Notes: http://ift.tt/2H2WBuV
Entire Class Web Site: http://ift.tt/2BjafuY

In this video learn how to use the COUNTIFS Function and the Part / Base Formula (Rate Formula) to create a Frequency Distribution with Probabilities (Relative Frequencies) that we3 can use to estimate the likelihood of an event in the unknown future.
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. (01:35) What is Probability?
3. (05:51) What is a Frequency Distribution?
4. (07:07) How to use COUNTIFS Function (to Count with a Specific Condition)
5. (12:00) Example 1: Create a Frequency Distribution to Calculate Probabilities of Getting a Certain Grade in Math Class
6. (16:11) Example 2: Create a Frequency Distribution to Calculate Probabilities of Getting a Certain Grade in English
7. (18:04) Example 3: Create a Frequency Distribution to Calculate Probabilities for # of Banquet Room Use
8. (20:42) Example 4: Create a Frequency Distribution to Calculate Probabilities for What Type of Item Will Be Purchased
9. (23:13) ) Summary

Next Video: 25 Excel & Business Math

View on YouTube

Excel & Business Math 23: Important Formulas for Increase Decrease Problems (8 Awesome Examples)

Posted on by Annie D

Download Start Excel File: http://ift.tt/2Bgnqvx
Download pdf Notes: http://ift.tt/2BL2myc
Entire Class Web Site: http://ift.tt/2BjafuY

In this video learn about all the formulas for Increase and Decrease, or Percentage Increase Problems.
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. (01:27) What is an Increase Decrease Problem
3. (03:33) How to solve an Increase Problem with the Table Method, Diagram Method and an example of every possible formula for an Increase Problem.
4. (23:33) How to solve a Decrease Problem with the Table Method, Diagram Method and an example of every possible formula for an Increase Problem.
5. (34:19) Example 1: Stock Market Drop on Feb 5, 2018 and make formulas
6. (42:21) Example 2: Using Yahoo Finance to get Stock Data and make formulas
7. (45:34) Example 3: Farmland Value Example
8. (49:52) Example 4: Markdown and Markup Examples
9. (52:30) Example 5: GE Company Sales Example
10. (55:07) Stock Example with a Portfolio of Different Stocks
11. (58:21) Summary

Next Video: 24 Excel & Business Math

View on YouTube