Offset function to count number of times in range, value is below Last Cell value

[analystbank]

Hello Seniors /Experts,

Sample Workbook is attached.
Background
Data range A31:M205. Row 31 acts as HEADER. This list will keep expanding down as days progresses in Column B, and corresponding data will be filled in adjacent cells.

Currently, Last Cell is B205, and corresponding value exist in Cell M205.

LATEST VALUE in cell M205 is 57.43, which has to be compared with preceding upside N values in same Column M.

Last range (N Days) which has to be compared could be last 50, 100, 150, or any custom number to lookback (upside from last Cell in Column M) for comparision purpose. This control (lookback number) could be in Cell E11

Currently I am using formula

Please Login or Register  to view this content.

in Cell E12. As and when new data (days) comes in, range will expand but always beginning from M31 which is header, and real data starts from M32, and Range will keep rolling like, M32:M204 (last row 205), M33:M205 (last row 206), M34:M206 (last row 207) and so on.

Above formula compares number of cells within Range (M32:M204), that has value less than what is being specified in Cell M205

(M205 is 57.43. Occurrence 1) M58=66.1, Occurrence 2) M117 has 59.07, and Occurrence 3) M118 has 82.49 values which are above M205 value of 57.43)

So, result is Number of times, last value = 170 out of 174 rows (I guess, one is missing number of record is 174 [Row 32 to row 205 = 174], three occurrence should return 171 !!!)


QUERY
How should I change the Formula to achieve the result considering

Last Cell in Column M is always changes, and goes down, as number of days gets update in future.

Look-back period, (controlled through Cell E11) should make comparison range in last N Days only which will also keep moving ignoring the last Cell in Column M and count / compare in reverse direction.

Thanks for your valuable time, and guidance.

P.S.: My earlier thread was here, but i guess, that could not be twisted to get desired result here in above case !!!
https://www.excelforum.com/excel-for...nding-row.html

[AliGW]

And how is this different to your previous thread?

[analystbank]

Honestly, Im confused, that's why made reference to it.

In previous thread, it was like finding, SUM / MAX / MIN value, but here, I just want to count - how many (occurrence of) cells compared to last cell.

I replaced COUNTA in place of either of SUM/MAX/MIN but that counts (non blank) all rows 173, and does not yield the desired result of 170 (173 - 3 occurrence where cell value is above last cell value).

[analystbank]

Any help or Hint please

[AliGW]

You are on a bit of a sticky wicket here, if I'm honest - this is not significantly different, and should have been asked in the other thread. However, I am in a generous mood.

Try this:

=COUNTIF(INDIRECT("M32:M"&MATCH(99^99,M:M)-1),"<"&LOOKUP(2,1/(M32:M9999<>0),M32:M9999))

I don't really know exactly what number you are expecting - the explanation in the workbook is very confused, and it doesn't really seem as if you know, either.

[analystbank]

So kind of you, Madam. (Sorry for poor English, if any).

I guess, you partly solved the query. Why partly because, it compares all cells within Column M. Whereas i need flexbility to compare within N range (preceding, 50, 100 or so) counted up from last cell, presently M205


I will try to be more simple, next time to raise query.

I don't really know exactly what number you are expecting - the explanation in the workbook is very confused, and it doesn't really seem as if you know, either.

Let me put this way,

Last Cell for comparison is M205 which will keep changing to M206 , M207, and so on when new data arrives.
It is this last cell in Column M that has to be compared in Column M (excluding the last one) within preceding N cells in Column M itself (say preceding 50, 100 or so) and count number of occurrence where value exceeds the value given in last Cell in Column M and from the given full range, subtract that number. So resultant number will say, all cell values within N range is less than current value in M205.

In the given example,
M205 is last so that is what we have to compare,
Cell M58 (66.10) value is > M205 (57.43)
Cell M117 (59.07) value is > M205 (57.43)
Cell M118 (82.49) value is > M205 (57.43)

I have highlighted in the first post having sample workbook with grey color.

All Cells other than M58,M117, M118 have values less than (<) what is being currently stored in last cell M205.

Since as in present scenario, we are comparing within Range M32:M205, (total 173 cells, and number of cases where cell value is > M205 is three, formula result should return value (173-3 = 170)

This range of 173 has to be flexible for user such that, similar comparison can be quickly ascertained in preceding 50, 100, or 150 cells within Column M.

[AliGW]

Yes, you have a tendency to be rather verbose!

Try this:

=COUNTIF(INDIRECT("M"&MATCH(99^99,M:M)-50&":M"&MATCH(99^99,M:M)-1),"<"&LOOKUP(2,1/(M32:M9999<>0),M32:M9999))

[analystbank]

Honestly, Thanks, M'am for being with me as guiding star.

[AliGW]

You're welcome!