Formula to include INDIRECT function

I can't deduce what the correction might be with knowing what you are trying to do.  Can you provide a sample of the data and the desired results.

I typically break down longer formulas in a text editor when building them to help get parens and args in the proper place.  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.

I broke your formula down as follows:

SMALL(
  INDIRECT("AV"&P4&":AV"&Q4),
  INDEX(
    FREQUENCY(
      INDIRECT("AV"&P4&":AV"&Q4),
      0
    ),
    1
  )
)

Select allOpen in new window

---

»bp

What is the formula meant to do and what do the values in P4 and Q4 represent?

PS Bill, I do something similar when working with long, complicated formulas by using ALT+ENTER in the Formula bar.

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

Working based on cells in original formula, then maybe this?

=MIN(INDIRECT("AV"&P4&":AV"&Q4))

Select allOpen in new window

---

»bp

Bill, sorry I forgot to add that I wanted to exclude zero values so I modified it to the following
=MIN(INDIRECT("AV"&P4&":AV"&Q4),">0")
but got a "VALUE" error

Thanks Bill, You've excelled again
Ian

Welcome.

---

»bp