• 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

  • Chandoo Sample.xlsx
    13.9 KB · Views: 0
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