Determine Future Value to Meet Average

  • Thread starter Thread starter SteveG
  • Start date Start date
S

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,

Thanks for the response. I think I may not have explained clearl
enough. The dates themselves do not really matter, it is the value
corresponding to those dates that I need to average to "predict" wha
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 leve
agreement says I need to be at 80. Assuming that the rest of the day
(excluding tomorrow) I maintain my level of 80, what does tomorrow'
value need to be so the average of all 22 meets the service level o
80.

Hope that makes more sense.

Thanks,

Stev
 
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.
 
Back
Top