I'm bamboozled!...and would welcome assistance from someone with greater
understanding of nesting functions than I. I've got the first bit, but can't
work out the next part:
Part A: If the value in cell M24 is found in range A2:a500, and if the item
code in range J2:J500 commences with "A", then sum the $values in range
I2:I500
Part B: Return an average based on the values in range I2:I500 (which meet
the above criteria) multiplied by the value in P24.

Try this array formula** :

=AVERAGE(IF(A2:A500=M24,IF(LEFT(J2:J500)="A",IF(IS NUMBER(I2:I500),I2:I500))))*P24

--
Biff
Microsoft Excel MVP


"Twishlist" wrote in message
...
I'm bamboozled!...and would welcome assistance from someone with greater
understanding of nesting functions than I. I've got the first bit, but
can't
work out the next part:
Part A: If the value in cell M24 is found in range A2:a500, and if the
item
code in range J2:J500 commences with "A", then sum the $values in range
I2:I500
Part B: Return an average based on the values in range I2:I500 (which meet
the above criteria) multiplied by the value in P24.

thanks for your response. Working on your formula, I've done this in Q25:
=AVERAGE(IF(A2:A1167=M24,IF(LEFT(K2:K1167)="c",IF( ISNUMBER(J2:J1167),J2:J1167))))*Q24
which has returned the total $value of the occurences, rather than the
average of the total $value. In Q26, underneath, I've done this:
=AVERAGE(Q25)/Q24
Question: It's possible K2:K1167 contains more than item code which should
be collected. If I adjust the "c" to display "c","n", it returns a zero
$value.


"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF(A2:A500=M24,IF(LEFT(J2:J500)="A",IF(IS NUMBER(I2:I500),I2:I500))))*P24

--
Biff
Microsoft Excel MVP


"Twishlist" wrote in message
...
I'm bamboozled!...and would welcome assistance from someone with greater
understanding of nesting functions than I. I've got the first bit, but
can't
work out the next part:
Part A: If the value in cell M24 is found in range A2:a500, and if the
item
code in range J2:J500 commences with "A", then sum the $values in range
I2:I500
Part B: Return an average based on the values in range I2:I500 (which meet
the above criteria) multiplied by the value in P24.

Take a look at this screencap:

http://img159.imageshack.us/img159/3...erageifek6.jpg

I have no idea what your actual data is but the formula works as shown in
that screencap. Did you enter the formula as an array? I see in my other
reply I forgot to include my array formula reminder:

Try this array formula** :

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Twishlist" wrote in message
...
thanks for your response. Working on your formula, I've done this in Q25:
=AVERAGE(IF(A2:A1167=M24,IF(LEFT(K2:K1167)="c",IF( ISNUMBER(J2:J1167),J2:J1167))))*Q24
which has returned the total $value of the occurences, rather than the
average of the total $value. In Q26, underneath, I've done this:
=AVERAGE(Q25)/Q24
Question: It's possible K2:K1167 contains more than item code which
should
be collected. If I adjust the "c" to display "c","n", it returns a zero
$value.


"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF(A2:A500=M24,IF(LEFT(J2:J500)="A",IF(IS NUMBER(I2:I500),I2:I500))))*P24

--
Biff
Microsoft Excel MVP


"Twishlist" wrote in message
...
I'm bamboozled!...and would welcome assistance from someone with
greater
understanding of nesting functions than I. I've got the first bit, but
can't
work out the next part:
Part A: If the value in cell M24 is found in range A2:a500, and if the
item
code in range J2:J500 commences with "A", then sum the $values in range
I2:I500
Part B: Return an average based on the values in range I2:I500 (which
meet
the above criteria) multiplied by the value in P24.