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]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Month​

[/td][td="bgcolor:#F3F3F3"]

Sales​

[/td][td="bgcolor:#F3F3F3"]

Pro-Rate​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

Feb 2018​

[/td][td]

$735,556​

[/td][td="bgcolor:#CCFFCC"]

$8,091,116​

[/td][td]C2: =(13 - MONTH($A$2)) / COUNT(B$1:B2) * SUM(B$2:B2)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

Mar 2018​

[/td][td]

$779,689​

[/td][td="bgcolor:#CCFFCC"]

$8,333,848​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

Apr 2018​

[/td][td]

$842,064​

[/td][td="bgcolor:#CCFFCC"]

$8,643,466​

[/td][td][/td][/tr]
[/table]

 

[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]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td]Start Month[/td][td]Feb[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]Sales for Year[/td][td]

$735,556​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]Annualized[/td][td="bgcolor:#CCFFCC"]

$802,425​

[/td][td]B3: =12 / (13 - MONTH(1 & $B$1)) * B2[/td][/tr]
[/table]

 

[bflan0524]

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

 

[shg]

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