If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Best 3 Consecutive Months
I have 10 years worth of monthly data and need to determine the best (or
worst) three consecutive months for benchmarking purposes. How do I write a formula that provides me the value of the highest (or lowest) value for three consecutive months (perhaps array formula?). I want to avoid at all cost adding another column of data to the worksheet that provides a running three month average and then utilizing the MAX or MIN function to determine the value. Thanks in advance Rachel |
#2
|
|||
|
|||
Best 3 Consecutive Months
Hi,
It may be possible in a single formula but nothing springs to mind so here's another solution. Say your data are in column A. Put this in (say) B1 and drag down =SUM(A1:A3) You now have a sum of every set of 3 consecutive cell so simply max and min this new range to get the highest and lowest values for 3 consecutive months. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Desoto" wrote: I have 10 years worth of monthly data and need to determine the best (or worst) three consecutive months for benchmarking purposes. How do I write a formula that provides me the value of the highest (or lowest) value for three consecutive months (perhaps array formula?). I want to avoid at all cost adding another column of data to the worksheet that provides a running three month average and then utilizing the MAX or MIN function to determine the value. Thanks in advance Rachel |
#3
|
|||
|
|||
Best 3 Consecutive Months
Hi,
Assuming you numbers are in B2:B24 with titles on row 1, enter the following array formula: =MAX(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3))) For the 3 lowest you need to be a little more careful: =MIN(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3))) For an array formula you do not type the outer {}'s, you press SHIFT+CTRL+Enter to enter the formula not Enter. Of course this doesn't identify the location of those 3 consecutive items. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Desoto" wrote: I have 10 years worth of monthly data and need to determine the best (or worst) three consecutive months for benchmarking purposes. How do I write a formula that provides me the value of the highest (or lowest) value for three consecutive months (perhaps array formula?). I want to avoid at all cost adding another column of data to the worksheet that provides a running three month average and then utilizing the MAX or MIN function to determine the value. Thanks in advance Rachel |
#4
|
|||
|
|||
Best 3 Consecutive Months
Shane Devenshire wrote:
Hi, Assuming you numbers are in B2:B24 with titles on row 1, enter the following array formula: =MAX(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3))) For the 3 lowest you need to be a little more careful: =MIN(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3))) For an array formula you do not type the outer {}'s, you press SHIFT+CTRL+Enter to enter the formula not Enter. Of course this doesn't identify the location of those 3 consecutive items. Array formula for the row number (within the list of numbers) of the start of those 3 consecutive items: =MATCH( MAX(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3))), SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3)), 0) |
#5
|
|||
|
|||
Best 3 Consecutive Months
Absolutely PERFECT!!! Thanks Guys
"Glenn" wrote: Shane Devenshire wrote: Hi, Assuming you numbers are in B2:B24 with titles on row 1, enter the following array formula: =MAX(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3))) For the 3 lowest you need to be a little more careful: =MIN(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3))) For an array formula you do not type the outer {}'s, you press SHIFT+CTRL+Enter to enter the formula not Enter. Of course this doesn't identify the location of those 3 consecutive items. Array formula for the row number (within the list of numbers) of the start of those 3 consecutive items: =MATCH( MAX(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3))), SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3)), 0) . |
#6
|
|||
|
|||
Best 3 Consecutive Months
Hi Glenn,
Yes I knew that but since the original post didn't ask for it, I just want to hint that they might want to know something else. If he wanted we could show him the full address of these results or we could conditionally format them. However, it is the core formula that I designed that was the critical element, I felt. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Glenn" wrote: Shane Devenshire wrote: Hi, Assuming you numbers are in B2:B24 with titles on row 1, enter the following array formula: =MAX(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3))) For the 3 lowest you need to be a little more careful: =MIN(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3))) For an array formula you do not type the outer {}'s, you press SHIFT+CTRL+Enter to enter the formula not Enter. Of course this doesn't identify the location of those 3 consecutive items. Array formula for the row number (within the list of numbers) of the start of those 3 consecutive items: =MATCH( MAX(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3))), SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3)), 0) . |
#7
|
|||
|
|||
Best 3 Consecutive Months
Try these array formulas** :
Max: =MAX(B2:B22+B3:B23+B4:B24) Min: =MIN(B2:B22+B3:B23+B4:B24) Note the 3 cell offset of the ranges. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Shane Devenshire" wrote in message news Hi, Assuming you numbers are in B2:B24 with titles on row 1, enter the following array formula: =MAX(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3))) For the 3 lowest you need to be a little more careful: =MIN(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3))) For an array formula you do not type the outer {}'s, you press SHIFT+CTRL+Enter to enter the formula not Enter. Of course this doesn't identify the location of those 3 consecutive items. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Desoto" wrote: I have 10 years worth of monthly data and need to determine the best (or worst) three consecutive months for benchmarking purposes. How do I write a formula that provides me the value of the highest (or lowest) value for three consecutive months (perhaps array formula?). I want to avoid at all cost adding another column of data to the worksheet that provides a running three month average and then utilizing the MAX or MIN function to determine the value. Thanks in advance Rachel |
#8
|
|||
|
|||
Best 3 Consecutive Months
Yours is very smart!
"T. Valko" wrote: Try these array formulas** : Max: =MAX(B2:B22+B3:B23+B4:B24) Min: =MIN(B2:B22+B3:B23+B4:B24) Note the 3 cell offset of the ranges. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Shane Devenshire" wrote in message news Hi, Assuming you numbers are in B2:B24 with titles on row 1, enter the following array formula: =MAX(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3))) For the 3 lowest you need to be a little more careful: =MIN(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3))) For an array formula you do not type the outer {}'s, you press SHIFT+CTRL+Enter to enter the formula not Enter. Of course this doesn't identify the location of those 3 consecutive items. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Desoto" wrote: I have 10 years worth of monthly data and need to determine the best (or worst) three consecutive months for benchmarking purposes. How do I write a formula that provides me the value of the highest (or lowest) value for three consecutive months (perhaps array formula?). I want to avoid at all cost adding another column of data to the worksheet that provides a running three month average and then utilizing the MAX or MIN function to determine the value. Thanks in advance Rachel . |
#9
|
|||
|
|||
Best 3 Consecutive Months
Thanks!
-- Biff Microsoft Excel MVP "Alojz" wrote in message ... Yours is very smart! "T. Valko" wrote: Try these array formulas** : Max: =MAX(B2:B22+B3:B23+B4:B24) Min: =MIN(B2:B22+B3:B23+B4:B24) Note the 3 cell offset of the ranges. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Shane Devenshire" wrote in message news Hi, Assuming you numbers are in B2:B24 with titles on row 1, enter the following array formula: =MAX(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3))) For the 3 lowest you need to be a little more careful: =MIN(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3))) For an array formula you do not type the outer {}'s, you press SHIFT+CTRL+Enter to enter the formula not Enter. Of course this doesn't identify the location of those 3 consecutive items. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Desoto" wrote: I have 10 years worth of monthly data and need to determine the best (or worst) three consecutive months for benchmarking purposes. How do I write a formula that provides me the value of the highest (or lowest) value for three consecutive months (perhaps array formula?). I want to avoid at all cost adding another column of data to the worksheet that provides a running three month average and then utilizing the MAX or MIN function to determine the value. Thanks in advance Rachel . |
#10
|
|||
|
|||
Best 3 Consecutive Months
There is one significant difference, try generalizing
=MAX(B2:B22+B3:B23+B4:B24) to the max of 30 consecutive numbers. Then try generalizing =MAX(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3))) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "T. Valko" wrote: Thanks! -- Biff Microsoft Excel MVP "Alojz" wrote in message ... Yours is very smart! "T. Valko" wrote: Try these array formulas** : Max: =MAX(B2:B22+B3:B23+B4:B24) Min: =MIN(B2:B22+B3:B23+B4:B24) Note the 3 cell offset of the ranges. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Shane Devenshire" wrote in message news Hi, Assuming you numbers are in B2:B24 with titles on row 1, enter the following array formula: =MAX(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3))) For the 3 lowest you need to be a little more careful: =MIN(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3))) For an array formula you do not type the outer {}'s, you press SHIFT+CTRL+Enter to enter the formula not Enter. Of course this doesn't identify the location of those 3 consecutive items. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Desoto" wrote: I have 10 years worth of monthly data and need to determine the best (or worst) three consecutive months for benchmarking purposes. How do I write a formula that provides me the value of the highest (or lowest) value for three consecutive months (perhaps array formula?). I want to avoid at all cost adding another column of data to the worksheet that provides a running three month average and then utilizing the MAX or MIN function to determine the value. Thanks in advance Rachel . . |
|
Thread Tools | |
Display Modes | |
|
|