Variable measurement analysis
 

Excel 2003 SP3

Hope this is not too sketchy...
I need to review some data in an Excel spreadsheet that is setup somewhat
like this:
Column A = Date of measurement
Column B = Day of week measurement
Column C = 12AM Start of day measurement (like a temperature in degrees
Fahrenheit)
Column D = High during the day (measured)
Column E = Low during the day (measured)
Column F = 12AM end of day measurement
Here's what I need:
I'd like to setup a Column G, say, that would provide the average of ANY
interval I select (1 day, 2 day, 3 day, 4 day, 5 day, ....10 day etc.)
[Column G can be used for the Start of Day measurements, Column H could be
High measurement analysis etc.]
I'd like to be able to have an 'x' day analysis where 'x' is 1, 2, 3, 4 or 5
and then just review the data as it is calculated and based on the 'x'
selected,. Hence, a '5' could be the last 5 days of temperature averages and
10 would be the last 10 days of temperature averages.
So, how can I make the column variable? (I know how to do the division
here, it's the SUM() for whatever RANGE of days that I can't seem to know how
to do.)
TIA

Variable measurement analysis
 

Same as previous post but MS indicated NEITHER one went through! Sorry about
that...

"Tom" wrote:

Excel 2003 SP3

Hope this is not too sketchy...
I need to review some data in an Excel spreadsheet that is setup somewhat
like this:
Column A = Date of measurement
Column B = Day of week measurement
Column C = 12AM Start of day measurement (like a temperature in degrees
Fahrenheit)
Column D = High during the day (measured)
Column E = Low during the day (measured)
Column F = 12AM end of day measurement
Here's what I need:
I'd like to setup a Column G, say, that would provide the average of ANY
interval I select (1 day, 2 day, 3 day, 4 day, 5 day, ....10 day etc.)
[Column G can be used for the Start of Day measurements, Column H could be
High measurement analysis etc.]
I'd like to be able to have an 'x' day analysis where 'x' is 1, 2, 3, 4 or 5
and then just review the data as it is calculated and based on the 'x'
selected,. Hence, a '5' could be the last 5 days of temperature averages and
10 would be the last 10 days of temperature averages.
So, how can I make the column variable? (I know how to do the division
here, it's the SUM() for whatever RANGE of days that I can't seem to know how
to do.)
TIA

Variable measurement analysis
 

Column C = 12AM Start of day measurement (like a temperature in degrees
Fahrenheit)
.. setup a Column G, say, that would provide the average of ANY
interval I select (1 day, 2 day, 3 day, 4 day, 5 day, ....10 day etc.)

Assuming source data as described in row2 down,
with real dates, chronologic in A2 down
It's also assumed there are no blank intervening rows within the data

In G1, you can specify the number of days, eg: 2 or 7, etc
Then this in say, G2: =AVERAGE(OFFSET(C2,,,G1))
will return the average of what's in C2:Cx
where x = that number of days specified in G2 (G2 = the offset's height param)

And if you want it flexible from a certain startdate (in col A) instead of
just starting from C2 down, you could have the startdate specified in G2, eg:
15 Jan 2008, then use this in G3:
=AVERAGE(OFFSET(C2,MATCH(G2,A2:A100,0)-1,,G1))

As further options ...
a. If you want to average col C over a certain date range (real dates in col
A), you could use an array-entered* expression like this:
=AVERAGE(IF((A2:A10=--"15 Jan 2008")*(A2:A10<=--"14 Feb 2008"),C2:C10))
*press CTRL+SHIFT+ENTER to confirm the formula
Adapt the ranges to suit the full extents of your data

If you want to average col C for all dates (in col A) in a certain
month/year,
use an array-entered* expression like this:
=AVERAGE(IF((TEXT(A2:A10,"mmmyyyy")="Jan2008"),C2: C10))
*press CTRL+SHIFT+ENTER to confirm the formula
Adapt the ranges to suit the full extents of your data

Do press the YES button below to high-five it, if you found the above helpful
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Tom" wrote:
Same as previous post but MS indicated NEITHER one went through! Sorry about
that...

"Tom" wrote:

Excel 2003 SP3

Hope this is not too sketchy...
I need to review some data in an Excel spreadsheet that is setup somewhat
like this:
Column A = Date of measurement
Column B = Day of week measurement
Column C = 12AM Start of day measurement (like a temperature in degrees
Fahrenheit)
Column D = High during the day (measured)
Column E = Low during the day (measured)
Column F = 12AM end of day measurement
Here's what I need:
I'd like to setup a Column G, say, that would provide the average of ANY
interval I select (1 day, 2 day, 3 day, 4 day, 5 day, ....10 day etc.)
[Column G can be used for the Start of Day measurements, Column H could be
High measurement analysis etc.]
I'd like to be able to have an 'x' day analysis where 'x' is 1, 2, 3, 4 or 5
and then just review the data as it is calculated and based on the 'x'
selected,. Hence, a '5' could be the last 5 days of temperature averages and
10 would be the last 10 days of temperature averages.
So, how can I make the column variable? (I know how to do the division
here, it's the SUM() for whatever RANGE of days that I can't seem to know how
to do.)
TIA