 Search:

# New MAX function?

Asked By: Bertha    Date: Sep 17    Category: MS Office    Views: 1289

How would you write a new "MAX" function which would allow you to
vary the size of range in the target column?

Example:
A B
1
4
2
3 =MaxOf (A4,4) gives value 4
7 =MaxOf (A5,4) gives value 7
5 =MaxOf (A6,4) gives value 7
8 =MaxOf (A7,4) gives value 8
5 =MaxOf (A8,4) gives value 8
10 =MaxOf (A9,4) gives value 10
9 =MaxOf (A10,4) gives value 10

Share:

You can already do this, and it doesn't require VBA. Following your
example, in cell B4, enter the following:

=MAX(A\$1:A4)

Then simply drag this down the length of column B. The \$ will fix the
start of the range, the lack of one in the A4 will allow the reference
to move with the formula, so in B5 the range  will be A\$1:A5 and so on.

The range  will not always start in row 1; perhaps a
more descriptive syntax would be:

MaxOf (TargetColumn, ColumnCellsToLookBack)

So, if the formula is copied down a column, you would
get the maximum of the last four (4) cells.

By making "ColumnCellsToLookBack" a fixed reference at
the top of the column, you might change the "look back",
from 2 to 100 (or however many) cells desired.

OK, I get it now. Right, then. You can do this by combining MAX and
OFFSET. Let's assume you have a constant or a single cell refence called
'Scope', which you might have set to a value of 4. In A4, the formula
is:

=MAX(OFFSET(A4,0,0,-Scope,1))

Sorry, that formula should have been in B4, of course!

Didn't find what you were looking for? Find more on New MAX function? Or get search suggestion and latest updates.