Trend to Budget

[EvansRJ]

Hi, Please help with what I think is a simple calculation

Cell C1 has monthly budget of 990 units
Cell C2 calculates daily rolling % to monthly budget
Cell C3 is the problem... I am trying to calculate rolling daily trend to budget

Cell A8-A38 has Days of month (May 1st-31st)
Cell C8-C38 has actual daily sales in units

I want C3 to populate with a % of what was sold to budget by day, adding each day...

990/31days = 32 units/day
May 1st budget = 32 Actual is 30. So my trend to budget % is 93.8%
May 2nd budget = 32 Actual is 28. So total rolling budget is now 64 for the 2 days and my actual is 58 so my trend to budget % is 90.6.

I want it to continue calculating the % from May 1 going forward adding each day.

Hope I explained the situation clearly enough.

Please help.

Thanks,
Ryan Evans

 

[braindiesel]

in cell D8, try this formula

=SUM($C$8:C8)/(COUNT($C$8:C8)*$C$1/COUNT($C$8:$C$38))

The first Sum gives the running total
the first count counts the days so far
C1 is your budget divided by the seconf count, the number of days in the period

Copy that formula down and that should solve it

 

[EvansRJ]

in cell D8, try this formula

=SUM($C$8:C8)/(COUNT($C$8:C8)*$C$1/COUNT($C$8:$C$38))

The first Sum gives the running total
the first count counts the days so far
C1 is your budget divided by the seconf count, the number of days in the period

Copy that formula down and that should solve it

Thanks for the info... it didn't exactly solve my problem but you gave me enough info to figure it out. Here is the formula that I ended up using.

=SUM(C8:C38)/((C2/(COUNT($A8:$A38)))*COUNT(C8:C38))

 

[braindiesel]

The difference between my formula and yours is that mine will show you the trending percentage on a daily basis, where yours will only show you the trending percentage today.

Whatever works, happy to help