+ Reply to Thread
Results 1 to 6 of 6

Calculate avg & median hours in a date series based on values in separate column

  1. #1
    Registered User
    Join Date
    08-05-2017
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    7

    Calculate avg & median hours in a date series based on values in separate column

    Hi all

    I'm working with stream flow discharge data which has a discharge value associated with the date-time each reading was taken. I am trying to determine what the average and median number of hours the stream is above a certain discharge level. I would also like to know the number of events that the stream is above a certain discharge. Note: an event is the total time that the stream discharge is elevated above a certain level. This information will be used to determine how many times stream flow is high enough to allow fish to swim upstream past a weir.

    In the spreadsheet attached I have two columns titled 'adequate fish passage' and 'unimpeded fish passage', with the respective discharge values in a reference cell above the columns. I have been able to identify which readings are above the respective values (filter for value 1 to identify), but cannot determine the descriptive stats I'm chasing. There aren't too many events in the example attached so I could do it manually, but other streams have thousands of events in the dataset.

    Any help on this would be greatly appreciated.

    Kind regards,

    Trent
    Attached Files Attached Files

  2. #2
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Calculate avg & median hours in a date series based on values in separate column

    Is this what you're after. Durations shown in minutes

    EDITED AFTER FORD'S COMMENTS BELOW> NOT SURE IF THEY WERE AIMED AT ME OR OP BUT HERE IS A FULLER EXPLANATION

    In D11
    Please Login or Register  to view this content.
    This is an array formula and needs to be entered with control + shift + enter

    In D12

    Please Login or Register  to view this content.
    and in D13

    Please Login or Register  to view this content.
    E11, E12, E13 are similar
    Last edited by Crooza; 04-30-2019 at 02:14 AM.
    Happy with my advice? Click on the * reputation button below

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Calculate avg & median hours in a date series based on values in separate column

    Quote Originally Posted by Crooza View Post
    Is this what you're after. Durations shown in minutes
    Sorry for off-topic interjection:

    Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

    Thanks again for all your hard work here!
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Calculate avg & median hours in a date series based on values in separate column

    Ford

    I get that and usually do it the way you have described but there were 6 different formula's created plus a change to the format of one of the existing formulas and the OP didn't set out exactly how he wanted it presented so felt it was easier to throw the spreadsheet up there and answer questions later.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Calculate avg & median hours in a date series based on values in separate column

    Crooza, thanks for the extra detail

    I fully understand that sometimes, it becomes complex to describe what you have done, but your attempt looks great - thanks

  6. #6
    Registered User
    Join Date
    08-05-2017
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    7

    Re: Calculate avg & median hours in a date series based on values in separate column

    Thanks for the reply. It wasn't quite what I was looking for but got me onto a method that worked.

    To simplify things I downloaded the average hourly gauge data. This allowed me to use value counts to calculate the flow duration times (i.e the number of values above a threshold is equivalent to the flow duration). The time between readings in the original dataset varied - Crooza's method worked for calculating the number of events and average flow duration but I couldn't expand that to determine other descriptive stats (e.g. min, max, median and range for event duration).To do this I added three columns with each refining the information until I could run the descriptive stats formulas on the third column.

    Attached is a sample spreadsheet with the formulas used - To explain I'll use the values for "Adequate fish Passage"

    Threshhold discharge (B2) - flows above this provide adequate passage.

    Column C contains discharge values

    Column D identifies which values are above (B2) using formula: =IF($C18>=$B$2,1,"")

    Column E counts the number of consecutive duplicates (event duration) for values identified in column D or returns a 1 if no value in column D. Formula: =IF(D19=1,IF(D18=D19,E18+1,1),1) Note: a value of 1 was entered in (E18) for this to work.

    Column F filters out the maximum value for each unique event using formula: =IF(AND(C18>$B$2,E18=E19),E18,IF(AND(C18>$B$2,E18>E19),E18,""))

    Finally, total stream flow duration was determined using formula: =COUNTIF(C18:C8321,">"&"0")


    From this I was able to calculate the following descriptive stats:


    Number of events: =COUNT(F18:F8321)

    Total time of events: =SUMPRODUCT(D18:D8321)

    Min Duration: =MIN(F18:F8321)

    Max Duration: =MAX(F18:F8321)

    Average event duration: =AVERAGE(F18:F8321)

    Median event duration: =MEDIAN(F18:F8321)

    Percent of total flow duration: =COUNTIF($C$18:$C$8321,">="&$B$2)/$M$16


    I'm sure that there are much more elegant ways of calculating these stats. Ideally I'd prefer to work directly with the time series and discharge data using the dataset in my original post. One of the disadvantageous of using the average hourly data is that it misses the peak readings of short, intense events (flash floods). For now this works but if anyone has any suggestions on how to simplify the methods in this post or incorporate the variable time series in my original post I'd love to hear them.

    Thanks again,

    Trent.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Calculate workday date and time based on hours
    By rdd2 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-23-2019, 04:43 PM
  2. [SOLVED] Excel2010 IF Formula to return value based on a separate date and two separate text values
    By jakecutler in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-12-2015, 03:24 AM
  3. Calculate date based on number of hours
    By SuddenImpact in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-25-2015, 08:59 AM
  4. [SOLVED] Calculate Work hours between date range based on Odd/Even weeks
    By KeAnd31 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-04-2014, 07:53 AM
  5. Calculate mean of column B values based on date values in column A
    By up&down in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-05-2013, 12:26 PM
  6. Calculate START date based on duration (work hours) and END date
    By kaaver in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-06-2010, 12:21 PM
  7. Calculate start date based on working hours and end date
    By kaaver in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-28-2010, 06:58 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1