Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average If
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average If
"carl" 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,A BS(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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average If
On Jan 31, 2:41*am, "joeu2004" wrote:
"carl" 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,A BS(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 ? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average If
"carl" 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A formula to AVERAGE IF but only average a set number of values | Excel Worksheet Functions | |||
Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
Weighed Average of a weiged average when there are blanks | Excel Discussion (Misc queries) | |||
how does one convert text to a formula "average(A:A)" to =average( | Excel Worksheet Functions |