# If, then, function statement

#### M83ERP

##### New Member
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!

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### lenze

##### Legend
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

##### New Member
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

##### New Member
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

##### MrExcel MVP

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

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

#### M83ERP

##### New Member
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)

#### M83ERP

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

Replies
2
Views
145
Replies
7
Views
90
Replies
7
Views
55
Replies
1
Views
72
Replies
5
Views
230

1,128,207
Messages
5,629,294
Members
416,384
Latest member
frsamiee

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back