Averaging values from one column based on value in another column


Hi, I'm trying to implement a lead/lag sort of function in Excel. I've attached a sample file.

If there is a value in column C (text), I want to pull x rows forward in Column E (pupil size) and average across the values in those rows until the next value in Column C (text) appears. For example, if there is a value in column C like 'PICSTIM_ 0', I want to average the values in Column E (and output the average) until the next value in column C appears like 'PICSTIM_ 1'. Is there a way to implement this programatically in Excel? Right now I've been doing it manually and it's a pain/takes awhile.

If I can clarify or provide further information let me know. Thanks for your help in advance! 



Selected Answer

The simplest way should be to use an AVERAGEIF() function.


This formula instructs Excel to average only those values in D2:D20000 where the value in C2:C20000 equals C2. In your workbook the result will be equal to D2 because the criterion PICSTIM_ 0 appears only once. As you enter this value in more rows the average will change accordingly.

This method gives you the desired result at the cost of writing the criterium in every line and then having it there. The frist problem can be solved with Copy/Paste. If you don't want to see the repeats use Conditional Formatting to hide the duplicates.

The advantage will be that you don't need to sort the criteria into consecutive rows. Let me know if this solves your problem.


Answer the Question

You must create an account to use the forum. Create an Account or Login