How would you write a new "MAX" function which would allow you tovary the size of range in the target column?Example:A B1423 =MaxOf (A4,4) gives value 47 =MaxOf (A5,4) gives value 75 =MaxOf (A6,4) gives value 78 =MaxOf (A7,4) gives value 85 =MaxOf (A8,4) gives value 810 =MaxOf (A9,4) gives value 109 =MaxOf (A10,4) gives value 10
You can already do this, and it doesn't require VBA. Following yourexample, in cell B4, enter the following:=MAX(A$1:A4)Then simply drag this down the length of column B. The $ will fix thestart of the range, the lack of one in the A4 will allow the referenceto 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 amore descriptive syntax would be:MaxOf (TargetColumn, ColumnCellsToLookBack)So, if the formula is copied down a column, you wouldget the maximum of the last four (4) cells.By making "ColumnCellsToLookBack" a fixed reference atthe 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 andOFFSET. 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 formulais:=MAX(OFFSET(A4,0,0,-Scope,1))
Sorry, that formula should have been in B4, of course!