• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding MAX and MIN in a range that is defined by non-blank cells

D

dv0x

New Member
Hi Ninjas,

I'm a long time Reader, first time Poster.

I've run into a problem while doing some financial market research on a large dataset that I have not yet been able to google my way out of.

I want to find the MAX of (F:F) and MIN of (G:G) in a range of rows which are defined as starting and ending with non-blank cells in (I:I).

Here are 2 specific examples from the Sample Sheet I've shared:

I8 is the first non-blank cell in (I:I)
I20 is the next non-blank cell in (I:I) after I8
Therefore, In K8 I want to find the MAX(F8:F20) and in L8 I want to find the MIN(G8:G20). These values are 2080.25 and 2070 respectively.

NEXT

I20 is the first non-blank cell in (I:I) after I8
I38 is the next non-blank cell in (I:I) after I20
Therefore, In K20 I want to find the MAX(F20:F38) and in L20 I want to find the MIN(G20:G38). These values are 2077 and 2066.50 respectively.

Thank you for helping me in my pursuit of being awesome.
 

Attachments

Somendra,

After further review your solution is very close, but not correct. The correct answers are as follows:

K8 = 2080.25 (from F10)
L8 = 2070 (from G20)

K20 = 2077 (from F38)
L20 = 2066.5 (G24)

The range, as it has been defined, is not including the rows which are non-blank (8, 20 and 38). In the Sample Sheet the Lowest Low (G20) from the first example happens to fall in the next non-blank row as does the Highest High (F38) in the second example. This will not always be the case.
 
Hi Somendra,

For K8,the range will be F8:F20
For K20,the range will be F20:F38

Non-blank rows determine the range.

This is my mistake. I inserted the INPUT and OUTPUT row at the top just before uploading the file and failed to amend my explanation in the text box. I apologize for the error.
 
You must log in or register to reply here.
Back
Top