Sales Required to achieve 6 month average

[anderson0511]

I need a formula for my sales team that will tell them the amount required to hit a target for their 6 month average
For example, in order to hit first target they need to achieve an average of $40000.00 in sales over 6 months. We are in the 6th month and want to show where they need to be to hit goals

Here is what I have

Month 1 $26000.00
Month 2 $32000.00
Month 3 $50000.00
Month 4 $28000.00
Month 5 $45000.00
Month 6 - ?

Right now, he is averaging $36200.00.
The goals for average sales are
$40000.00
$50000.00
$75000.00
$90000.00

So I want to show required sales to get to each number.

I can do this using goal seek, but it needs to be available for our sales force to plug in their numbers and have it calculated.

I appreciate any input

[MrShorty]

Before I make it an Excel problem, I would make it an algebra problem. Start with the definition of arithmetic average
average=sum(x's)/count(x's)
If I understand what you are doing, for a 6 month average
average=(x1+x2+x3+x4+x5+x6)/6
40000=(sum(month1to5)+month6)/6
Solve for month6 and put that formula into your month 6 cell.
Can I assume that your algebra is up to that?

[anderson0511]

Well I wouldn't say I don't know algebra but this just isn't clicking for me.

Month 6 can change based on the target (40, 50, 75, etc.)

I'll keep working on it.

[MrShorty]

6*40000=sum(month1to5)+month6 -- sales required to acheive 40000 average
6*50000=sum(month1to5)+month6 -- sales required to acheive 50000 average
and so on.
In Excel, if you put your desired target into a cell, your month6 formula can contain a reference to that cell. Then it will figure out what your needed month6 sales are to achieve whatever target you put in.

[anderson0511]

I was over thinking this I guess. This worked. Thanks so much