Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Aug 2009
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am having trouble figuring out this question posed by one of co-workers. It is hard to explain so I included a sample excel sheet.

    The summary data at the top lets the user pick a range of time from a set of raw data at the bottom of the sheet. The raw data varies in length and starting row. In the example, I want to average the "Value" column from a start time of 3 to end time of 6.

    So if I did it manually my formula would be =Average(B9:B143). The column letter I can pick and enter manually. What I can't figure out is how to write a forums that will take the value in B3 search for it in the Raw Data Table and return the row number it was found in. I could then do the same for the end time.

    If this doesn't make sense after viewing the sample let me know.

    Thanks in advance for any advice!!!

    Jamie
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    For the average, you don't need to know the row numbers. You can use the following array formula (confirm with Ctrl+Shift+Enter):

    =AVERAGE(IF((A7:A14>=B3)*(A7:A14<=C3),B7:B14))

  3. #3
    New Lounger
    Join Date
    Aug 2009
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='799591' date='23-Oct-2009 15:45']For the average, you don't need to know the row numbers. You can use the following array formula (confirm with Ctrl+Shift+Enter):

    =AVERAGE(IF((A7:A14>=B3)*(A7:A14<=C3),B7:B14))[/quote]

    Hi Hans,

    Thanks you for the response. When I used the forumla I got a #VALUE error. It didn't like the A7:A14 part. I've attached a screenshot.

    Jamie
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Did you confirm the formula with Ctrl+Shift+Enter? It will return #VALUE if you confirm with Enter only.

    See the attached workbook: [attachment=86206:example.xls]
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Aug 2009
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='800091' date='27-Oct-2009 09:09']Did you confirm the formula with Ctrl+Shift+Enter? It will return #VALUE if you confirm with Enter only.

    See the attached workbook: [attachment=86206:example.xls][/quote]

    Hi Hans,

    I saw in your original post I should use Ctrl+Shift+Enter, but I didn't undertand what you meant having never done that before. I tried hitting that combination, but I didn't have my cursor in the formula bar when I tried. This time I figured it out. The formula works exactly as I wanted. You are a genius!

    Thanks!

    Jamie

Posting Permissions

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