MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

New MAX function?

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

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

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



4 Answers Found

Answer #1    Answered By: Teresa Rogers     Answered On: Sep 17

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


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.

Answer #2    Answered By: Tammy Sanders     Answered On: Sep 17

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.

Answer #3    Answered By: Hilma Miller     Answered On: Sep 17

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


Answer #4    Answered By: Earl Stone     Answered On: Sep 17

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.