Wednesday, April 17, 2013

yogi_Compute Average Of Days In Column C Where Part # And Month Of Date Are As Specified

Google Spreadsheet   Post  #1136
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 17, 2013
user MichaelGodfrey13 :(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/how-do-i/desktop/cXnLFqOi5hw)
Average Array multiple columns
The formula first looks at the part column looking for the letters EN. Next looks at the date column to find the month.
Finally and where my problem is in the Days column, averaging the days

My Current formula
=ArrayFormula(sum(iferror((left('Quotation Results'!A2:A,len(A1))=A1)*(month('Quotation Results'!I2:I)=1))))

A1=EN

Month =1 (Jan)

Part #                     Date          Days
EN12345              1/13/13           12
EE43247              1/4/13              2
MN78427              3/27/13            2
EN19087              1/23/13            4
ME98768              2/19/13            4
EN56399              3/22/13             5

The expected result should be 7, the average of 12, 4, and 5

Chrome Version 26.0.1410.64 m  on Win XP SP3 

Any ideas or examples, Thank you in advance for any help.
----------------------------------------------------------------------------------------------------------------
following is a solution to the problem


No comments:

Post a Comment