Formula to display increase required to achieve average?

[slinky]

Sorry folks, brain has fallen out of my ear.


I have a table listing "sales" per week. Table has 52 columns to represent year.
Averages are generated for Q1/Q2/Q3/Q4 & YTD using the SUM/COUNTA method.


If I wanted to display the weekly performance required to bring the average to a target level, without using "what if" / Goal Seek, how would I go about it?


Any thoughts?

 

[Gerald Higgins]

OK, here's my thoughts.

Let's consider a simplified example, looking at 4 weeks.
Let's assume your data is setup like this . . .
Week names (Wk01 etc) in the range A1:A4
Weekly actual data in the range B1:B4

Target average is held in cell E2.

Current average based on actual data is calculated in F2, with this formula
=SUM(A2:D2)/COUNTA(A2:D2)

Required weekly run rate to hit the target average is calculated in G2, with this formula
=((E2*COUNTA(A1:D1))-(F2*COUNTA(A2:D2)))/(COUNTA(A1:D1)-COUNTA(A2:D2))

This might get you started.

There may be better ways of doing this, but this seems to work.

 

[slinky]

That works nicely Gerald, thanks very much.
I've built a Q1/2/3/4 table with actual/required and it's playing ball perfectly.
Also, your use of the term run rate reminded me of the term I needed to remember, so thanks again on that front!