Average If

[carl]

My data looks like this:

Price Bid Ask
1.91 1.9 1.97
1.91 1.9 1.97
0.50 0.49 0.54
0.50 0.49 1.8
0.55 0.52 0.58
0.55 0.52 0.58
0.22 0.21 0.22
0.22 0.21 0.22
3.10 3 3.15
3.10 3 3.15
1.89 1.89 1.94
1.89 1.89 1.94
1.40 1.38 1.4
1.40 1.38 1.4
0.74 0.7 0.74
0.74 0.7 0.74
0.65 0.6 0.7
0.65 0.6 0.7

I am looking for a formula that will produce this result:

Bid-Ask Spread For Price < 2 .13
Bid-Ask Spread for Price >=2 but < 5 .15


Thanks you in advance.

 

[joeu2004]

My data looks like this:
Price Bid Ask
1.91 1.9 1.97 [....]
I am looking for a formula that will produce this result:
Bid-Ask Spread For Price < 2 .13

Enter this array formula [*] (press ctrl+shift+Enter, not Enter):

=IF(COUNTIF(A2:A19,"<2")=0,0,AVERAGE(IF(A2:A19<2,ABS(B2:B19-C2:C19))))

Bid-Ask Spread for Price >=2 but < 5 .15

Enter this array formula [*] (press ctrl+shift+Enter, not Enter):

=IF(COUNTIF(A2:A19,">=2")=0,0,AVERAGE(IF(A2:A19>=2,ABS(B2:B19-C2:C19))))

This assumes that your price, bid and ask data are in rows 2 through 19 of
columns A, B and C respectively.


-----
[*] Enter an array formula by pressing ctrl+shift+Enter instead of Enter.
Excel will display an array formula surrounded by curly braces in the
Formula Bar, i.e. {=formula}. You cannot type the curly braces yourself.
If you make a mistake, select the cell, press F2 and edit, then press
ctrl+shift+Enter.

 

[carl]

My data looks like this:
Price Bid Ask
1.91 1.9 1.97 [....]
I am looking for a formula that will produce this result:
Bid-Ask Spread For Price < 2                 .13

Enter this array formula [*] (press ctrl+shift+Enter, not Enter):

=IF(COUNTIF(A2:A19,"<2")=0,0,AVERAGE(IF(A2:A19<2,ABS(B2:B19-C2:C19))))

Bid-Ask Spread for Price >=2 but < 5      .15

Enter this array formula [*] (press ctrl+shift+Enter, not Enter):

=IF(COUNTIF(A2:A19,">=2")=0,0,AVERAGE(IF(A2:A19>=2,ABS(B2:B19-C2:C19))))

This assumes that your price, bid and ask data are in rows 2 through 19 of
columns A, B and C respectively.

-----
[*] Enter an array formula by pressing ctrl+shift+Enter instead of Enter.
Excel will display an array formula surrounded by curly braces in the
Formula Bar, i.e. {=formula}.  You cannot type the curly braces yourself.
If you make a mistake, select the cell, press F2 and edit, then press
ctrl+shift+Enter.

Thanks.

For this formula

=IF(COUNTIF(A2:A19,">=2")=0,0,AVERAGE(IF(A2:A19>=2,ABS(B2:B19-
C2:C19))))

How would I modify so it does the calculation only when A2:A19 is
between 2 and 5 ?

 

[joeu2004]

For this formula
=IF(COUNTIF(A2:A19,">=2")=0,0,
AVERAGE(IF(A2:A19>=2,ABS(B2:B19-C2:C19))))
How would I modify so it does the calculation only when
A2:A19 is between 2 and 5 ?

First, for XL2007 and later, the original formula could simplified as
follows:

=IFERROR(AVERAGE(IF(A2:A19>=2,ABS(B2:B19-C2:C19))),0)

And for your follow-up question:

=IFERROR(AVERAGE(IF((2<=A2:A19)*(A2:A19<=5),ABS(B2:B19-C2:C19))),0)

The tests "between" inclusively, including 2 and 5. Change "<=" and/or "<="
appropriately if you do not mean to include one or the other end point or
both.

The multipication behaves like a pairwise "AND" in this context. We cannot
write AND(2<=A2:A19,A2:A19<=5) because the AND() function interprets the
arrays, not the IF() function.

In XL2003 and earlier, we can write:

=IF(COUNTIF(A2:A19,"<=5")-COUNTIF(A2:A19,"<2")=0,0,
AVERAGE(IF((2<=A2:A19)*(A2:A19<=5),ABS(B2:B19-C2:C19))))

That, too, tests "between" inclusively despite appearances due to the "<2".
To exclude 2, use "<=2". To exclude 5, use "<5". A little tricky.