RE: sumif and average with limits... pls help....

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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(C10>0,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??
.

Relevant Pages

  • RE: sumif and average with limits... pls help....
    ... Second sheet ... C10 to get the average ... You could move your criteria to other cells to make it more flexible. ... "Chris" wrote: ...
    (microsoft.public.excel.misc)
  • RE: sumif and average with limits... pls help....
    ... "Chris" wrote: ... Second sheet ... C10 to get the average ... You could move your criteria to other cells to make it more flexible. ...
    (microsoft.public.excel.misc)
  • RE: sumif and average with limits... pls help....
    ... "JMB" wrote: ... C10 to get the average ... You could move your criteria to other cells to make it more flexible. ... "Chris" wrote: ...
    (microsoft.public.excel.misc)
  • RE: excel formulas
    ... converts a number from euros to a euro member ... Counts the cells that contain numbers in a database ... Specifies a logical test to perform ... Looks in the top row of an array and returns the value of the indicated cell ...
    (microsoft.public.excel.misc)
  • Re: excel formulas
    ... member currency, or converts a number from one euro member currency ... Counts the cells that contain numbers in a database ... Specifies a logical test to perform ... Looks in the top row of an array and returns the value of the ...
    (microsoft.public.excel.misc)