Excel - Dueling Excel - Working on the Railroad - Duel 158 - Episode 1883
2,975 views · Published 2 May 2014 · 8:27 · Indexed 28 October 2026
Channel: MrExcel.com · 2014 · Education
Microsoft Excel Tutorial - Dueling Excel - Working on the Railroad - Duel 158. Welcome to another exciting episode of Dueling Excel, where Mike Girvin from Excel Is Fun and I, Bill Jelen from MrExcel, go head to head to solve your Excel problems. In this episode, we tackle a question about working with changing rates on a railroad. Mike and I both have different approaches to solving this problem, so let's see who comes out on top. But before we dive into the problem, I want to give a shout out to Mike for his recent appearance on Excel TV. Those guys are a lot of fun and it was great to see Mike on the show. And speaking of appearances, I'll be hosting an Excel live event in Tampa, Florida on May 9th. So if you're in the area, come join us for some Excel fun. Now, let's get to the problem at hand. Our viewer is renting a rail car from April 18th to August 13th, and the rates for the car change during that time period. From April 15th to May 10th, the rate is $35, but on May 11th it changes to $40, and on July 11th it changes to $50. Our viewer wants to know how to calculate the total cost for the rental period. And while you may think this requires some complex VBA coding, I have the perfect array formula that will solve this problem. First, I use the INDIRECT function to create an array of all the dates in the rental period. Then, I pass this array to the LOOKUP function, which allows us to work with arrays of values. I also use the table names trick, where I select the range and type in a name in the name box, to make the formula more dynamic. And finally, I use the SUM function to calculate the total cost. And the best part? This formula works for any rail car and any set of dates. But Mike has a different approach to solving this problem. He uses the ROW and INDIRECT functions to create an array of serial numbers, which he then passes to the LOOKUP function. He also uses the OFFSET function to create a dynamic range, in case the number of rail cars changes. And instead of calculating the total cost, Mike calculates the average cost using the AVERAGE function. And just like my formula, this one is also dynamic and works for any rail car and any set of dates. In the end, both Mike and I come up with different solutions to the same problem. And while I may have gotten the formula right, Mike's approach was more in line with what our viewer was looking for. So, I have to give him the point for this one. But that's the beauty of Dueling Excel, we both learn from each other and come up with creative solutions to Excel problems. So, make sure to tune in next week for another exciting episode. And don't forget to check out Mike's book, Control Shift Enter, where he shares even more amazing Excel tips and tricks. Thanks for watching and we'll see you next time! Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ Table of Contents: (00:00) Working on the Railroad (00:11) Excitement for the question and solution (01:05) Explanation of the solution using array formulas (02:41) Demonstration of the solution with different data (03:00) Mike's reaction and alternative solution using dynamic ranges (07:03) Mike's realization and demonstration of his solution (08:06) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial This video answers these common search terms: Array formula AVERAGE function Average rate calculation Control Shift Enter book Dynamic range using offset Excel live event Excel TV appearance Lookup function Rail Car number 123 Rail Car rental rates VBA solution Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153708/ Today's question: You have to rent rail tank car #123 for a 3 month period. The daily rate changes twice ; once in May, and once in June. Given the start date, end date, and the rate changes, how can you calculated the total rental charge or average per day? Bill shows how he has been reading MIke's Ctrl+Shift+Enter book and creates an array formula. Mike makes the formula better. Links: Mike's Ctrl+Shift+Enter book: http://www.mrexcel.com/2013books/cse2013book.html Bill's May 9 seminar in Tampa: https://www.eventbrite.com/e/bill-mrexcel-jelen-at-spring-ima-fl-council-seminar-8-cpe-tickets-10676733397 Mike's appearance on ExcelTV: https://www.youtube.com/watch?v=MFvRkEL2f1o
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920