How to Prorate in Excel
The cells in an Excel spreadsheet allow you to enter numbers and perform mathematical calculations on those numbers, making it the ideal tool to prorate a number. When you have a value that is spread out over a period of time, like monthly rent or a bi-weekly paycheck, if you need to account for the value over a fraction of that time period, that process is called proration. Excel allows you to set up each factor in the proration formula in a different cell, making it simple to understand the mathematics behind the process.
Open a new Excel 2010 spreadsheet.
Click on cell "A1," which is the top cell in the leftmost column. Type in the total amount that you are trying to reduce to the prorated amount. If, for example, you are prorating a monthly bill, this is the usual monthly cost of the bill.
Click on cell "B1," which is directly to the right of the first cell. Enter in the total number of sub-periods within the overall period. For example, if you are prorating a monthly bill by a number of days, put the number of days in the month into this cell.
Click on cell "C1." Enter the number of sub-periods that you want to use to determine the prorated amount. To continue the example, this is the number of days that have passed in the month at the point in which you are determining the prorated amount.
Click on cell "B2," which is directly under your total number of sub-periods. Enter "=A1/B1" without quotes to give you the cost of each sub-period.
Click on cell "C3" and enter "=B2*C1" without quotes to give you your desired prorated amount.