If, then, function statement

[M83ERP]

Hi - I'm new on the forum so pardon if this is a novice question - I'm wondering if there is a way to tweak the "if, then" statement in Excel to tell Excel to calculate the value in a corresponding column.

For instance, I would like to tell Excel: if a range of values in Column L = 1, then calculate the average for the corresponding range of values in Column G. Eventually, I would like to add more criteria to this statement, for instance: if a range of values in Column L = 1 and the corresponding range of values in Column H = 1, then calculate the average for the corresponding range of values in Column G.

Can somebody help me on this? Let me know if my question needs clarifying.
Thanks!

 

[lenze]

Hi and welcome to the board!!
Can you explain what you mean by
"if a range of values in Column L = 1, then calculate the average for the corresponding range of values in Column G". Do you want to Caluculate the AVERAGE for Column "G" values oly where Column "L" equals 1? Perhaps some sample data would be helpful.

lenze

lenze

 

[M83ERP]

Hi Lenze,

Thanks for responding. I'm not sure how to attach a sample data sheet, so I attached a screen shot of what my data looks like. Basically, I'm attempting to calculate the Accuracy and RT values for various Visual Targets Groups (1-6). Column A is the Accuracy column, so a 1 = Correct and 0 = Incorrect. Column B is the Reaction Time (RT) column in ms that it took a participant to respond. Column C denotes the various Target Groups.

I would like to calculate the Mean Accuracy (based on the group of values in Column A) that correspond to each one of those Visual Target Groups (1-6). (So for instance, I would like to average rows 2-8 in Column A to create the "Average Accuracy" for Target Group 1).

Then, eventually, I would like to calculate the Mean and Median RTs (based on the values in Column B) for each of those Visual Target Groups (in Column C) that has received a Correct Response (denoted in Column A). (So in this instance, I would like to average rows 2-5 in Column B for "Mean RT" for Target Group 1 because those are events in Target Group 1 that also received a "1" in Column A).

Does that make more sense or is that more convoluted? Thanks again for your help!

 

[M83ERP]

Hi,

Let me update this. I realize that I can do this through the "averageifs" function and now I am wondering if there is a comparable median function?

Would love to hear yes to this?

 

[Scott Huish]

=MEDIAN(IF(H1:H100=1,IF(L1:L100=1,G1:G100)))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

 

[M83ERP]

Hey Hot Pepper,

Thanks for the input, but the formula you suggested turns out zeroes for me? I attempted to adjust it to be an "if,and" statement, but that still resulted in zeroes.

=MEDIAN(IF(AND(C:C=2,A:A=1),B:B))

(This formula corresponds to the data image I uploaded previously)

More advice please?

 

[M83ERP]

Aha! Never mind, I just saw the control-shift-enter thing. Thanks! That works great!