Conditional Averages with Multiple Columns

[LP]

In Sheet1, I have 13 columns total (A = Location, B – M are Jan -
Dec)

A B C D E F
Location Jan Feb Mar Apr May
Hawaii 5 8 12 22 45
Denver 10 58 41 44 10
Hawaii 12 58 77 88 99



In Sheet2, Cell A1, B1 and C1 are manual input month fields and they
are expected to change randomly. For instance, tomorrow, A1 might be
Oct instead of Feb.

A1 B1 C1
Feb Mar Apr


In sheet2, is it possible to have Excel take the average of value in
Sheet1 based on location and the month set in A1, B1 and C1 ? For
example, Sheet2 would look like this:

A1 B1 C1 D1
Location Feb Mar Apr
Hawaii 8.5 44.5 55
Denver 10 41 44


Any thoughts will be greatly appreciated.

Thanks.

LP

 

[Bernard Liengme]

Why not make a pivot table?
Lots of info here:

http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.techonthenet.com/excel/pivottbls/index.htm
http://www.dicks-blog.com/archives/2005/06/23/download-pivottable-parameters/
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

In Sheet1, I have 13 columns total (A = Location, B – M are Jan -
Dec)

A B C D E F
Location Jan Feb Mar Apr May
Hawaii 5 8 12 22 45
Denver 10 58 41 44 10
Hawaii 12 58 77 88 99



In Sheet2, Cell A1, B1 and C1 are manual input month fields and they
are expected to change randomly. For instance, tomorrow, A1 might be
Oct instead of Feb.

A1 B1 C1
Feb Mar Apr


In sheet2, is it possible to have Excel take the average of value in
Sheet1 based on location and the month set in A1, B1 and C1 ? For
example, Sheet2 would look like this:

A1 B1 C1 D1
Location Feb Mar Apr
Hawaii 8.5 44.5 55
Denver 10 41 44


Any thoughts will be greatly appreciated.

Thanks.

LP

 

[LP]

Why not make a pivot table?
Lots of info here:

http://www.cpearson.com/excel/pivot...m/archives/2005/06/23/download-pivottable-par...
best wishes
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email


In Sheet1, I have 13 columns total (A = Location,   B – M are Jan -
Dec)

A B C D E F
Location Jan Feb Mar Apr May
Hawaii 5 8 12 22 45
Denver 10 58 41 44 10
Hawaii 12 58 77 88 99

In Sheet2, Cell A1, B1 and C1 are manual input month fields and they
are expected to change randomly.  For instance, tomorrow, A1 might be
Oct instead of Feb.

A1 B1 C1
Feb Mar Apr

In sheet2, is it possible to have Excel take the average of value in
Sheet1 based on location and the month set in A1, B1 and C1 ?   For
example, Sheet2 would look like this:

A1                    B1          C1            D1
Location            Feb        Mar          Apr
Hawaii               8.5         44.5          55
Denver               10          41             44

Any thoughts will be greatly appreciated.

Thanks.

LP

Thanks..but Pivot is not an option at this time because it these
results will be linked to other charts and graphs. In addition, I find
that a lot of people forget to refresh tables which tends to cause a
lot of errors. Any other thoughts?

LP

 

[Bernard Liengme]

On Sheet1 I have your locations in column A and the range is called MyPlaces
In B1:M1 I have: Jan, Feb , Mar... Dec That range is called MyYear
In B2:M4 I have your numbers

On Sheet2
In A2:A3 I have Hawaii and Denver
In B1:M1 I have: Jan, Feb , Mar
In B2 I have the formula
=SUMPRODUCT(($A2=MyPlaces)*(B$1=MyYear)*RawData)
This is copied down and across, giving
A1 B1 C1 D1
Location Feb Mar Apr
Hawaii 66 89 110
Denver 58 41 44


Now if I could only workout what you were averaging!
Would you like me to send you a file?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Why not make a pivot table?
Lots of info here:

http://www.cpearson.com/excel/pivot...m/archives/2005/06/23/download-pivottable-par...
best wishes
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email


In Sheet1, I have 13 columns total (A = Location, B – M are Jan -
Dec)

A B C D E F
Location Jan Feb Mar Apr May
Hawaii 5 8 12 22 45
Denver 10 58 41 44 10
Hawaii 12 58 77 88 99

In Sheet2, Cell A1, B1 and C1 are manual input month fields and they
are expected to change randomly. For instance, tomorrow, A1 might be
Oct instead of Feb.

A1 B1 C1
Feb Mar Apr

In sheet2, is it possible to have Excel take the average of value in
Sheet1 based on location and the month set in A1, B1 and C1 ? For
example, Sheet2 would look like this:

A1 B1 C1 D1
Location Feb Mar Apr
Hawaii 8.5 44.5 55
Denver 10 41 44

Any thoughts will be greatly appreciated.

Thanks.

LP

Thanks..but Pivot is not an option at this time because it these
results will be linked to other charts and graphs. In addition, I find
that a lot of people forget to refresh tables which tends to cause a
lot of errors. Any other thoughts?

LP