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

Watch on YouTube

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