Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Averaging values from one column based on value in another column

0

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! 

excel
MS Office Version: Office 2011 Version 1477
posted by: torchburnsbrighter (rep: 2) on Mon Sep 17, 2018 at 11:44 am
Answer
Discuss

Answers

0
Selected Answer

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

=AVERAGEIF($C$2:$C$20000,$C2,$D$2:$D$20000)

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.

posted by: Variatus (rep: 4889) on Mon Sep 17, 2018 at 8:26 pm
Discuss


Answer the Question

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