Help with pro ration

[bflan0524]

hello
i need help with figuring out a formula to prorate a persons sales for the year (based on monthly)

if column A had for example 735,556 and this person had come on in February- how would i pro rate their sales ?

 

[magpie2000k]

If the spread sheet has a sales persons start date you could use networkdays and as such divide the sales value by worked days

 

[shg]

	A​	B​	C​	D​
1​	Month​	Sales​	Pro-Rate​	​
2​	Feb 2018​	$735,556​	$8,091,116​	C2: =(13 - MONTH($A$2)) / COUNT(B$1:B2) * SUM(B$2:B2)
3​	Mar 2018​	$779,689​	$8,333,848​
4​	Apr 2018​	$842,064​	$8,643,466​

 

[bflan0524]

sorry, what if that # i provided the $735,556 was the final # and we know the person started in february and we wanted to find out what their prorated value was

 

[magpie2000k]

=(b2/(13-month($a$2)))

 

[shg]

	A​	B​	C​
1​	Start Month	Feb
2​	Sales for Year	$735,556​
3​	Annualized	$802,425​	B3: =12 / (13 - MONTH(1 & $B$1)) * B2

 

[bflan0524]

thank you very much, can you explain to me what this is doing Shg?

 

[shg]

Annualized = 12 / (months worked) * (sales for year)