Dragging formulas

Question

Hi everyone,

I'm hoping someone on here might be able to help me. Basically, I've got a relatively simple excel formula in which I want to calculate the average of a given set of cells. So for instance, for participant_1 (below), I am using the formula =AVERAGEIF(E179:E238,"left",H179:H238) to provide me with the average duration when the stimulus is presented on the left hand side. 

Participant              Side      Duration(ms)

participant_1               right           0.208

participant_1                left              0.9

participant_1                right           0.32

participant_1                 left             0.67

This bit is working fine and I have devised two columns on the worksheet that look a bit like this:

Participant                      Average left  

participant_1.eyd               0.48745 

participant_2.eyd                  ??  

participant_3.eyd                   ??

What I am stuck on is how I can just drag my formula down so that it calculates the value for the next participant (participant_2.eyd), and participant_3 etc etc, all the way up to participant_94. At the moment, it would just move the formula down by one row whereas I need it to be moved down by lots of rows (until participant_2.eyd). I could just do it manually but I have 94 participants so it would be rather time consuming.

I hope this makes sense!

Thank you in advance for your help :-)

Answer 1

Let's Assume that your column E is AOIName and column F is TotalFixDur, that is why you have a formula like this.

AVERAGEIF(E179:E238,"left",H179:H238)

Let's say your below table has participant in column D

Participant              Side      Duration(ms)

participant_1               right           0.208

participant_1                left              0.9

participant_1                right           0.32

participant_1                 left             0.67

Now, let's take your table

Participant                      Average left  

participant_1.eyd               0.48745 

participant_2.eyd                  ??   

participant_3.eyd                   ??

Let's say, the column Participant is in column J and starts in row 1, hence participant_1.eyd will be in J2.

You will need to use following formula in this case

=AVERAGEIF(H179:H238,E179:E238,"left",D179:D238,J2)

This formula can be dragged down.

Answer 2

Hi,

Thank you for your replies! The thing is is that I'm not sure that I want to anchor the formula - I want it to drag down but, rather than just going to the next row, I want it to skip X number of rows so that the next average that it gives me corresponds to the next participant.

My spreadsheet looks like this (see below), and I would like to calculate the average "TotalFixDur" for each participant for values that ONLY correspond to the left (AOIName). The 'averageifs' function is helping for this however I then want to just drag the formula down so that it gives me the average "totalfixdur" for participant_10 etc etc. At the moment, when I drag the formula down, then it would just calculate the average values for cells C3 etc etc, but I need it to skip all these cells until the next participant (participant_10, in this case).

Hope that makes a bit more sense ! 

File	AOIName	TotalFixDur
participant_1.eyd	OUTSIDE	0.192
participant_1.eyd	right	0.208
participant_1.eyd	left	0.9
participant_1.eyd	OUTSIDE	0.183
participant_1.eyd	right	0.225
participant_1.eyd	left	0.725
participant_1.eyd	OUTSIDE	0.217
participant_10.eyd	OUTSIDE	0.217
participant_10.eyd	right	1.725
participant_10.eyd	left	0
participant_10.eyd	OUTSIDE	0.192
participant_10.eyd	right	1.75
participant_10.eyd	left	0

Answer 3

Hi,

Try the AVERAGEIFS() function to accommodate the other condition of participant.

Answer 4

Hi,

Not sure I fully understand but maybe something like this.

=AVERAGEIF($E$179:$E$238,A2,$H$179:$H$238)

Note that in the formula 2 of the ranges are anchored with the $ sign so they won't change when you drag down but A2 will change to a3 etc when dragged.