Look Who it Was Random video

Excel - Master Prorating Costs in Excel: Using Coercion and INDIRECT Function - Episode 829

567 views · Published 9 January 2009 · 2:59 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  Master Prorating Costs in Excel: Using Coercion and INDIRECT Function.

Welcome back to the MrExcel netcast where we tackle tough Excel questions sent in by our viewers. Today, we have a challenging question from Jonathan about prorating costs over a span of 60 days. He needs to allocate the cost into monthly buckets, but the program start and end dates can fall within 1, 2, or 3 months. How can we accurately distribute the cost for each month? 

To solve this problem, we will be exploring the amazing feature of coercing dates in Excel. In this video, we will be using the INDIRECT function to build a reference to a range of cells. By using the EVALUATE FORMULA icon, we can see that Excel stores dates as numbers, which allows us to manipulate them in various ways. 

In the example shown, we use the INDIRECT function to create a reference to 5 days, but in reality, this can be expanded to include up to 60 days. By using the ROW function, we can convert the dates into numbers and perform calculations on them. This allows us to accurately prorate the cost for each month, as shown in the next formula. 

This technique can be extremely useful in various scenarios, and we will be exploring more applications of it in tomorrow’s netcast. So make sure to tune in and learn how to use this feature to your advantage. Thank you for watching and don’t forget to subscribe to our channel for more helpful Excel tips and tricks. See you next time on the MrExcel netcast!

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/


Column A contains a start date. Column B contains an end date. You need to calculate how many days occur in each month of the program. In Episode 829, we learn how to coerce an array of dates from those two cells.

Table of Contents:
(00:00) Introduction and tough question from Jonathan
(00:19) Program start and end dates and cost allocation
(00:37) Prorating cost for partial months
(00:50) Using INDIRECT feature to build a reference
(01:21) Evaluating formula and creating large array
(01:53) Coercing 2 cells into a large array
(02:03) Using INDIRECT for row numbers
(02:29) Using array for calculations
(02:39) 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:
Advanced Excel functions for data manipulation
Calculating prorated costs in Excel
Coercing cells into arrays in Excel
Converting dates to numbers in Excel
Excel tips and tricks for financial calculations
How to allocate costs over multiple months in Excel
MrExcel netcast on prorating program costs
Summing values using INDIRECT function
Understanding date formatting in Excel
Using INDIRECT function in Excel
Using ROW function in Excel
YouTube video on prorating program costs

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

More from this channel