auto calculating formula or macro, not sure.

Looking for a =SUM or =Vlookup or other formula/macro that can average 15 minutes of data based upon a THEORITICAL VALUE. Ideally, I would like to have the THEORITICAL VALUE in the formula and not reference a cell. I tried fooling around combining nested =countifs() formulas from other threads but failed.
The attached file provides a more descriptive explanation. Thanks in advance. MP

I've been fooling around with Solver for this but not sure if that works. Also the above file did not have the solver loaded into it when I uploaded it.

See the attached Excel 2003 file (which is a copy of your file with additions) that
contains Macros that should get you started. I don't think it does exactly what you want,
since it hard codes the 'start cell' and the 'theoretical value' for each of the three calculations.

The Macros are included here:

Function CalculateFifteenMinuteAverage(xTheoreticalValue As Single, sStartCell As String) As Single

  Dim jColumn As Long
  Dim jEndRow As Long
  Dim jStartRow As Long
  
  Dim xAverage As Single
  Dim xStandardDeviation As Single
  
  jColumn = Sheet1.Range(sStartCell).Column
  jStartRow = Sheet1.Range(sStartCell).Row
  
  jEndRow = jStartRow + 15 - 1
  
  xAverage = CSng(Application.WorksheetFunction.Average(Sheet1.Range(Cells(jStartRow, jColumn), Sheet1.Cells(jEndRow, jColumn))))

  CalculateFifteenMinuteAverage = xAverage

End Function

Sub ClearDataFromYellowArea()

  Const sRange = "E35:K44"
  Range(sRange).ClearContents
  
End Sub


Sub CalculateThreeAverages()

  Const sResultCOLUMN = "E"
  Const sResultTheoreticalCOLUMN = "G"
  Const nDatumResultROW = 34   'One less than the output start row

  Dim iRow As Integer


  Dim x As Single
  Dim xTheoreticalValue As Single

  Dim sStartCell As String
  
  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Initialization
  ''''''''''''''''''''''''''''''''''''''''''''''''
  
  'Clear the old data (then wait 1 second)
  Call ClearDataFromYellowArea
  Application.Wait DateAdd("s", 1, Now)
  
  'Assign the row prior to the Start Row
  iRow = nDatumResultROW

  iRow = iRow + 1
  Sheet1.Cells(iRow, sResultCOLUMN) = "15 Minute Averages"
  
  ''''''''''''''''''''''''''''''''''''''''''''''''
  'First Calculation
  ''''''''''''''''''''''''''''''''''''''''''''''''
  xTheoreticalValue = 20#
  sStartCell = "B32"

  x = CalculateFifteenMinuteAverage(xTheoreticalValue, sStartCell)

  iRow = iRow + 1
  iRow = iRow + 1

  Sheet1.Cells(iRow, sResultCOLUMN) = Format(x, "0.00")
  Sheet1.Cells(iRow, sResultTheoreticalCOLUMN) = "Theoretical Value = " & xTheoreticalValue


  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Second Calculation
  ''''''''''''''''''''''''''''''''''''''''''''''''
  xTheoreticalValue = 0#
  sStartCell = "B65"

  x = CalculateFifteenMinuteAverage(xTheoreticalValue, sStartCell)

  iRow = iRow + 1
  iRow = iRow + 1

  Sheet1.Cells(iRow, sResultCOLUMN) = Format(x, "0.00")
  Sheet1.Cells(iRow, sResultTheoreticalCOLUMN) = "Theoretical Value = " & xTheoreticalValue


  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Third Calculation
  ''''''''''''''''''''''''''''''''''''''''''''''''
  xTheoreticalValue = 85#
  sStartCell = "B117"

  x = CalculateFifteenMinuteAverage(xTheoreticalValue, sStartCell)

  iRow = iRow + 1
  iRow = iRow + 1

  Sheet1.Cells(iRow, sResultCOLUMN) = Format(x, "0.00")
  Sheet1.Cells(iRow, sResultTheoreticalCOLUMN) = "Theoretical Value = " & xTheoreticalValue


End Sub

Maybe Im missing something, but where does the "theoretical value" come in with this? In your sample workbook you have...
=AVERAGE(B32:B46) TV = 20
=AVERAGE(B65:B79) TV = 0
=AVERAGE(B117:B131) TV = 85

I dont get how you are using the 20, 0, 85???

I get this every day. The times are never exact, it is always plus/minus a few minutes from the start. I have to average out the data every time by adjusting the formula.

I'm looking for a formula or VBA code that can scan the minute data based upon a theoretical value and calculate a 15 minute average. The TV can be any value, 5, 100, 250, 450, 850. In this instance, its 20, 0 and 85.

This is a big code and it will take me a while to figure I all out. This is way beyond me so thanks for your patience in advance. So far so good.

I like how it calculates but is there a way to have the results populate specific cells? This is already done with the above post from LJM. My misunderstanding. I need to figure out how to change the cells it is assigned to.

I have to run, be back in an hour.

OK but I still dont get how "20" would apply to your formla/requirement. For the "20", you use the range =AVERAGE(B32:B46), I dont see where "20" fits in with that - if you weere looking to start at the 1st 20 minutes, then that starts in row 19, not 32.

Please explain exactly how the 20 (or whatever) fits in please? Also, you said you wanted it "in the formula and not reference a cell" - if it keeps changing, how do you intend to handle that (we can get there later, just asking)