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

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

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

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" wrote in
message ...

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

Thanks Andy. That worked.

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=551028