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
- How to get MIN, MAX and AVG of a variable sized table
- Max number of sheets in workbook?
- VBA Max Line Limit
- MAX VALUE CELL ADDRESS
- open a new page in new window on page load
- How can I call the function TotalPrice within another function?
- about JSP reset function
- how Mysql database function java code execute ?
- HttpContext.Current function returns Nothing for spawned thread
- Math function to determine if a number is an int
- date Function
- MAPI Functions
- function that returns a String Array
- Why does Java not allow default function arguments ?
- Is there a way or method to implement different function of applet
- Call JavaScript function from JAva Code
- can somebody please explain the function of "this"
- where should i include function
- Call C function through Java
- Column Headings using getColumnName function
- how to make alphabets uppercase without using function
- Destroy function inservlet
- How to call a JNI function in C