Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Averages with Multiple Columns
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Averages with Multiple Columns
Why not make a pivot table?
Lots of info he 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/2...le-parameters/ best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "LP" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Averages with Multiple Columns
On Jun 12, 3:18*pm, "Bernard Liengme"
wrote: Why not make a pivot table? Lots of info he http://www.cpearson.com/excel/pivots...pivottable-par... best wishes -- Bernard V Liengme Microsoft Excel MVPhttp://people.stfx.ca/bliengme remove caps from email "LP" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Averages with Multiple Columns
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 "LP" wrote in message ... On Jun 12, 3:18 pm, "Bernard Liengme" wrote: Why not make a pivot table? Lots of info he http://www.cpearson.com/excel/pivots...pivottable-par... best wishes -- Bernard V Liengme Microsoft Excel MVPhttp://people.stfx.ca/bliengme remove caps from email "LP" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Average with Multiple Columns | Excel Discussion (Misc queries) | |||
Conditional Result based on Multiple Columns | Excel Worksheet Functions | |||
Conditional averages | Excel Worksheet Functions | |||
How do I apply conditional formulas across multiple columns? | Excel Worksheet Functions | |||
Conditional Averages | Excel Discussion (Misc queries) |