auto calculating formula or macro, not sure.

[Mechanical Pencil]

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.

[LJMetzger]

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:

Please Login or Register  to view this content.

[Mechanical Pencil]

Its a start and I will fool around with it and report back. I'm getting better with VBA. Thanks a bunch.

[Alf]

Hi Metzger

Most impressive macro! Found a small glich in row below "First Calculation"

Please Login or Register  to view this content.

should be 20 of cource.

Alf

[FDibbins]

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???

[LJMetzger]

Alf,

Thanks for catching my error. If you need statistics, it's relatively easy to add standard deviation, and to search for outlier values (outside of +/- 2 standard deviations, or +/- 3 standard deviations, etc.).

Lewis

[Mechanical Pencil]

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.

[FDibbins]

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)

[Mechanical Pencil]

Look at the table and chart. It displays a readout of a select ppb value over time. It starts with 20, then a 0 then an 85ppb value. The selected average of the minute data is closest to the theoretical value of 20, 0 and 85. In that order. And it has to be a continuous 15 minute average. The 3 =AVERAGE(B32:B46) formulas I have in the sample file I attached is what I am trying to automate for the 3 separate values. The times are not always the same. They change in either direction plus/minus a few minutes. I'm getting tired of repeating this calculation every day, importing the data and adjusting the current average range. That's is what I am looking to automate. Hope that helps a little.

I still need time to fool around with the code.

[ben_hensel]

Those flat bits are all at least 30 minutes long.

How are you picking the sample ranges? Do you want the first 15 minutes, the last 15 minutes, or the 15 minutes in the middle? Those are all going to be different.

I've attached something that will demonstrate what I mean.

[LJMetzger]

See the attached spreadsheet which may be close to the answer you seek.

I got lucky and may have found a way to automate the 'Starting Point' selection process for
each of the three theoretical values. I added 'Standard Deviations' for 15 minute intervals
in 'Column C' of the spreadsheet. They are not used for anything (and can be removed), however
they seem to indicate that a 'Starting Point' can be obtained by finding the 'Potential Start Row' that
has the LOWEST '15 mintue Standard Deviation' that is below an 'arbitrary threshhold value'.

All the 'Starting Points' you used had '15 minute Standard Deviation' values
less than 0.06. I set the 'arbitrary threshhold value' to 0.20 and seemed to be successful in
performing the calculations. You may want to adjust my algorithm to suit your purposes (e.g. you may
want the data more centered).

[Mechanical Pencil]

How are you picking the sample ranges? Do you want the first 15 minutes, the last 15 minutes, or the 15 minutes in the middle?

I understand.

This is where the theoretical value comes into play. I am looking for consecutive 15 minutes average closest to the theoretical value. It can be any 15 consecutive minute average. Usually its the average closest to the end.

[LJMetzger]

Since the solution I posted earlier today is based on the 15 minute period with the lowest standard deviation, it should give you exactly what you want.

Lewis

[Mechanical Pencil]

This looks and works good. I'll fool around with it and try to expand upon it. Thanks

[one_genuine]

Hi LJMetger, Thanks a lot. I browsed the code and I will definitely try it out. Thanks a lot for such good insight!