If/Then calculate the average

[LynnJ]

I'm trying to find a formula that will calculate the average number of days
it takes for a certain type of material to move through my department. Where
column A is the type of material on a book truck and column I is the number
of days it took for that book truck to move through the department.

If the cells in column A= MUSIC then average the cells of the same row in
column I.

I've been playing with the SUMIF and AVERAGE functions but haven't been able
to figure it out.

It didn't like =AVERAGE(IF(A2:A399,"MUSIC",I2:I399)) as an array formula.

Any help is greatly appreciated.

[Roger Govier]

Hi Lynn

I can't see whay that array formula wouldn't work, unless there is
something wrong with the data.

You could try
=SUMPRODUCT(--(A2:A399="MUSIC"),--(I2:I339))/COUNTIF(A2:A339,"MUSIC")


--
Regards

Roger Govier

LynnJ <LynnJ@discussions.microsoft.com> wrote
> I'm trying to find a formula that will calculate the average number
> of days it takes for a certain type of material to move through my
> department. Where column A is the type of material on a book truck
> and column I is the number of days it took for that book truck to
> move through the department. If the cells in column A= MUSIC then
> average the cells of the same row in column I.
> I've been playing with the SUMIF and AVERAGE functions but haven't
> been able to figure it out.
> It didn't like =AVERAGE(IF(A2:A399,"MUSIC",I2:I399)) as an array
> formula. Any help is greatly appreciated.

[Ron Coderre]

Try this:
=AVERAGE(IF(A2:A399="MUSIC",I2:I399))
Note: commit that array formula by holding down [Ctrl]+[Shift] and press
[Enter]


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"LynnJ" wrote:

> I'm trying to find a formula that will calculate the average number of days
> it takes for a certain type of material to move through my department. Where
> column A is the type of material on a book truck and column I is the number
> of days it took for that book truck to move through the department.
>
> If the cells in column A= MUSIC then average the cells of the same row in
> column I.
>
> I've been playing with the SUMIF and AVERAGE functions but haven't been able
> to figure it out.
>
> It didn't like =AVERAGE(IF(A2:A399,"MUSIC",I2:I399)) as an array formula.
>
> Any help is greatly appreciated.
>
>
>

[bj]

have you tried
=sumif(A2:A399,"MUSIC",I2:I399)/countif(A2:A399,"MUSIC")

"LynnJ" wrote:

> I'm trying to find a formula that will calculate the average number of days
> it takes for a certain type of material to move through my department. Where
> column A is the type of material on a book truck and column I is the number
> of days it took for that book truck to move through the department.
>
> If the cells in column A= MUSIC then average the cells of the same row in
> column I.
>
> I've been playing with the SUMIF and AVERAGE functions but haven't been able
> to figure it out.
>
> It didn't like =AVERAGE(IF(A2:A399,"MUSIC",I2:I399)) as an array formula.
>
> Any help is greatly appreciated.
>
>
>

[Duke Carey]

Just a slight issue with your formula:

=AVERAGE(IF(A2:A399="MUSIC",I2:I399,""))

Enter it as an array formula


"LynnJ" wrote:

> I'm trying to find a formula that will calculate the average number of days
> it takes for a certain type of material to move through my department. Where
> column A is the type of material on a book truck and column I is the number
> of days it took for that book truck to move through the department.
>
> If the cells in column A= MUSIC then average the cells of the same row in
> column I.
>
> I've been playing with the SUMIF and AVERAGE functions but haven't been able
> to figure it out.
>
> It didn't like =AVERAGE(IF(A2:A399,"MUSIC",I2:I399)) as an array formula.
>
> Any help is greatly appreciated.
>
>
>

[Roger Govier]

Luckily, others were wider awake than I was, and spotted the "," in
place of the "=" in your array formula.

--
Regards

Roger Govier


Roger Govier <roger@technologyNOSPAM4u.co.uk> wrote
> Hi Lynn
> I can't see whay that array formula wouldn't work, unless there is
> something wrong with the data.
> You could try
> =SUMPRODUCT(--(A2:A399="MUSIC"),--(I2:I339))/COUNTIF(A2:A339,"MUSIC")