Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Pro rata of amounts of different rents for different hours


I have to invoice a dance hall.  They rent a hall for 55 1/2 hours a week. The first hour is charged at £20 with subsequent hours at £10.  I have been asked to work out the cost of the rent for half an hour pro rata.  Apparently it can be done with Excel.

Thank you.



Selected Answer

Yea, you can do A LOT of things in Excel and this is certainly one of those things.

How you do it though depends on how you want the spreadsheet to look and function.

The simplest way to set this up is probably a math formula that calculates if the rented time is greater than 1 hour and then multiplies that amount by 10 plus 10 for the additional 10 pounds that the first hour costs.

Here is a formula that could get you started:


This assumes that the number of hours is in cell A1 and that you charge a flat fee of 20 for anything that is 1 hour or less.

Once you have the total amount you can then do whatever you want with it.

If you need the cost per 30 minutes, you could do something like this:


B1 contains the first formula I included.



Thanks very much.  That is a huge help.  Thanks again. Have a good day!
katemwyatt (rep: 2) Oct 18, '16 at 5:25 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login