Dragging down formulas so that it goes to the next relevant case

[helzbelz1984]

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 Av 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. 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 :-)

[Glenn Kennedy]

Hi there... and welcome to the Excel Forum. Nope. I can't visualise how your data are laid out. Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

Click on GO ADVANCED and use the paperclip icon to open the upload window.

The paperclip icon

[newdoverman]

This small example might help:
With participants listed from B2:B15 and their values in C2:C15, enter this in D2 and fill down:

=IF(COUNTIF($B$2:B2,B2)=1,AVERAGE(IF($B$2:$B$15=B2,$C$2:$C$15)),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

	B	C	D	E
2	Participant1	4	6	=IF(COUNTIF($B$2:B2,B2)=1,AVERAGE(IF($B$2:$B$15=B2,$C$2:$C$15)),"")
3	Participant1	6
4	Participant1	5
5	Participant1	8
6	Participant1	7
7	Participant2	1	2
8	Participant2	2
9	Participant2	3
10	Participant3	4	4
11	Participant3	5
12	Participant3	6
13	Participant3	4
14	Participant3	3
15	Participant3	2

[helzbelz1984]

That's great, thank you for your response. However, I also have another column titled 'direction' and I only want to calculate the average of the values which correspond to the "left". Do you know how I can incorporate this into the formula?

My spreadsheet looks like this:

A B C
Participant Side Duration
Participant_1 Left 0.23
Participant_1 Right 0.45


Thanks :-)

[Glenn Kennedy]

Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

Click on GO ADVANCED and use the paperclip icon to open the upload window.

The paperclip icon

[helzbelz1984]

Also, this seems to work for the first participant, but when I drag down it doesn't give me any values for the next participants - it just says #VALUE!