=MAX(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3)))
I am trying to do this exact same formula, however, I need my array to calculate across columns instead of rows, I have substituted ROW with COLUMN but I get 0.00's. Any advice?
Shane Devenshire wrote:
There is one significant difference, try
01-Feb-10
There is one significant difference, try generalizin
=MAX(B2:B22+B3:B23+B4:B24
to the max of 30 consecutive numbers. Then try generalizin
=MAX(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3))
-
If this helps, please click the Yes button
Cheers
Shane Devenshir
:
Previous Posts In This Thread:
Best 3 Consecutive Months
I have 10 years worth of monthly data and need to determine the best (o
worst) three consecutive months for benchmarking purposes. How do I write
formula that provides me the value of the highest (or lowest) value for thre
consecutive months (perhaps array formula?). I want to avoid at all cos
adding another column of data to the worksheet that provides a running thre
month average and then utilizing the MAX or MIN function to determine th
value
Thanks in advanc
Rachel
Hi,It may be possible in a single formula but nothing springs to mind so here
Hi
It may be possible in a single formula but nothing springs to mind so here i
another solution
Say your data are in column A. Put this in (say) B1 and drag dow
=SUM(A1:A3
You now have a sum of every set of 3 consecutive cell so simply max and mi
this new range to get the highest and lowest values for 3 consecutive months
-
Mik
When competing hypotheses are otherwise equal, adopt the hypothesis tha
introduces the fewest assumptions while still sufficiently answering th
question
:
Hi,Assuming you numbers are in B2:B24 with titles on row 1, enter the
Hi
Assuming you numbers are in B2:B24 with titles on row 1, enter the followin
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 pres
SHIFT+CTRL+Enter to enter the formula not Enter
Of course this does not identify the location of those 3 consecutive items
-
If this helps, please click the Yes button
Cheers
Shane Devenshir
:
Shane Devenshire wrote:Array formula for the row number (within the list of
Shane Devenshire wrote
Array formula for the row number (within the list of numbers) of the start o
those 3 consecutive items
=MATCH
MAX(SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3)))
SUBTOTAL(9,OFFSET(B1,ROW(1:21),,3))
0)
Absolutely PERFECT!!! Thanks Guys"Glenn" wrote:
Absolutely PERFECT!!! Thanks Guy
:
Hi Glenn,Yes I knew that but since the original post did not ask for it, I
Hi Glenn
Yes I knew that but since the original post did not ask for it, I just wan
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 coul
conditionally format them
However, it is the core formula that I designed that was the critica
element, I felt
-
If this helps, please click the Yes button
Cheers
Shane Devenshir
:
Try these array formulas**
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 o
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIF
key then hit ENTER
-
Bif
Microsoft Excel MVP
Yours is very smart!"T. Valko" wrote:
Yours is very smart!
:
Thanks!--BiffMicrosoft Excel MVP
Thanks!
--
Biff
Microsoft Excel MVP
There is one significant difference, try
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
:
Yeah, that is true.
Yeah, that is true. I would not use or suggest that formula for more than 3
or 4 consecutive cell intervals.
While we are at it...
If the range to calculate is B2:B24...
ROW(1:21) is not very intuitive in addition to not being very robust.
--
Biff
Microsoft Excel MVP
Submitted via EggHeadCafe - Software Developer Portal of Choice
Get Silverlight 4 Installed: Tips and Tricks
http://www.eggheadcafe.com/tutorial...b-f54c56a64ed9/get-silverlight-4-install.aspx