<div>
I can't deduce what the correction might be with knowing what you are trying to do. &nbsp;Can you provide a sample of the data and the desired results.<br />
<br />
I typically break down longer formulas in a text editor when building them to help get parens and args in the proper place. &nbsp;It looks like you haven't violated any of the parms, but hard to know if you have the right values in the right places without knowing the goal.<br />
<br />
I broke your formula down as follows:<br />
<br />

<pre><code class="code-10 nolinks" id="code-20-42354736-1"><span>SMALL(</span>
<span>  INDIRECT(&quot;AV&quot;&amp;P4&amp;&quot;:AV&quot;&amp;Q4),</span>
<span>  INDEX(</span>
<span>    FREQUENCY(</span>
<span>      INDIRECT(&quot;AV&quot;&amp;P4&amp;&quot;:AV&quot;&amp;Q4),</span>
<span>      0</span>
<span>    ),</span>
<span>    1</span>
<span>  )</span>
<span>)</span>
</code></pre>
<hr />
»<b><i>bp</i></b>
</div>


<div>
What is the formula meant to do and what do the values in P4 and Q4 represent?<br />
<br />
PS Bill, I do something similar when working with long, complicated formulas by using ALT+ENTER in the Formula bar.
</div>


<div>
I'm trying to find the minimum value in a range.<br />
example<br />
The minimum value in below range is 14<br />
AV1 &nbsp;20<br />
AV2 &nbsp;30<br />
AV3 &nbsp;14<br />
AV4 &nbsp; 40<br />
The ROW range is shown in cells &nbsp; P4 &nbsp; 1 &nbsp; &nbsp;Q4 &nbsp;4 &nbsp;(P4 &amp;&nbsp;Q4 are only cells where data range is input.)<br />
The formula looks up column AV and determines the smallest value<br />
between rows AV1 and &nbsp;AV4
</div>


<div>
Working based on cells in original formula, then maybe this?<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-42354749-1"><span>=MIN(INDIRECT(&quot;AV&quot;&amp;P4&amp;&quot;:AV&quot;&amp;Q4))</span>
</code></pre>
<hr />
»<b><i>bp</i></b>
</div>


<div>
Bill, sorry I forgot to add that I wanted to exclude zero values so I modified it to the following<br />
=MIN(INDIRECT(&quot;AV&quot;&amp;P4&amp;&quot;:AV<wbr />&quot;&amp;Q4),&quot;&gt;0&quot;<wbr />)<br />
but got a &quot;VALUE&quot; error
</div>


<div>
Thanks Bill, You've excelled again<br />
Ian
</div>


<div>
Welcome.<br />
<br />

<hr />
»<b><i>bp</i></b>
</div>


<div>
<div class="content wysiwyg-content">
Hi,<br />
Could someone please modify the below formula or provide a new one. I may have placed the INDIRECT in the wrong place.<br />
<br />
=SMALL(INDIRECT(&quot;AV&quot;&amp;P4&amp;&quot;:<wbr />AV&quot;&amp;Q4),IN<wbr />DEX(FREQUE<wbr />NCY(INDIRE<wbr />CT(&quot;AV&quot;&amp;P4<wbr />&amp;&quot;:AV&quot;&amp;Q4)<wbr />,0),1))<br />
<br />
Assuming values are in cells P4 and Q4 and referring to values in column AV<br />
<br />
Thanks<br />
<br />
Ian
</div>
</div>


Hi,
Could someone please modify the below formula or provide a new one. I may have placed the INDIRECT in the wrong place.

=SMALL(INDIRECT("AV"&P4&":AV"&Q4),INDEX(FREQUENCY(INDIRECT("AV"&P4&":AV"&Q4),0),1))

Assuming values are in cells P4 and Q4 and referring to values in column AV

Thanks

Ian

Formula to include INDIRECT function

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.