Finding a Row Number

Topic

New Reply

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

Reply | Quote

Replies

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))

Reply | Quote

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))

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

Reply | Quote

Did you confirm the formula with Ctrl+Shift+Enter? It will return #VALUE if you confirm with Enter only.

See the attached workbook:

Reply | Quote

Did you confirm the formula with Ctrl+Shift+Enter? It will return #VALUE if you confirm with Enter only.

See the attached workbook:

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

Reply | Quote