Return MAX value Offset 1 Row from Criterion and count occurrence
 

Hi All,

I have a dynamic named range "Data" that spans 8 columns and many rows.
"Data" holds numeric values. "Data" starts at row 18, column "K". The oldest
data is at the top / start of the dynamic range and the most recent is at the
bottom / end.

I would like 2 flexible formulas that can look at the LAST "x" number of rows
( "x" will vary) within my dynamic range "Data". Using the LAST 70 rows, I
would like 2 formulas that fill across to find and return:

1) the maximum numeric value per column that has a numeric criterion of 100
in the row below it.
2) a summed count of the times (1) above occurs.

Would very much appreciate a formula using the dynamic named range "Data" as
opposed to the A1 notation style of cell referencing.

Thanks,
Sam

--
Message posted via http://www.officekb.com

Return MAX value Offset 1 Row from Criterion and count occurrence
 

Assuming that A2 contains 'x', try...

B2, confirmed with CONTROL+SHIFT+ENTER, copied across:

=MAX(IF(OFFSET(Data,ROWS(Data)-1,COLUMNS($B2:B2)-1,-$A2+1,1)=100,OFFSET(D
ata,ROWS(Data)-2,COLUMNS($B2:B2)-1,-$A2+1,1)))

For the second part, try....

=COUNTIF(OFFSET(Data,ROWS(Data)-1,COLUMNS($B2:B2)-1,-$A2+1,1),100)

....confirmed with CONTROL+SHIFT+ENTER. Note that if the numeric
criterion can also be found in the rows above them, the formulas will
need to be changed.

Hope this helps!

In article <822eb031110a0@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi All,

I have a dynamic named range "Data" that spans 8 columns and many rows.
"Data" holds numeric values. "Data" starts at row 18, column "K". The oldest
data is at the top / start of the dynamic range and the most recent is at the
bottom / end.

I would like 2 flexible formulas that can look at the LAST "x" number of rows
( "x" will vary) within my dynamic range "Data". Using the LAST 70 rows, I
would like 2 formulas that fill across to find and return:

1) the maximum numeric value per column that has a numeric criterion of 100
in the row below it.
2) a summed count of the times (1) above occurs.

Would very much appreciate a formula using the dynamic named range "Data" as
opposed to the A1 notation style of cell referencing.

Thanks,
Sam

Return MAX value Offset 1 Row from Criterion and count occurrence
 

Hi Domenic,

Thank you very much for reply and assistance.

Domenic wrote:
Assuming that A2 contains 'x', try...

B2, confirmed with CONTROL+SHIFT+ENTER, copied across:

=MAX(IF(OFFSET(Data,ROWS(Data)-1,COLUMNS($B2:B2)-1,-$A2+1,1)=100,OFFSET(D
ata,ROWS(Data)-2,COLUMNS($B2:B2)-1,-$A2+1,1)))

This works Great!

For the second part, try....

=COUNTIF(OFFSET(Data,ROWS(Data)-1,COLUMNS($B2:B2)-1,-$A2+1,1),100)
...confirmed with CONTROL+SHIFT+ENTER.

The count is picking up other numeric values that have a numeric criterion of
100
in the row below them but they are not necessarily the maximum value in the
LAST "x" rows.
The criterion of 100 should only be counted if it has the maximum value above
it, within the LAST "x" rows.

Note that if the numeric criterion can also be found in the rows above them, the formulas will
need to be changed.

The numeric criterion can be found in the rows below other numeric values
that are not the maximum.

Hope this helps!

Further help most appreciated.

Cheers,
Sam

--
Message posted via http://www.officekb.com

Return MAX value Offset 1 Row from Criterion and count occurrence
 

In article <823c088bb2a16@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

For the second part, try....

=COUNTIF(OFFSET(Data,ROWS(Data)-1,COLUMNS($B2:B2)-1,-$A2+1,1),100)
...confirmed with CONTROL+SHIFT+ENTER.

The count is picking up other numeric values that have a numeric criterion of
100
in the row below them but they are not necessarily the maximum value in the
LAST "x" rows.
The criterion of 100 should only be counted if it has the maximum value above
it, within the LAST "x" rows.

Try...

B2, confirmed with CONTROL+SHIFT+ENTER, copied across:

=SUM(IF(OFFSET(Data,ROWS(Data)-1,COLUMNS($B2:B2)-1,-$A2+1,1)=100,IF(OFFSE
T(Data,ROWS(Data)-2,COLUMNS($B2:B2)-1,-$A2+1,1)=B2,1)))

Hope this helps!

Return MAX value Offset 1 Row from Criterion and count occurrence
 

Hi Domenic,

That's done it. Brilliant!

Thank you for your time and assistance.

Cheers,
Sam

Domenic wrote:
For the second part, try....

[quoted text clipped - 7 lines]
The criterion of 100 should only be counted if it has the maximum value above
it, within the LAST "x" rows.

Try...

B2, confirmed with CONTROL+SHIFT+ENTER, copied across:

=SUM(IF(OFFSET(Data,ROWS(Data)-1,COLUMNS($B2:B2)-1,-$A2+1,1)=100,IF(OFFSE
T(Data,ROWS(Data)-2,COLUMNS($B2:B2)-1,-$A2+1,1)=B2,1)))

Hope this helps!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200804/1

Return MAX value Offset 1 Row from Criterion and count occurrence
 

You're very welcome! Thanks for the feedback!

In article <823d2faf8deb4@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi Domenic,

That's done it. Brilliant!

Thank you for your time and assistance.

Cheers,
Sam