Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If/Then calculate the average
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If/Then calculate the average
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 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If/Then calculate the average
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If/Then calculate the average
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If/Then calculate the average
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If/Then calculate the average
Luckily, others were wider awake than I was, and spotted the "," in
place of the "=" in your array formula. -- Regards Roger Govier Roger Govier 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") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to calculate average from large dataset in several sheets? | Excel Worksheet Functions | |||
Calculate the average using the Lookup function or similar | Excel Discussion (Misc queries) | |||
calculate average in percentage coloumn with #DIV/! (ignore error | Excel Discussion (Misc queries) | |||
calculate average hours and minutes | Excel Worksheet Functions | |||
calculate average hours worked | Excel Worksheet Functions |