MrExcel Excel Videos

How to calculate pay by month where the contractor worked part of the first and last months. Mike and Bill offer competing formulas but hope you can come up with something better.

How can you calculate work days between two dates when your business is closed on Sunday and Wednesday? WORKDAYS.INTL does not offer a Sunday and Wednesday option. Today, @ExcelWriter from Twitter checks in with a great solution to this question.

Matt asks about making VBA code created with the macro recorder more reliable. In today's long episode, a discussion about adding variables to your recorded code and then using those variables to replace the hard-coded bits in the recorded code.

A question from YouTube on how to write a macro to mark duplicates in each row where duplicates over the value of 5 are marked in red and under 5 are marked in blue. This can be done without a macro, using two formula-based conditional formatting rules. However - the formula will change depending on if you want to mark both halves of the duplicate or just the 2nd occurrence of the duplicate.

Four co-workers play 8-ball every day at lunch. They are currently calculating win rate on a white board. Can Excel be used to track the win rate of each player? Although the person is new to Excel, this video goes through a few somewhat advanced topics - Freeze Panes, Ctrl+T tables, Fill Handle, and a pair of COUNTIF functions.

The rent for a storage container starts out at $5 a day for the first 7 days, then $10 a day for the next two weeks, then $20 a day after that. How do you calculate the total rent? In this episode, Bill shows a pair of VBA User Defined Functions and Mike shows a pair of formulas.

Say that a co-worker writes a VLOOKUP to a closed workbook and refers to $A$1:$S$99,19 to grab a value from column S. The co-worker sends you the linking workbook but not the linked workbook. Perhaps there is some confidential information in columns B through R of that workbook. Everyone involved might be initially surprised that you can take the linking workbook to a new computer, without access to the closed workbook on the original computer, and drag out the entire table stored in A1:S99 of the closed workbook, including the confidential bits. I will admit I was initially surprised, but I guess I shouldn't be surprised. Everyone should be aware of this, and think before you set up wide VLOOKUPs to closed workbooks.

You have a 5x5 Risk and Severity Matrix. Separately, there is a database of events. Each is assigned a risk and severity. Place the record number from each event in the proper box. If more than one record falls in a cell, join them with commas in between. After consulting with ExcelisFun, I went with a custom function in VBA. Can you think of a formula that wouldn't be repetitively silly?

A question from Twitter asked how to have long text on an invoice extend down many rows. In today's podcast, Bill looks at three different ways.