Determine Future Value to Meet Average

[SteveG]

Hey all,

Having a typical Monday morning here. I have what I assume is an easy question but I can't get my brain around it.

I have a range of dates (A1:A22) for which I have a value (C1:C22 actuals formula reference to B1:B22) assigned to each. This data is input each day. In D1:D22 I have a value that represents a "goal" to reach for each day. This remains constant to meet the SLA with our client.

What I would like to do is in E1:E22 is to have a formula that will determine what tomorrow's "goal" should be based on the Actuals in B if the constant in D remains the same for the remainder of the days. So, AVERAGE what actuals we have with the constant for all days except tomorrow and determine what tomorrow's goal should be to meet the AVERAGE for the total days.

Hope that is clear. Any help is much appreciated.

Thanks
Steve

[Marcelo]

Hi, Steve.

If my understand was correct one way to do it is calc the difference between
the dates and have the avg.

you could try to use DATADIF, see the tip on this web page.

http://www.bettersolutions.com/excel...D711511811.htm

hope this helps
regards from Brazil
Marcelo

"SteveG" escreveu:

>
> Hey all,
>
> Having a typical Monday morning here. I have what I assume is an easy
> question but I can't get my brain around it.
>
> I have a range of dates (A1:A22) for which I have a value (C1:C22
> actuals formula reference to B1:B22) assigned to each. This data is
> input each day. In D1:D22 I have a value that represents a "goal" to
> reach for each day. This remains constant to meet the SLA with our
> client.
>
> What I would like to do is in E1:E22 is to have a formula that will
> determine what tomorrow's "goal" should be based on the Actuals in B if
> the constant in D remains the same for the remainder of the days. So,
> AVERAGE what actuals we have with the constant for all days except
> tomorrow and determine what tomorrow's goal should be to meet the
> AVERAGE for the total days.
>
> Hope that is clear. Any help is much appreciated.
>
> Thanks
> Steve
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=551028
>
>

[SteveG]

Marcelo,

Thanks for the response. I think I may not have explained clearly enough. The dates themselves do not really matter, it is the values corresponding to those dates that I need to average to "predict" what the next value needs to be to maintain a certain service level.

So if for days 1-5 of 22 my values average 150 and my service level agreement says I need to be at 80. Assuming that the rest of the days (excluding tomorrow) I maintain my level of 80, what does tomorrow's value need to be so the average of all 22 meets the service level of 80.

Hope that makes more sense.

Thanks,

Steve

Hi Steve

Try this in B2, with your list starting in A1:
=160-AVERAGE($A$1:A1)
If you fill this down to B22 and enter your numbers in A1:A22, you'll see
the required value change. If you want to hide the list until you reach the
cell, try this in A2 and fill down:
=IF(A1<>"",160-AVERAGE($A$1:A2),"")

Hope this helps.
Andy.

"SteveG" <[email protected]> wrote in
message news:[email protected]...
>
> Marcelo,
>
> Thanks for the response. I think I may not have explained clearly
> enough. The dates themselves do not really matter, it is the values
> corresponding to those dates that I need to average to "predict" what
> the next value needs to be to maintain a certain service level.
>
> So if for days 1-5 of 22 my values average 150 and my service level
> agreement says I need to be at 80. Assuming that the rest of the days
> (excluding tomorrow) I maintain my level of 80, what does tomorrow's
> value need to be so the average of all 22 meets the service level of
> 80.
>
> Hope that makes more sense.
>
> Thanks,
>
> Steve
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile:
> http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=551028
>

[SteveG]

Thanks Andy. That worked.

Steve