MrExcel.com - Your One Stop for Excel Tips & Solutions

Average some values in a column based on a text string in another

Posted by Bob Frolichstein on February 05, 2002 12:14 AM

I have a spreadsheet that calculates a time interval. Each of the time intervals corresponds to one of 7 text strings in another column. How can I get the average time interval for each of the text strings. J5:J500 has time interval G5:G500 has one of 7 text strings.

Posted by Brian on February 05, 2002 1:44 AM

Formula arrays are your only man here.

Assuming your time intervals are in cells J5:J500, and this is what you want to average.

Also this is based on what information is in G5:G500.

I will also assume that the 7 time intervals are listed H1:H7. (They don’t have to be, you can just enter the text string itself)

This formula should do the trick.

=AVERAGE(IF($G$5:$G$500=H1,$J$5:$J$500))

As this is a formula array, do not press Enter. Press CTRL + SHIFT + ENTER after entering the formula.

Instead of =H1 here you can type =”text string” if the time intervals are not listed in the spreadsheet.

Posted by Bob Frolichstein on February 05, 2002 5:43 AM

Thanks!