Excel - Calculate Rent on a Sliding Scale in Excel | Multiple Solutions - Episode 1775

3,138 views · Published 16 August 2013 · 7:17 · Indexed 12 November 2026

Channel: MrExcel.com · 2013 · Education

Watch on YouTube

Microsoft Excel Tutorial: Calculate Rent on a Sliding Scale in Excel | Multiple Solutions.

Welcome back to another episode of Dueling Excel, where Mike Girvin from ExcelIsFun and I, Bill Jelen from MrExcel, tackle a challenging Excel problem. In this episode, we will be calculating rent on a sliding scale, where the rent changes based on the number of days the container is rented for.

The first week, the rent is $5 per day, the next two weeks it increases to $10 per day, and after that, it jumps to $20 per day. This may seem like a complicated problem, but with the help of Excel, we will find a solution in no time.

At first, I thought this problem would require a lot of brain work, but then I remembered a similar problem I had encountered before - a sliding income tax scale. So, instead of trying to figure it out manually, I decided to switch over to VBA and create a user-defined function called RENT. This may not be the most efficient code, but it gets the job done without any complicated thinking on my part.

Mike also came up with a clever solution using the IF function, which works well for smaller tables. However, for larger tables with multiple categories, it may not be the most practical solution. That's where the LOOKUP function comes in handy. Mike walks us through how to use VLOOKUP to create a table and then use that table to calculate the rent based on the number of days.

But wait, there's more! I also came up with a solution using VBA, where I used the SELECT CASE DAYS statement to determine the rent based on the number of days. This may not be the most efficient code, but it gets the job done and gives us the same results as the other solutions.

In the end, there are multiple ways to solve this problem, and it's always interesting to see how different people approach it. We hope you found this episode helpful and learned something new. Don't forget to tune in next week for another exciting episode of Dueling Excel. Thanks for watching!

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) Calculate Rent on a Sliding Scale
(00:11) Question: Renting a container with changing daily rates
(00:21) Explanation of sliding scale rates for 41 days
(00:31) VBA solution for calculating rent
(01:00) Inefficient code but fast calculation
(01:51) Alternative solution using IF and AND functions
(03:48) Alternative solution using VLOOKUP
(05:58) Mike's solution using VLOOKUP and nested IF functions
(06:52) Bill's solution using SELECT CASE in VBA
(07:02) 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:
Calculate rent on a sliding scale
Container rental
Different ways to solve rent calculation problem
IF function for rent calculation
Inefficient user-defined function
LOOKUP function for rent calculation
MrExcel and ExcelIsFun podcast
Rent calculation logic
Sliding scale rent calculation
VBA code for rent calculation
VLOOKUP function for rent calculation


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153596/


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.

More from this channel