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 dont 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!
