Average IF with calculation

[Chris]

I have this problem:

IF O1 = "yes" then j1-p1+m1
but I need to do this from row1:row120 and then get the averages of
row1:row120

Can anyone help me please?

 

[Chip Pearson]

You can use an array formula to do this. Enter the following formula:

=AVERAGE(IF(O1:O20="yes",J1:J20-P120+M1:M20,FALSE))

Since this is an array formula, you must press CTRL SHIFT ENTER rather than
just ENTER when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula in the formula bar
enclosed in curly braces { }. See www.cpearson.com/Excel/ArrayFormulas.aspx
for much more info about array formulas.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Tyro]

You stated half. If O1 = "yes" but what if it doesn't?

Tyro