Getting an Daily Avarage

[Ardy]

Hello All:
I am looking for some help here….I have a series of spreadsheets
coming to me that I need to have it do a daily average. The
spreadsheet is hourly record of water level for various wells. I have
been using macros to get this going but have been unsuccessful. The
full day is consist of 24 reading one for each hour, given that we
have full day sometimes is less and that is part of my problem. The
daily avg is the sum of DTW_AF/by the number of readings. I run into
problem when I want the code to find the beginning and the end of the
day by date and hour and have the code do a count of records to use
for the divide part of the avg………


A B C D E F
G H I J K
Date time hours psi corrected_pm Temp Level_D +/-
DTW DTW_AF Daily Avg
1
2
3
etc

Ardy

 

[smartin]

Hello All:
I am looking for some help here….I have a series of spreadsheets
coming to me that I need to have it do a daily average. The
spreadsheet is hourly record of water level for various wells. I have
been using macros to get this going but have been unsuccessful. The
full day is consist of 24 reading one for each hour, given that we
have full day sometimes is less and that is part of my problem. The
daily avg is the sum of DTW_AF/by the number of readings. I run into
problem when I want the code to find the beginning and the end of the
day by date and hour and have the code do a count of records to use
for the divide part of the avg………


A B C D E F
G H I J K
Date time hours psi corrected_pm Temp Level_D +/-
DTW DTW_AF Daily Avg
1
2
3
etc

Hi Ardy,

Let me make sure I understand the problem:

You want to find the straight (unweighted) average of DTW/AF by Date,
but the number of readings within Date is variable?

If this is correct, I don't think you need any macro as a worksheet
function will do just fine.

Put a date you want to check in cell X2 and this *array* formula in Y2:
*commit array formulae by pressing Ctrl + Shift + Enter*
=AVERAGE(IF(X2=[your date values],[your DTW/AF values]))

I am assuming here that your "Date" column is exactly that -- a date
data type.

 

[Ardy]

Hello All:
I am looking for some help here….I have a series of spreadsheets
coming to me that I need to have it do a daily average.  The
spreadsheet is hourly record of water level for various wells.  I have
been using macros to get this going but have been unsuccessful.  The
full day is consist of 24 reading one for each hour,  given that we
have full day sometimes is less and that is part of my problem.  The
daily avg is the sum of DTW_AF/by the number of readings.  I run into
problem when I want the code to find the beginning and the end of the
day by date and hour and have the code do a count of records to use
for the divide part of the avg………

        A      B      C     D            E                 F
G         H      I             J           K
    Date  time  hours  psi  corrected_pm   Temp   Level_D    +/-
DTW    DTW_AF  Daily Avg
1
2
3
etc

Hi Ardy,

Let me make sure I understand the problem:

You want to find the straight (unweighted) average of DTW/AF by Date,
but the number of readings within Date is variable?

If this is correct, I don't think you need any macro as a worksheet
function will do just fine.

Put a date you want to check in cell X2 and this *array* formula in Y2:
*commit array formulae by pressing Ctrl + Shift + Enter*
=AVERAGE(IF(X2=[your date values],[your DTW/AF values]))

I am assuming here that your "Date" column is exactly that -- a date
data type.- Hide quoted text -

- Show quoted text -

Smartin:
Thank you for replying, I did looked at the arrays but couldn’t quit
figure it. It is a straight trough average, but the function needs to
find couple of variables on it’s own from existing data first…… let me
explain data acquisition and the variables to include the columns.
The instrument collects data on an Hourly basis, so on top of each
hour it records date(A), time(B), dtw(J), all of said data is in an
spreadsheet. Since we don’t want to push in gobs of data into our
database we decided to push in just the daily average of dtw into the
database, which means 1 data point for each day Vs 24 or less(the
reason for less is that sometimes the instrument misses an hour or
so, the day is not 24 collected data at all times).

Column A:
This column is the date variable which is formatted as date “mm/dd/
yyyy”
Column B:
This column is the time variable which is formatted as 12/h “hh:mm AM/
PM”
Column J:
This the Depth To Water(DTW). Formatted as number
Column K:
This is the Daily Average. Formatted as number

The function needs to first look at Column A and filter each day(what
I mean by each day is that, we have assuming there were no misses 24
reading for each day so 1/1/2008 is repeated in column A 24 times)
then it needs to do a COUNT and keep that number in this case number
24. Then it needs to do a SUM in Column J(DTW) and divide that sum by
the 24 which is the AVG. This average needs to go to column K right
next the last reading in column J. this needs to get repeated (loop)
until there is no more dates……..

A B …. J K
1 date Time …. dtw Daily_Avg
2 data data data
3 data data data
3 data data data AVG
4 data data data
5 data data data
6 data data data AVG

 

[smartin]

Hello All:
I am looking for some help here….I have a series of spreadsheets
coming to me that I need to have it do a daily average. The
spreadsheet is hourly record of water level for various wells. I have
been using macros to get this going but have been unsuccessful. The
full day is consist of 24 reading one for each hour, given that we
have full day sometimes is less and that is part of my problem. The
daily avg is the sum of DTW_AF/by the number of readings. I run into
problem when I want the code to find the beginning and the end of the
day by date and hour and have the code do a count of records to use
for the divide part of the avg………
A B C D E F
G H I J K
Date time hours psi corrected_pm Temp Level_D +/-
DTW DTW_AF Daily Avg
1
2
3
etc

Hi Ardy,

Let me make sure I understand the problem:

You want to find the straight (unweighted) average of DTW/AF by Date,
but the number of readings within Date is variable?

If this is correct, I don't think you need any macro as a worksheet
function will do just fine.

Put a date you want to check in cell X2 and this *array* formula in Y2:
*commit array formulae by pressing Ctrl + Shift + Enter*
=AVERAGE(IF(X2=[your date values],[your DTW/AF values]))

I am assuming here that your "Date" column is exactly that -- a date
data type.- Hide quoted text -

- Show quoted text -

Smartin:
Thank you for replying, I did looked at the arrays but couldn’t quit
figure it. It is a straight trough average, but the function needs to
find couple of variables on it’s own from existing data first…… let me
explain data acquisition and the variables to include the columns.
The instrument collects data on an Hourly basis, so on top of each
hour it records date(A), time(B), dtw(J), all of said data is in an
spreadsheet. Since we don’t want to push in gobs of data into our
database we decided to push in just the daily average of dtw into the
database, which means 1 data point for each day Vs 24 or less(the
reason for less is that sometimes the instrument misses an hour or
so, the day is not 24 collected data at all times).

Column A:
This column is the date variable which is formatted as date “mm/dd/
yyyy”
Column B:
This column is the time variable which is formatted as 12/h “hh:mm AM/
PM”
Column J:
This the Depth To Water(DTW). Formatted as number
Column K:
This is the Daily Average. Formatted as number

The function needs to first look at Column A and filter each day(what
I mean by each day is that, we have assuming there were no misses 24
reading for each day so 1/1/2008 is repeated in column A 24 times)
then it needs to do a COUNT and keep that number in this case number
24. Then it needs to do a SUM in Column J(DTW) and divide that sum by
the 24 which is the AVG. This average needs to go to column K right
next the last reading in column J. this needs to get repeated (loop)
until there is no more dates……..

A B …. J K
1 date Time …. dtw Daily_Avg
2 data data data
3 data data data
3 data data data AVG
4 data data data
5 data data data
6 data data data AVG

Hi Ardy,

Thanks for taking the time to explain your problem in detail.

That's almost exactly what the formula I gave does, although I bypass
collecting counts and sums by applying the AVERAGE directly to the data,
using a filter (IF) to average by day.

If you put the formula in column K, and point [your date values] to
$A$2:$A$x and [your DTW/AF values] to $J$2:$J$x, you will get a daily
average on every row. If you want to display the average only on the
last row per day, you can hide the other results.

Here's how it looks from my angle:

A J K L
Dates DTW Av Hidden
24-Aug-08 80 68
24-Aug-08 56 68 68
25-Aug-08 65 87
25-Aug-08 97 87
25-Aug-08 99 87 87
26-Aug-08 52 66
26-Aug-08 80 66 66
27-Aug-08 62 80.5
27-Aug-08 99 80.5 80.5


K2: =AVERAGE(IF($A2=$A$2:$A$10,$J$2:$J$10))
L2: =IF($A2<>$A3,AVERAGE(IF($A2=$A$2:$A$10,$J$2:$J$10)),"")

Again, these are all array formulas. Ctrl+Shift+Enter. Arrays are not
for everyone, but in this case arrays offer a succinct alternative to
creating helper columns and storing intermediate results.

If you want to do this with counts and sums, we can do that too. In a
few more columns, place these (not array) formulae:

COUNTS: =COUNTIF($A$2:$A$10,$A2)
SUMS: =SUMIF($A$2:$A$10,$A2,$J$2:$J$10)

Then the average is
AV: =SUMS/COUNTS

Hope this helps!

 

[Ardy]

Ardy wrote:
Hello All:
I am looking for some help here….I have a series of spreadsheets
coming to me that I need to have it do a daily average.  The
spreadsheet is hourly record of water level for various wells.  I have
been using macros to get this going but have been unsuccessful.  The
full day is consist of 24 reading one for each hour,  given that we
have full day sometimes is less and that is part of my problem.  The
daily avg is the sum of DTW_AF/by the number of readings.  I run into
problem when I want the code to find the beginning and the end of the
day by date and hour and have the code do a count of records to use
for the divide part of the avg………
        A      B      C     D           E                 F
G         H      I             J           K
    Date  time  hours  psi  corrected_pm   Temp   Level_D    +/-
DTW    DTW_AF  Daily Avg
1
2
3
etc
Hi Ardy,
Let me make sure I understand the problem:
You want to find the straight (unweighted) average of DTW/AF by Date,
but the number of readings within Date is variable?
If this is correct, I don't think you need any macro as a worksheet
function will do just fine.
Put a date you want to check in cell X2 and this *array* formula in Y2:
*commit array formulae by pressing Ctrl + Shift + Enter*
=AVERAGE(IF(X2=[your date values],[your DTW/AF values]))
I am assuming here that your "Date" column is exactly that -- a date
data type.- Hide quoted text -
- Show quoted text -

Smartin:
Thank you for replying, I did looked at the arrays but couldn’t quit
figure it.  It is a straight trough average, but the function needs to
find couple of variables on it’s own from existing data first…… let me
explain data acquisition and the variables to include the columns.
The instrument collects data on an Hourly basis, so on top of each
hour it records date(A), time(B), dtw(J), all of said data is in an
spreadsheet.  Since we don’t want to push in gobs of data into our
database we decided to push in just the daily average of dtw into the
database, which means 1 data point for each day Vs 24 or less(the
reason for less is that sometimes the instrument misses an hour or
so,  the day is not 24 collected data at all times).

Column A:
This column is the date variable which is formatted as date “mm/dd/
yyyy”
Column B:
This column is the time variable which is formatted as 12/h “hh:mm AM/
PM”
Column J:
This the Depth To Water(DTW). Formatted as number
Column K:
This is the Daily Average. Formatted as number

The function needs to first look at Column A and filter each day(what
I mean by each day is that,  we have assuming there were no misses 24
reading for each day so 1/1/2008 is repeated in column A 24 times)
then it needs to do a COUNT and keep that number in this case number
24.  Then it needs to do a SUM in Column J(DTW) and divide that sum by
the 24 which is the AVG.  This average needs to go to column K right
next the last reading in column J. this needs to get repeated (loop)
until there is no more dates……..

       A           B        ….          J                K
1  date       Time    ….        dtw       Daily_Avg
2  data        data              data
3  data        data              data
3 data         data              data         AVG
4  data        data              data
5  data        data              data
6  data        data              data         AVG

Hi Ardy,

Thanks for taking the time to explain your problem in detail.

That's almost exactly what the formula I gave does, although I bypass
collecting counts and sums by applying the AVERAGE directly to the data,
using a filter (IF) to average by day.

If you put the formula in column K, and point [your date values] to
$A$2:$A$x and [your DTW/AF values] to $J$2:$J$x, you will get a daily
average on every row. If you want to display the average only on the
last row per day, you can hide the other results.

Here's how it looks from my angle:

     A           J       K        L
Dates          DTW     Av      Hidden
24-Aug-08      80      68
24-Aug-08      56      68      68
25-Aug-08      65      87
25-Aug-08      97      87
25-Aug-08      99      87      87
26-Aug-08      52      66
26-Aug-08      80      66      66
27-Aug-08      62      80.5
27-Aug-08      99      80.5      80.5

K2: =AVERAGE(IF($A2=$A$2:$A$10,$J$2:$J$10))
L2: =IF($A2<>$A3,AVERAGE(IF($A2=$A$2:$A$10,$J$2:$J$10)),"")

Again, these are all array formulas. Ctrl+Shift+Enter. Arrays are not
for everyone, but in this case arrays offer a succinct alternative to
creating helper columns and storing intermediate results.

If you want to do this with counts and sums, we can do that too. In a
few more columns, place these (not array) formulae:

COUNTS: =COUNTIF($A$2:$A$10,$A2)
SUMS:   =SUMIF($A$2:$A$10,$A2,$J$2:$J$10)

Then the average is
AV:     =SUMS/COUNTS

Hope this helps!- Hide quoted text -

- Show quoted text -

Thanks Smartin:
Your efforts in explaining and helping Deserves an A+, I need to hit
the books and take your solution and play with the spreadsheet until I
see and undrestand this. Unfortunetly Arrays are not my strong suit
and I guess this is the time to learn it using an live work
example..........

Much thanks.......
I will post questions once I read a bit more.......

Ardy