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

[Chris]

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)

 

[Chris]

please take note of the changes.

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

 

[JMB]

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 D1
=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]

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.

 

[Chris]

Btw, will this formula work if I my data is less than 2??? Will it still
average?? Tnx.

 

[JMB]

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]

WoW!!!

Thanks so much, I am really starting to enjoy Excel......

 

[Chris]

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]

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),MMULT(--(TRANSPOSE(COLUMN(Sheet2!$B$1:$F$1))>=COLUMN(Sheet2!$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((Sheet2!$B$1:$F$1=A1)*(COLUMN(Sheet2!$B$1:$F$1)<=SMALL(IF(Sheet2!$B$1:$F$1=A1,COLUMN(Sheet2!$B$1:$F$1)),MIN(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]

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.