| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif and average with limits... pls help....
I need to average a group of data with a criteria but only up to a certain
number. Sample: CD AG CD FG CD FG AG 50 20 10 30 60 40 80 I need to average the data that is under "CD" but ONLY the first 2..(50 and 10). How can I set the limit?? (btw, I will be using Hlookup to get the "CD" criteria) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif and average with limits... pls help....
please take note of the changes.
"Chris" wrote: I need to average a group of data with a criteria but only up to a certain number. Sample: CD AG CD FG CD FG AG Sales 50 20 10 30 60 40 80 Expense 30 10 60 20 70 90 50 I need to average the data that is under "CD" and only those in the 2nd row(expense) but ONLY the first 2..(30 and 60). How can I set the limit?? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif and average with limits... pls help....
If your table is in A1:H3, you could enter this in C10
=MIN(2,COUNTIF(B1:H1,"CD")) and this, array entered using Ctrl+Shift+Enter, in D10 =SMALL(IF(TRANSPOSE(B1:H1="CD"),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD"))))),C10)/C10 although there could be a better way "Chris" wrote: please take note of the changes. "Chris" wrote: I need to average a group of data with a criteria but only up to a certain number. Sample: CD AG CD FG CD FG AG Sales 50 20 10 30 60 40 80 Expense 30 10 60 20 70 90 50 I need to average the data that is under "CD" and only those in the 2nd row(expense) but ONLY the first 2..(30 and 60). How can I set the limit?? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif and average with limits... pls help....
tnx JMB,
can u explain to me how the formula works so that next time I can do it on my own. Tnx a lot. "JMB" wrote: If your table is in A1:H3, you could enter this in C10 =MIN(2,COUNTIF(B1:H1,"CD")) and this, array entered using Ctrl+Shift+Enter, in D10 =SMALL(IF(TRANSPOSE(B1:H1="CD"),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD"))))),C10)/C10 although there could be a better way "Chris" wrote: please take note of the changes. "Chris" wrote: I need to average a group of data with a criteria but only up to a certain number. Sample: CD AG CD FG CD FG AG Sales 50 20 10 30 60 40 80 Expense 30 10 60 20 70 90 50 I need to average the data that is under "CD" and only those in the 2nd row(expense) but ONLY the first 2..(30 and 60). How can I set the limit?? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif and average with limits... pls help....
Btw, will this formula work if I my data is less than 2??? Will it still
average?? Tnx. "Chris" wrote: tnx JMB, can u explain to me how the formula works so that next time I can do it on my own. Tnx a lot. "JMB" wrote: If your table is in A1:H3, you could enter this in C10 =MIN(2,COUNTIF(B1:H1,"CD")) and this, array entered using Ctrl+Shift+Enter, in D10 =SMALL(IF(TRANSPOSE(B1:H1="CD"),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD"))))),C10)/C10 although there could be a better way "Chris" wrote: please take note of the changes. "Chris" wrote: I need to average a group of data with a criteria but only up to a certain number. Sample: CD AG CD FG CD FG AG Sales 50 20 10 30 60 40 80 Expense 30 10 60 20 70 90 50 I need to average the data that is under "CD" and only those in the 2nd row(expense) but ONLY the first 2..(30 and 60). How can I set the limit?? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif and average with limits... pls help....
In order to see what is happening (and given the assumption that your sample
table is in A1:H3 with labels in row 1 and column A), select a block of cells that is 7 rows by 1 column. Then enter this (which I picked up from one of Harlan Groves posts) in the formula bar and hit Ctrl+Shift+Enter =MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)))) This returns a running subtotal of your expense data. 30 40 100 120 190 280 330 By multiplying B3:H3 by (B1:H1="CD"), I get a running subtotal that only includes expense items for "CD". This is due to the fact that excel stores TRUE as 1 and FALSE as 0. So multiplying B3:H3 by an array of 1's (for CD) and 0's (other values) causes the items that don't fit your criteria to drop out: =MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD")))) returns 30 30 90 90 160 160 160 The IF function eliminates duplicated values by testing B1:H1 for "CD" and returning the corresponding value from the array returned by MMULT, otherwise it returns FALSE =IF(TRANSPOSE(B1:H1="CD"),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD"))))) returns 30 FALSE 90 FALSE 160 FALSE FALSE Then I used the Small function to return the second smallest number in the above array (small ignores FALSE) =SMALL(IF(TRANSPOSE(B1:H1="CD"),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD"))))),C10) returns 90 Instead of hardcoding "2" in the Small function, I reference cell C10 which contains =MIN(2,COUNTIF(B1:H1,"CD")) If there are fewer than 2 occurences of CD in B1:H1, Min returns the actual count. Then divide the subtotal figure returned by Small by the number in C10 to get the average =SMALL(IF(TRANSPOSE(B1:H1="CD"),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD"))))),C10)/C10 which gives you 90/2 = 45. You could move your criteria to other cells to make it more flexible. You could use A10 and B10 as input cells so that you can easily modify your criteria without having to edit the formula every time: A10 = CD B10 = 2 C10 = MIN(B10,COUNTIF(B1:H1,A10)) D10 = IF(C100,SMALL(IF(TRANSPOSE(B1:H1=A10),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1=A10))))),C10)/C10,0) adding a check to ensure C10 is greater than 0, otherwise you'll get an error. "Chris" wrote: Btw, will this formula work if I my data is less than 2??? Will it still average?? Tnx. "Chris" wrote: tnx JMB, can u explain to me how the formula works so that next time I can do it on my own. Tnx a lot. "JMB" wrote: If your table is in A1:H3, you could enter this in C10 =MIN(2,COUNTIF(B1:H1,"CD")) and this, array entered using Ctrl+Shift+Enter, in D10 =SMALL(IF(TRANSPOSE(B1:H1="CD"),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD"))))),C10)/C10 although there could be a better way "Chris" wrote: please take note of the changes. "Chris" wrote: I need to average a group of data with a criteria but only up to a certain number. Sample: CD AG CD FG CD FG AG Sales 50 20 10 30 60 40 80 Expense 30 10 60 20 70 90 50 I need to average the data that is under "CD" and only those in the 2nd row(expense) but ONLY the first 2..(30 and 60). How can I set the limit?? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif and average with limits... pls help....
WoW!!!
Thanks so much, I am really starting to enjoy Excel...... :) "JMB" wrote: In order to see what is happening (and given the assumption that your sample table is in A1:H3 with labels in row 1 and column A), select a block of cells that is 7 rows by 1 column. Then enter this (which I picked up from one of Harlan Groves posts) in the formula bar and hit Ctrl+Shift+Enter =MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)))) This returns a running subtotal of your expense data. 30 40 100 120 190 280 330 By multiplying B3:H3 by (B1:H1="CD"), I get a running subtotal that only includes expense items for "CD". This is due to the fact that excel stores TRUE as 1 and FALSE as 0. So multiplying B3:H3 by an array of 1's (for CD) and 0's (other values) causes the items that don't fit your criteria to drop out: =MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD")))) returns 30 30 90 90 160 160 160 The IF function eliminates duplicated values by testing B1:H1 for "CD" and returning the corresponding value from the array returned by MMULT, otherwise it returns FALSE =IF(TRANSPOSE(B1:H1="CD"),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD"))))) returns 30 FALSE 90 FALSE 160 FALSE FALSE Then I used the Small function to return the second smallest number in the above array (small ignores FALSE) =SMALL(IF(TRANSPOSE(B1:H1="CD"),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD"))))),C10) returns 90 Instead of hardcoding "2" in the Small function, I reference cell C10 which contains =MIN(2,COUNTIF(B1:H1,"CD")) If there are fewer than 2 occurences of CD in B1:H1, Min returns the actual count. Then divide the subtotal figure returned by Small by the number in C10 to get the average =SMALL(IF(TRANSPOSE(B1:H1="CD"),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD"))))),C10)/C10 which gives you 90/2 = 45. You could move your criteria to other cells to make it more flexible. You could use A10 and B10 as input cells so that you can easily modify your criteria without having to edit the formula every time: A10 = CD B10 = 2 C10 = MIN(B10,COUNTIF(B1:H1,A10)) D10 = IF(C100,SMALL(IF(TRANSPOSE(B1:H1=A10),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1=A10))))),C10)/C10,0) adding a check to ensure C10 is greater than 0, otherwise you'll get an error. "Chris" wrote: Btw, will this formula work if I my data is less than 2??? Will it still average?? Tnx. "Chris" wrote: tnx JMB, can u explain to me how the formula works so that next time I can do it on my own. Tnx a lot. "JMB" wrote: If your table is in A1:H3, you could enter this in C10 =MIN(2,COUNTIF(B1:H1,"CD")) and this, array entered using Ctrl+Shift+Enter, in D10 =SMALL(IF(TRANSPOSE(B1:H1="CD"),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD"))))),C10)/C10 although there could be a better way "Chris" wrote: please take note of the changes. "Chris" wrote: I need to average a group of data with a criteria but only up to a certain number. Sample: CD AG CD FG CD FG AG Sales 50 20 10 30 60 40 80 Expense 30 10 60 20 70 90 50 I need to average the data that is under "CD" and only those in the 2nd row(expense) but ONLY the first 2..(30 and 60). How can I set the limit?? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif and average with limits... pls help....
I need more help....
I tried editign the formula to what im doing but it gave me iether a Value error or NUm error. Let me be specific with my chart. First sheet 123 456 239 958 Second sheet A B C D E F 1 456 239 958 239 239 2 QE A 10 50 80 70 20 3 QE B 40 90 10 50 50 4 AVE 25 70 45 60 35 What I need is to get the average (row 4) of the first 2 data but the source reference is in sheet one. So i need a formula that averages the first 2 data but it needs to lookup the source reference in sheet one. Thanks again. "JMB" wrote: In order to see what is happening (and given the assumption that your sample table is in A1:H3 with labels in row 1 and column A), select a block of cells that is 7 rows by 1 column. Then enter this (which I picked up from one of Harlan Groves posts) in the formula bar and hit Ctrl+Shift+Enter =MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)))) This returns a running subtotal of your expense data. 30 40 100 120 190 280 330 By multiplying B3:H3 by (B1:H1="CD"), I get a running subtotal that only includes expense items for "CD". This is due to the fact that excel stores TRUE as 1 and FALSE as 0. So multiplying B3:H3 by an array of 1's (for CD) and 0's (other values) causes the items that don't fit your criteria to drop out: =MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD")))) returns 30 30 90 90 160 160 160 The IF function eliminates duplicated values by testing B1:H1 for "CD" and returning the corresponding value from the array returned by MMULT, otherwise it returns FALSE =IF(TRANSPOSE(B1:H1="CD"),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD"))))) returns 30 FALSE 90 FALSE 160 FALSE FALSE Then I used the Small function to return the second smallest number in the above array (small ignores FALSE) =SMALL(IF(TRANSPOSE(B1:H1="CD"),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD"))))),C10) returns 90 Instead of hardcoding "2" in the Small function, I reference cell C10 which contains =MIN(2,COUNTIF(B1:H1,"CD")) If there are fewer than 2 occurences of CD in B1:H1, Min returns the actual count. Then divide the subtotal figure returned by Small by the number in C10 to get the average =SMALL(IF(TRANSPOSE(B1:H1="CD"),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD"))))),C10)/C10 which gives you 90/2 = 45. You could move your criteria to other cells to make it more flexible. You could use A10 and B10 as input cells so that you can easily modify your criteria without having to edit the formula every time: A10 = CD B10 = 2 C10 = MIN(B10,COUNTIF(B1:H1,A10)) D10 = IF(C100,SMALL(IF(TRANSPOSE(B1:H1=A10),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1=A10))))),C10)/C10,0) adding a check to ensure C10 is greater than 0, otherwise you'll get an error. "Chris" wrote: Btw, will this formula work if I my data is less than 2??? Will it still average?? Tnx. "Chris" wrote: tnx JMB, can u explain to me how the formula works so that next time I can do it on my own. Tnx a lot. "JMB" wrote: If your table is in A1:H3, you could enter this in C10 =MIN(2,COUNTIF(B1:H1,"CD")) and this, array entered using Ctrl+Shift+Enter, in D10 =SMALL(IF(TRANSPOSE(B1:H1="CD"),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD"))))),C10)/C10 although there could be a better way "Chris" wrote: please take note of the changes. "Chris" wrote: I need to average a group of data with a criteria but only up to a certain number. Sample: CD AG CD FG CD FG AG Sales 50 20 10 30 60 40 80 Expense 30 10 60 20 70 90 50 I need to average the data that is under "CD" and only those in the 2nd row(expense) but ONLY the first 2..(30 and 60). How can I set the limit?? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif and average with limits... pls help....
So for each item on Sheet1 you want the average of the first two values in
row 4? If your source is in Sheet1!A1:A4, your data is in Sheet2!A1:F4, you should be able to enter this in Sheet1!B1 =MIN(2,COUNTIF(Sheet2!$B$1:$F$1,A1)) and this in Sheet1!C1 (array entered) = IF(B1,SMALL(IF(TRANSPOSE(Sheet2!$B$1:$F$1=A1),MMUL T(--(TRANSPOSE(COLUMN(Sheet2!$B$1:$F$1))=COLUMN(Sheet 2!$B$1:$F$1)),--(TRANSPOSE((Sheet2!$B$4:$F$4)*(Sheet2!$B$1:$F$1=A1 ))))),B1)/B1,0) and copy the formulas down. Another alternative that is slightly smaller without a helper cell (given the same assumptions about the location of the data, is to enter this in Sheet1!B1 and copy down =IF(COUNTIF(Sheet2!$B$1:$F$1,A1),AVERAGE(IF((Sheet 2!$B$1:$F$1=A1)*(COLUMN(Sheet2!$B$1:$F$1)<=SMALL(I F(Sheet2!$B$1:$F$1=A1,COLUMN(Sheet2!$B$1:$F$1)),MI N(2,COUNTIF(Sheet2!$B$1:$F$1,A1)))),Sheet2!$B$4:$F $4)),0) If that is not it, please provide more details and post what you have attempted. "Chris" wrote: I need more help.... I tried editign the formula to what im doing but it gave me iether a Value error or NUm error. Let me be specific with my chart. First sheet 123 456 239 958 Second sheet A B C D E F 1 456 239 958 239 239 2 QE A 10 50 80 70 20 3 QE B 40 90 10 50 50 4 AVE 25 70 45 60 35 What I need is to get the average (row 4) of the first 2 data but the source reference is in sheet one. So i need a formula that averages the first 2 data but it needs to lookup the source reference in sheet one. Thanks again. "JMB" wrote: In order to see what is happening (and given the assumption that your sample table is in A1:H3 with labels in row 1 and column A), select a block of cells that is 7 rows by 1 column. Then enter this (which I picked up from one of Harlan Groves posts) in the formula bar and hit Ctrl+Shift+Enter =MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)))) This returns a running subtotal of your expense data. 30 40 100 120 190 280 330 By multiplying B3:H3 by (B1:H1="CD"), I get a running subtotal that only includes expense items for "CD". This is due to the fact that excel stores TRUE as 1 and FALSE as 0. So multiplying B3:H3 by an array of 1's (for CD) and 0's (other values) causes the items that don't fit your criteria to drop out: =MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD")))) returns 30 30 90 90 160 160 160 The IF function eliminates duplicated values by testing B1:H1 for "CD" and returning the corresponding value from the array returned by MMULT, otherwise it returns FALSE =IF(TRANSPOSE(B1:H1="CD"),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD"))))) returns 30 FALSE 90 FALSE 160 FALSE FALSE Then I used the Small function to return the second smallest number in the above array (small ignores FALSE) =SMALL(IF(TRANSPOSE(B1:H1="CD"),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD"))))),C10) returns 90 Instead of hardcoding "2" in the Small function, I reference cell C10 which contains =MIN(2,COUNTIF(B1:H1,"CD")) If there are fewer than 2 occurences of CD in B1:H1, Min returns the actual count. Then divide the subtotal figure returned by Small by the number in C10 to get the average =SMALL(IF(TRANSPOSE(B1:H1="CD"),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD"))))),C10)/C10 which gives you 90/2 = 45. You could move your criteria to other cells to make it more flexible. You could use A10 and B10 as input cells so that you can easily modify your criteria without having to edit the formula every time: A10 = CD B10 = 2 C10 = MIN(B10,COUNTIF(B1:H1,A10)) D10 = IF(C100,SMALL(IF(TRANSPOSE(B1:H1=A10),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1=A10))))),C10)/C10,0) adding a check to ensure C10 is greater than 0, otherwise you'll get an error. "Chris" wrote: Btw, will this formula work if I my data is less than 2??? Will it still average?? Tnx. "Chris" wrote: tnx JMB, can u explain to me how the formula works so that next time I can do it on my own. Tnx a lot. "JMB" wrote: If your table is in A1:H3, you could enter this in C10 =MIN(2,COUNTIF(B1:H1,"CD")) and this, array entered using Ctrl+Shift+Enter, in D10 =SMALL(IF(TRANSPOSE(B1:H1="CD"),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD"))))),C10)/C10 although there could be a better way "Chris" wrote: please take note of the changes. "Chris" wrote: I need to average a group of data with a criteria but only up to a certain number. Sample: CD AG CD FG CD FG AG Sales 50 20 10 30 60 40 80 Expense 30 10 60 20 70 90 50 I need to average the data that is under "CD" and only those in the 2nd row(expense) but ONLY the first 2..(30 and 60). How can I set the limit?? |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumif and average with limits... pls help....
My apologies for the late reply but this was PERFRECT!!!!
Thanks so much for all the help and expect more questions from me :) Again, TY. "JMB" wrote: So for each item on Sheet1 you want the average of the first two values in row 4? If your source is in Sheet1!A1:A4, your data is in Sheet2!A1:F4, you should be able to enter this in Sheet1!B1 =MIN(2,COUNTIF(Sheet2!$B$1:$F$1,A1)) and this in Sheet1!C1 (array entered) = IF(B1,SMALL(IF(TRANSPOSE(Sheet2!$B$1:$F$1=A1),MMUL T(--(TRANSPOSE(COLUMN(Sheet2!$B$1:$F$1))=COLUMN(Sheet 2!$B$1:$F$1)),--(TRANSPOSE((Sheet2!$B$4:$F$4)*(Sheet2!$B$1:$F$1=A1 ))))),B1)/B1,0) and copy the formulas down. Another alternative that is slightly smaller without a helper cell (given the same assumptions about the location of the data, is to enter this in Sheet1!B1 and copy down =IF(COUNTIF(Sheet2!$B$1:$F$1,A1),AVERAGE(IF((Sheet 2!$B$1:$F$1=A1)*(COLUMN(Sheet2!$B$1:$F$1)<=SMALL(I F(Sheet2!$B$1:$F$1=A1,COLUMN(Sheet2!$B$1:$F$1)),MI N(2,COUNTIF(Sheet2!$B$1:$F$1,A1)))),Sheet2!$B$4:$F $4)),0) If that is not it, please provide more details and post what you have attempted. "Chris" wrote: I need more help.... I tried editign the formula to what im doing but it gave me iether a Value error or NUm error. Let me be specific with my chart. First sheet 123 456 239 958 Second sheet A B C D E F 1 456 239 958 239 239 2 QE A 10 50 80 70 20 3 QE B 40 90 10 50 50 4 AVE 25 70 45 60 35 What I need is to get the average (row 4) of the first 2 data but the source reference is in sheet one. So i need a formula that averages the first 2 data but it needs to lookup the source reference in sheet one. Thanks again. "JMB" wrote: In order to see what is happening (and given the assumption that your sample table is in A1:H3 with labels in row 1 and column A), select a block of cells that is 7 rows by 1 column. Then enter this (which I picked up from one of Harlan Groves posts) in the formula bar and hit Ctrl+Shift+Enter =MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)))) This returns a running subtotal of your expense data. 30 40 100 120 190 280 330 By multiplying B3:H3 by (B1:H1="CD"), I get a running subtotal that only includes expense items for "CD". This is due to the fact that excel stores TRUE as 1 and FALSE as 0. So multiplying B3:H3 by an array of 1's (for CD) and 0's (other values) causes the items that don't fit your criteria to drop out: =MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD")))) returns 30 30 90 90 160 160 160 The IF function eliminates duplicated values by testing B1:H1 for "CD" and returning the corresponding value from the array returned by MMULT, otherwise it returns FALSE =IF(TRANSPOSE(B1:H1="CD"),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD"))))) returns 30 FALSE 90 FALSE 160 FALSE FALSE Then I used the Small function to return the second smallest number in the above array (small ignores FALSE) =SMALL(IF(TRANSPOSE(B1:H1="CD"),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD"))))),C10) returns 90 Instead of hardcoding "2" in the Small function, I reference cell C10 which contains =MIN(2,COUNTIF(B1:H1,"CD")) If there are fewer than 2 occurences of CD in B1:H1, Min returns the actual count. Then divide the subtotal figure returned by Small by the number in C10 to get the average =SMALL(IF(TRANSPOSE(B1:H1="CD"),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD"))))),C10)/C10 which gives you 90/2 = 45. You could move your criteria to other cells to make it more flexible. You could use A10 and B10 as input cells so that you can easily modify your criteria without having to edit the formula every time: A10 = CD B10 = 2 C10 = MIN(B10,COUNTIF(B1:H1,A10)) D10 = IF(C100,SMALL(IF(TRANSPOSE(B1:H1=A10),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1=A10))))),C10)/C10,0) adding a check to ensure C10 is greater than 0, otherwise you'll get an error. "Chris" wrote: Btw, will this formula work if I my data is less than 2??? Will it still average?? Tnx. "Chris" wrote: tnx JMB, can u explain to me how the formula works so that next time I can do it on my own. Tnx a lot. "JMB" wrote: If your table is in A1:H3, you could enter this in C10 =MIN(2,COUNTIF(B1:H1,"CD")) and this, array entered using Ctrl+Shift+Enter, in D10 =SMALL(IF(TRANSPOSE(B1:H1="CD"),MMULT(--(TRANSPOSE(COLUMN(B1:H1))=COLUMN(B1:H1)),--(TRANSPOSE((B3:H3)*(B1:H1="CD"))))),C10)/C10 although there could be a better way "Chris" wrote: please take note of the changes. "Chris" wrote: I need to average a group of data with a criteria but only up to a certain number. Sample: CD AG CD FG CD FG AG Sales 50 20 10 30 60 40 80 Expense 30 10 60 20 70 90 50 I need to average the data that is under "CD" and only those in the 2nd row(expense) but ONLY the first 2..(30 and 60). How can I set the limit?? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Is there formula similar to SUMIF that will work for average | Excel Discussion (Misc queries) | |||
| SUMIF for an AVERAGE | Excel Worksheet Functions | |||
| Average/Sumif based on several columns | Excel Worksheet Functions | |||
| Average/Sumif based on several columns | Excel Worksheet Functions | |||
| Weighted average using SUMIF and/or SUMPRODUCT | Excel Discussion (Misc queries) | |||
Linear Mode
