Return MAX value Offset 1 Row from Criterion and count occurrence

[Sam via OfficeKB.com]

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

 

[Domenic]

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!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you very much for reply and assistance.

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

 

[Domenic]

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.[/QUOTE]

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!

 

[Sam via OfficeKB.com]

Hi Domenic,

That's done it. Brilliant!

Thank you for your time and assistance.

Cheers,
Sam

[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!

 

[Domenic]

You're very welcome! Thanks for the feedback!