Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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

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.

Related Topics:

- Wait function and Invisible function
- Wait function and Invisible function
- Max number of sheets in workbook?
- VBA Max Line Limit
- How to get MIN, MAX and AVG of a variable sized table
- MAX VALUE CELL ADDRESS
- open a new page in new window on page load
- How can I call the function TotalPrice within another function?
- Destroy function inservlet
- help on functions
- Problems with XL's 'MATCH' function
- User Function - is optional calculation possibe?
- Automatic update of Function return value.
- String-Split Function parameter "compare"
- function as argument
- Need to create function smiliar to DMAX
- Excel won't calculate my function
- Populate Formula(Function) in rows
- defining variables in function
- help requested for using excel's built-in function in coding
- Linest Function
- user defined functions
- How to call a JNI function in C
- User defined functions not recognized
- custom Sum function?