To prorate in Microsoft Excel, start by entering the formula =A1/B1 in the Formula bar for cell B2, where A1 is the total cost and B1 is the total number of days to which the cost applies. Press Enter. Then select another cell and type the formula =B2*C1 in the Formula bar, where B2 is the previous result and C1 is the number of days that have gone by. Press Enter again to get the final amount.
Let’s take a look at this in more detail.
How to Prorate in Excel
Let’s get to know the indicators before we put together the formula. The first indicator is the total cost, which we put in cell A1. Cell B1 contains the total number of days covered by the total cost. Cell C1 displays the current number of days that have gone by. This calculation will show us how much money has been used from the start of the cost date to today.
1. Click on a blank cell where you will start by calculating the cost per day.
2. Go to the Formula bar and enter the formula =A1/B1.
3. Press Enter on your keyboard.
And with that, the cost per day has been calculated.
Now let’s calculate the prorated amount.
1. Select the cell where you want to display the prorated value.
2. Navigate to the Formula bar and type the formula =B2*C1, where B2 is the amount per day that we just calculated in the previous step and C1 is the number of days that have elapsed.
3. Press Enter.
Voila! You’ve successfully done it!
Curious about more information on prorating? Read on to cover some additional questions and answers to learn more information on the subject.
What Is Prorating?
Prorating in Excel is the relatively simple process of calculating an amount that is proportionate to another amount. This is often used to determine partial payments and potential refunds.
What Are Some Common Situations that Use Prorating?
Prorating can be useful in multiple situations, including allocating shared expenses, calculating partial amounts, and more.
Some common situations requiring prorating include:
- Allocating shared expenses: Prorating can be used to calculate how much each person should pay for shared expenses, such as utilities for an apartment complex.
- Calculating partial amounts: Prorating can be used to calculate partial amounts, such as prorating rent for a new tenant who moves in on the 15th of the month rather than at the beginning of the month.
- Determining pro-rata share: Prorating can be used to determine each person’s pro-rata share of an expense, such as prorating the cost of a hotel room among the people who stay in it.
- Adjusting for partial periods: Prorating can be used to adjust amounts for partial periods.
How to Prorate Sales?
To prorate sales, you will need to determine the proration factor, which is the proportion of the total that you are prorating. For example, if you are prorating rent for a new tenant who moves in on the 15th day of the month, the proration factor would be 15/30, or 1/2.