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.

"carl" <(E-Mail Removed)> wrote:
> 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.

On Jan 31, 2:41*am, "joeu2004" <joeu2...@foo.bar> wrote:
> "carl" <cmieda...@msn.com> wrote:
> > 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 ?

"carl" <(E-Mail Removed)> wrote:
> 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.