• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 62
  • Last Modified:

Formula to include INDIRECT function

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
0
racepro
Asked:
racepro
  • 4
  • 3
1 Solution
 
Bill PrewCommented:
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
  )
)

Open in new window


»bp
1
 
NorieVBA ExpertCommented:
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.
1
 
raceproretiredAuthor Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

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

Open in new window


»bp
0
 
raceproretiredAuthor Commented:
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
0
 
Bill PrewCommented:
Okay, hence the use of SMALL, give this a try then:

=SMALL(INDIRECT("AV"&P4&":AV"&Q4),COUNTIF(INDIRECT("AV"&P4&":AV"&Q4),0)+1)

Open in new window


»bp
1
 
raceproretiredAuthor Commented:
Thanks Bill, You've excelled again
Ian
0
 
Bill PrewCommented:
Welcome.


»bp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now