Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highest 12-Month Period Average Over Range of Months
I need to know if a formula can be constructed that will return the highest
consecutive twelve-month average over a range of months. Example: Assume cells A1..A14 represent a single month and contain the following values. 1, 6, 3, 8, 2, 9, 12, 3, 6, 2, 6, 9, 9, 4 In this example, there are 3 consecutive twelve-month periods, A1..A12, A2..A13 and A3..A14. The average of A1..A12 = 5.58, A2..A13 = 6.25, and A3..A14 = 6.08. I want a formula that will return the highest average of the three, 6.25. Thanks in advance for any assistance. -Mark |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highest 12-Month Period Average Over Range of Months
Hi Mark
One way With your data in A1:A14 in B1 =AVERAGE(A1:A12) copy through B2:B14 In another cell =MAX(B1:B14) -- Regards Roger Govier "Mark T." <Mark wrote in message ... I need to know if a formula can be constructed that will return the highest consecutive twelve-month average over a range of months. Example: Assume cells A1..A14 represent a single month and contain the following values. 1, 6, 3, 8, 2, 9, 12, 3, 6, 2, 6, 9, 9, 4 In this example, there are 3 consecutive twelve-month periods, A1..A12, A2..A13 and A3..A14. The average of A1..A12 = 5.58, A2..A13 = 6.25, and A3..A14 = 6.08. I want a formula that will return the highest average of the three, 6.25. Thanks in advance for any assistance. -Mark |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highest 12-Month Period Average Over Range of Months
Roger,
Thanks for the suggestion. I really need to get it done in one cell with one formula though (no intermediary formulas below the data). Any suggestions on that? Is it possible? Thanks, -Mark "Roger Govier" wrote: Hi Mark One way With your data in A1:A14 in B1 =AVERAGE(A1:A12) copy through B2:B14 In another cell =MAX(B1:B14) -- Regards Roger Govier "Mark T." <Mark wrote in message ... I need to know if a formula can be constructed that will return the highest consecutive twelve-month average over a range of months. Example: Assume cells A1..A14 represent a single month and contain the following values. 1, 6, 3, 8, 2, 9, 12, 3, 6, 2, 6, 9, 9, 4 In this example, there are 3 consecutive twelve-month periods, A1..A12, A2..A13 and A3..A14. The average of A1..A12 = 5.58, A2..A13 = 6.25, and A3..A14 = 6.08. I want a formula that will return the highest average of the three, 6.25. Thanks in advance for any assistance. -Mark |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highest 12-Month Period Average Over Range of Months
Try this:
Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =MAX(SUBTOTAL(1,OFFSET(A1:A14,{1,2,3}-1,,12))) Biff "Mark T." <Mark wrote in message ... I need to know if a formula can be constructed that will return the highest consecutive twelve-month average over a range of months. Example: Assume cells A1..A14 represent a single month and contain the following values. 1, 6, 3, 8, 2, 9, 12, 3, 6, 2, 6, 9, 9, 4 In this example, there are 3 consecutive twelve-month periods, A1..A12, A2..A13 and A3..A14. The average of A1..A12 = 5.58, A2..A13 = 6.25, and A3..A14 = 6.08. I want a formula that will return the highest average of the three, 6.25. Thanks in advance for any assistance. -Mark |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highest 12-Month Period Average Over Range of Months
Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER): =MAX(SUBTOTAL(1,OFFSET(A1:A14,{1,2,3}-1,,12))) Actually, no need to array enter. Enter normally. When I tested I used the ROW function as the row offset argument which needed array entry. You could always use this: (but I have a feeling you really want more than just 3 ranges averaged which is why I posted the Subtotal version!) =MAX(AVERAGE(A1:A12),AVERAGE(A2:A13),AVERAGE(A3:A1 4)) Biff "T. Valko" wrote in message ... Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =MAX(SUBTOTAL(1,OFFSET(A1:A14,{1,2,3}-1,,12))) Biff "Mark T." <Mark wrote in message ... I need to know if a formula can be constructed that will return the highest consecutive twelve-month average over a range of months. Example: Assume cells A1..A14 represent a single month and contain the following values. 1, 6, 3, 8, 2, 9, 12, 3, 6, 2, 6, 9, 9, 4 In this example, there are 3 consecutive twelve-month periods, A1..A12, A2..A13 and A3..A14. The average of A1..A12 = 5.58, A2..A13 = 6.25, and A3..A14 = 6.08. I want a formula that will return the highest average of the three, 6.25. Thanks in advance for any assistance. -Mark |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highest 12-Month Period Average Over Range of Months
Hi Biff
Nice solution! Couldn't get my brain into gear late last night. -- Regards Roger Govier "T. Valko" wrote in message ... Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =MAX(SUBTOTAL(1,OFFSET(A1:A14,{1,2,3}-1,,12))) Actually, no need to array enter. Enter normally. When I tested I used the ROW function as the row offset argument which needed array entry. You could always use this: (but I have a feeling you really want more than just 3 ranges averaged which is why I posted the Subtotal version!) =MAX(AVERAGE(A1:A12),AVERAGE(A2:A13),AVERAGE(A3:A1 4)) Biff "T. Valko" wrote in message ... Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =MAX(SUBTOTAL(1,OFFSET(A1:A14,{1,2,3}-1,,12))) Biff "Mark T." <Mark wrote in message ... I need to know if a formula can be constructed that will return the highest consecutive twelve-month average over a range of months. Example: Assume cells A1..A14 represent a single month and contain the following values. 1, 6, 3, 8, 2, 9, 12, 3, 6, 2, 6, 9, 9, 4 In this example, there are 3 consecutive twelve-month periods, A1..A12, A2..A13 and A3..A14. The average of A1..A12 = 5.58, A2..A13 = 6.25, and A3..A14 = 6.08. I want a formula that will return the highest average of the three, 6.25. Thanks in advance for any assistance. -Mark |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highest 12-Month Period Average Over Range of Months
Perfect, that works. Thanks!
-Mark "T. Valko" wrote: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =MAX(SUBTOTAL(1,OFFSET(A1:A14,{1,2,3}-1,,12))) Actually, no need to array enter. Enter normally. When I tested I used the ROW function as the row offset argument which needed array entry. You could always use this: (but I have a feeling you really want more than just 3 ranges averaged which is why I posted the Subtotal version!) =MAX(AVERAGE(A1:A12),AVERAGE(A2:A13),AVERAGE(A3:A1 4)) Biff "T. Valko" wrote in message ... Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =MAX(SUBTOTAL(1,OFFSET(A1:A14,{1,2,3}-1,,12))) Biff "Mark T." <Mark wrote in message ... I need to know if a formula can be constructed that will return the highest consecutive twelve-month average over a range of months. Example: Assume cells A1..A14 represent a single month and contain the following values. 1, 6, 3, 8, 2, 9, 12, 3, 6, 2, 6, 9, 9, 4 In this example, there are 3 consecutive twelve-month periods, A1..A12, A2..A13 and A3..A14. The average of A1..A12 = 5.58, A2..A13 = 6.25, and A3..A14 = 6.08. I want a formula that will return the highest average of the three, 6.25. Thanks in advance for any assistance. -Mark |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highest 12-Month Period Average Over Range of Months
Biff,
Is there a way to have the {1,2,3} part of the formula be variable based on the count() of number of data items in the range? My data is not static, it grows each month. Let me know if you have any thoughts. Thanks, -Mark "T. Valko" wrote: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =MAX(SUBTOTAL(1,OFFSET(A1:A14,{1,2,3}-1,,12))) Actually, no need to array enter. Enter normally. When I tested I used the ROW function as the row offset argument which needed array entry. You could always use this: (but I have a feeling you really want more than just 3 ranges averaged which is why I posted the Subtotal version!) =MAX(AVERAGE(A1:A12),AVERAGE(A2:A13),AVERAGE(A3:A1 4)) Biff "T. Valko" wrote in message ... Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =MAX(SUBTOTAL(1,OFFSET(A1:A14,{1,2,3}-1,,12))) Biff "Mark T." <Mark wrote in message ... I need to know if a formula can be constructed that will return the highest consecutive twelve-month average over a range of months. Example: Assume cells A1..A14 represent a single month and contain the following values. 1, 6, 3, 8, 2, 9, 12, 3, 6, 2, 6, 9, 9, 4 In this example, there are 3 consecutive twelve-month periods, A1..A12, A2..A13 and A3..A14. The average of A1..A12 = 5.58, A2..A13 = 6.25, and A3..A14 = 6.08. I want a formula that will return the highest average of the three, 6.25. Thanks in advance for any assistance. -Mark |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highest 12-Month Period Average Over Range of Months
The {1,2,3} means that's how many averages are being calculated. It doesn't
have anything to do with how much data is in the range. You could make that a variable if you wanted (but it adds complexity to the formula). Say you wanted 2 averages: C1 = 2 (number of averages to use in the calc) Now the formula *is* an *array* and needs to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =MAX(SUBTOTAL(1,OFFSET(A1:A14,ROW(INDIRECT("1:"&C1 ))-1,,12))) If you add data to the end of the range then you could make the range dynamic and the references used in the formula will automatically adjust: http://contextures.com/xlNames01.html#Dynamic Then (still an array): =MAX(SUBTOTAL(1,OFFSET(Range,ROW(INDIRECT("1:"&C1) )-1,,12))) Biff "Mark T." wrote in message ... Biff, Is there a way to have the {1,2,3} part of the formula be variable based on the count() of number of data items in the range? My data is not static, it grows each month. Let me know if you have any thoughts. Thanks, -Mark "T. Valko" wrote: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =MAX(SUBTOTAL(1,OFFSET(A1:A14,{1,2,3}-1,,12))) Actually, no need to array enter. Enter normally. When I tested I used the ROW function as the row offset argument which needed array entry. You could always use this: (but I have a feeling you really want more than just 3 ranges averaged which is why I posted the Subtotal version!) =MAX(AVERAGE(A1:A12),AVERAGE(A2:A13),AVERAGE(A3:A1 4)) Biff "T. Valko" wrote in message ... Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =MAX(SUBTOTAL(1,OFFSET(A1:A14,{1,2,3}-1,,12))) Biff "Mark T." <Mark wrote in message ... I need to know if a formula can be constructed that will return the highest consecutive twelve-month average over a range of months. Example: Assume cells A1..A14 represent a single month and contain the following values. 1, 6, 3, 8, 2, 9, 12, 3, 6, 2, 6, 9, 9, 4 In this example, there are 3 consecutive twelve-month periods, A1..A12, A2..A13 and A3..A14. The average of A1..A12 = 5.58, A2..A13 = 6.25, and A3..A14 = 6.08. I want a formula that will return the highest average of the three, 6.25. Thanks in advance for any assistance. -Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I find the greatest possible sum within 12 months? A newbie... | Excel Worksheet Functions | |||
Picking out a Period, in relation to certain cells | Excel Worksheet Functions | |||
SUM a range of sales based on month | Excel Worksheet Functions | |||
Average starting with first month | Excel Discussion (Misc queries) | |||
Working days left in the month compared to previous months | Excel Worksheet Functions |