# Find Minimum Value in Variable Range (On Separate Sheet)

1. ## 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.

2. ## Re: Find Minimum Value in Variable Range (On Separate Sheet)

why not use vba functions and code ?
attach a sample of your file with comments

3. ## Re: Find Minimum Value in Variable Range (On Separate Sheet)

Originally Posted by patel45
why not use vba functions and code ?
I guess I'm just not familiar enough with VBA. Are there similar functions, or is my only option to create my own?
I'm glad you seem to understand the problem, I was worried that my post wasn't very clear lol.

Is there an efficient way to do this without using functions like "SMALL" in code? I wanted to avoid moving through all of the cells and doing it in code, because it's going to be a lot of data.

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1