# Thread: Finding a Row Number

1. 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.

Jamie

2. 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. [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

4. 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]

5. [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
•