Find Minimum Value in Variable Range (On Separate Sheet)

Hi,

Let me try to explain my situation here. I need to find the minimum value in a variable range, excluding zero. I'm trying to find the most efficient way to do this.

Here's the formula I've got:

Please Login or Register  to view this content.

The result is arbitrarily being placed into "O8" for testing.

This works correctly, but I'm not sure that I can accomplish what I need this way because I'm not going to be able to hardcode the range. Here's how my workbook looks:

\1

There are any number of Raw Data files, and each needs to have this calculation run on it a variable number of times. The final destination of this result will actually be on the "Report" sheet.

On each Raw Data sheet, the operation of ValuesPer ("D7") / IncY ("L6") gives me the number of rows to be included per calculation (i.e. this determines the range).

Please Login or Register  to view this content.

Somehow, I need to use this numOfRows value to determine that range over which I am calculating the minimum value. I want to do something like this:

Please Login or Register  to view this content.

The data begins at the bottom row, and ends at the top row. So I'm trying to start at the bottom row, select the range of (ValuesPer/IncY) size including it, find the minimum value in that range, and then move up and continue finding the minimum values in this range.


This is kind of complicated for me to describe, so I hope it's understandable. I'm just not really sure how to go about this.

My main problem is that I'm trying to use SMALL and COUNTIF in the context of VBA, but I don't think it's possible unless you use the R1C1 format, which I believe can only be used for relative referencing.