If, then, function statement

M83ERP

New Member
Joined
Nov 18, 2009
Messages
5
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!
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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
Joined
Nov 18, 2009
Messages
5
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!
mail
 

M83ERP

New Member
Joined
Nov 18, 2009
Messages
5
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
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

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

M83ERP

New Member
Joined
Nov 18, 2009
Messages
5
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

New Member
Joined
Nov 18, 2009
Messages
5
Aha! Never mind, I just saw the control-shift-enter thing. Thanks! That works great!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,782
Members
415,927
Latest member
vedasinternational

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top