ExcelBanter - Find Max value within a Offset

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Find Max value within a Offset (https://www.excelbanter.com/excel-discussion-misc-queries/446744-find-max-value-within-offset.html)

StigmataZA

Find Max value within a Offset

Hi

I am trying to return the max value that is part of a offset range.

Basically i have a name range that i use for trending and i want to find the highest value of that trend.

=OFFSET(Data!$M$1,'Trend 1'!$W$1,,'Trend 1'!$Y$1)

$W$1 refers to where my trend will start.
$Y$1 refers to how many row to include in the trend.

the trend works great. the problem is that there are a large number of data points. i would therefore like to display the highest value from the offset range.

Any assistance will be greatly appreciated.

zvkmpw

Find Max value within a Offset

I am trying to return the max value that is part of a offset range.
=OFFSET(Data!$M$1,'Trend 1'!$W$1,,'Trend 1'!$Y$1)

Just wrap MAX() around it:
=MAX(OFFSET(Data!$M$1,'Trend 1'!$W$1,,'Trend 1'!$Y$1))

When OFFSET() returns a range, it can be used as a parameter in a function that expects a range, like MAX or AVERAGE.

StigmataZA

Quote:

Originally Posted by zvkmpw (Post 1604273)

Just wrap MAX() around it:
=MAX(OFFSET(Data!$M$1,'Trend 1'!$W$1,,'Trend 1'!$Y$1))

When OFFSET() returns a range, it can be used as a parameter in a function that expects a range, like MAX or AVERAGE.

Shot zvkmpw, i did try that but it did not work previously. it appears that there was a issue with the formula that made up the range part of the offset.

Sorted now and working.

i thought that it could not be complicated, got to love them formulae, works for one thing but not another :

All times are GMT +1. The time now is 12:47 AM.