weighted percents

  • Thread starter Thread starter hef
  • Start date Start date
H

hef

is there an existing excel function that can do the following?


I can create a formula that says:
(sales jan/sum(sales jan:sales mar))*2nd Q sales.


IE:
Sales Jan 10
Sales Feb 60
Sales Mar 30
total 1st Q sales = 100

a1 -> 10% of total
a2 -> 60 % of total
a3 -> 30 % of total

now: from the above weights, I want to determine sales for Apr, May
Jun...say 2nd Q sales = 200 (I'm not looking for a rolling average)

then
Sales Apr = 200*.1 = 20
Sales May = 200*.6 = 120
Sales Jun = 200*.3 = 60
 
Hi hef!

It's easy enough to achieve but precise formulas will depend upon
details of your layout.

Apr is simply:

AprSales*(JanSales/Sum(JanToMarSales))
MaySales*(FebSales/Sum(JanToMarSales))
JunSales*(MarSales/Sum(JanToMarSales))

As for the validity of what you're doing....

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top