Issues with Array Functions I am lost overcoming

[nicolelost]

I have been working on this for days and have searched and searched. Of the forums where I have found help this has been the best.

I have two columns of data:
Name: Performance Value:
A 1
B 2
C 0
D 2
E 5
F 6
G 3

In another spot I am trying to lookup the following:
1) the top 5 Performance Values2) The lowest 5 Performance Values when the value is greater than 0
3) The Name Associated with the top or lowest performance value, accounting for duplicates
4) I would like to set it up to where I have a cell where I put in the cell range to use in the formula and source back to it, I have no Idea how to do this or search for it apparently. I have to copy and paste all this numerous times so I need to make it easy to modify these formulas by changing the cell range.


Here is how I have it set up:
Name Lookup: Smallest or Largest Values:
=INDEX(RangeWithNames,MATCH(CellToRight,RangeWithPerformanceValues,0)) =SMALL(RangeWithPerformanceValues,1+COUNTIF(RangeWithPerformanceValues,0))
=INDEX(RangeWithNames,MATCH(CellToRight,RangeWithPerformanceValues,0)) =SMALL(RangeWithPerformanceValues,2+COUNTIF(RangeWithPerformanceValues,0))
=INDEX(RangeWithNames,MATCH(CellToRight,RangeWithPerformanceValues,0)) =SMALL(RangeWithPerformanceValues,3+COUNTIF(RangeWithPerformanceValues,0))
=INDEX(RangeWithNames,MATCH(CellToRight,RangeWithPerformanceValues,0)) =SMALL(RangeWithPerformanceValues,4+COUNTIF(RangeWithPerformanceValues,0))
=INDEX(RangeWithNames,MATCH(CellToRight,RangeWithPerformanceValues,0)) =SMALL(RangeWithPerformanceValues,5+COUNTIF(RangeWithPerformanceValues,0))
=INDEX(RangeWithNames,MATCH(CellToRight,RangeWithPerformanceValues,0)) =LARGE(G3Three,5)
=INDEX(RangeWithNames,MATCH(CellToRight,RangeWithPerformanceValues,0)) =LARGE(G3Three,4)
=INDEX(RangeWithNames,MATCH(CellToRight,RangeWithPerformanceValues,0)) =LARGE(G3Three,3)
=INDEX(RangeWithNames,MATCH(CellToRight,RangeWithPerformanceValues,0)) =LARGE(G3Three,2)
=INDEX(RangeWithNames,MATCH(CellToRight,RangeWithPerformanceValues,0)) =LARGE(G3Three,1)


Problems:
1) The name lookup formula cannot account for duplicates in the smallest or largest values. So if I have one that is valued at 2 and the next smallest is also 2 it still assigns the name for the first 2 found.
2) I have no idea how to fix it up to where I do not have to replace the range name by hand. I would like to be able to just change it once as I will use that range name in numerous formulas and then paste it elsewhere and need it to use a different range name.

 

[Aladin Akyurek]

Consider A:B on Sheet1...
[TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]Name:
[/TD]
[TD="class: xl64, width: 175, bgcolor: transparent"]Performance Value:
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]A
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]B
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]C
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]D
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]E
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]5
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]F
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]6
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]G
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]P
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]6
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Define Lrow with Scope set to Sheet1 by means of Insert | Name | Define or Formulas | Name Manager as referring to:

=MATCH(9.99999999999999E+307,Sheet1!B:B)

Define Name with Scope set to Workbook as referring to:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,Sheet1!Lrow)

and Pvalue with Scope set to Workbook as referring to:

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,Sheet1!Lrow)

The processing...

1)
[TABLE="width: 440"]
<tbody>[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]Name:
[/TD]
[TD="class: xl67, width: 175, bgcolor: transparent"]Performance Value:
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 109, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl65, width: 110, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]A
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]B
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Largest Scores
[/TD]
[TD="class: xl66, bgcolor: transparent"]Top Performers
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]C
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl65, bgcolor: transparent"]G
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]D
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl65, bgcolor: transparent"]F
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]E
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl65, bgcolor: transparent"]P
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]F
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl65, bgcolor: transparent"]E
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]G
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]B
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]P
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]D
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

E1: 5 (Meaning Top 5)

E2, just enter:

=COUNTIF(Pvalue,">="&LARGE(Pvalue,E1))

E4, just enter and copy down:

=IF(ROWS($E$4:E4)<=$E$2,LARGE(Pvalue,ROWS($E$4:E4)),"")

F4, control+shift+enter, not just enter, and copy down:

=IF(ISNUMBER(E4),
  INDEX(Name,SMALL(IF(Pvalue=E4,ROW(Pvalue)-ROW(INDEX(Pvalue,1,1))+1),
   COUNTIF(E$4:E4,E4))),"")

2)
[TABLE="width: 581"]
<tbody>[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]Name:
[/TD]
[TD="class: xl67, width: 175, bgcolor: transparent"]Performance Value:
[/TD]
[TD="class: xl65, width: 26, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 30, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 109, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl65, width: 110, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 29, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 106, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl65, width: 127, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]A
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]B
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Largest Scores
[/TD]
[TD="class: xl66, bgcolor: transparent"]Top Performers
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Lowest Scores
[/TD]
[TD="class: xl66, bgcolor: transparent"]Top Performers
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]C
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl65, bgcolor: transparent"]G
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]A
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]D
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl65, bgcolor: transparent"]F
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]B
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]E
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl65, bgcolor: transparent"]P
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]D
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]F
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl65, bgcolor: transparent"]E
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl65, bgcolor: transparent"]E
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]G
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]B
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl65, bgcolor: transparent"]F
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]P
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]D
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl65, bgcolor: transparent"]P
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

H1: 5 (Meaning Top 5)

H2, control+shift+enter:

=SUM(IF(IF(ISNUMBER(Pvalue),IF(Pvalue>0,Pvalue))<=
  SMALL(IF(ISNUMBER(Pvalue),IF(Pvalue>0,Pvalue)),H1),1))

H4, control+shift+enter and copy down:

=IF(ROWS($E$4:H4)<=$E$2,
  SMALL(IF(ISNUMBER(Pvalue),IF(Pvalue>0,Pvalue)),
   ROWS($E$4:H4)),"")

I4, control+shift+enter and copy down:

=IF(ISNUMBER(H4),INDEX(Name,
  SMALL(IF(Pvalue=H4,ROW(Pvalue)-ROW(INDEX(Pvalue,1,1))+1),
   COUNTIF(H$4:H4,H4))),"")

 

[nicolelost]

Thank you. I know that was a lot of work and I a grateful for you generosity with your time.

While this works, It is far too much work to copy this down and edit it 12 times. I have 12 sets of data I am processing these same stats for.

That also leaves a tremendous amount of room for error. I would have to redefine Lrow, Pvalue, and Name over 36 times and edit 192 formulas. What I was thinking was figuring out a quick way to make these edits. I suppose it is impossible. Or maybe I am not explaining it well.

I have Office 2007 FYI

I do not need it to be pretty it is just to process some graphs on another sheet in the workbook.

In addition I am also calculating 6 other values from the same data set. I have working formulas for those, so I did not post that here.

So say I have a data set in C1:C36 that I need to make reference to numerous times.

In Cell E2 I type C1:C36.
Is there a way I can refer to the data set called out in Cell E2 within the formula that will work?

So if I needed an average,
Instead of
=AVERAGE(C1:C36)
I could say
=AVERAGE(ContentsOfCellE2)

That way I can use that in all the formulas, and when I paste the formulas to the next data set I only have to edit 2 cells that contain the sets of data instead of 16 formulas?

 

[Aladin Akyurek]

Thank you. I know that was a lot of work and I a grateful for you generosity with your time.

While this works, It is far too much work to copy this down and edit it 12 times. I have 12 sets of data I am processing these same stats for.

That also leaves a tremendous amount of room for error. I would have to redefine Lrow, Pvalue, and Name over 36 times and edit 192 formulas. What I was thinking was figuring out a quick way to make these edits. I suppose it is impossible. Or maybe I am not explaining it well.

I have Office 2007 FYI

I do not need it to be pretty it is just to process some graphs on another sheet in the workbook.

I had the impression from your original post that you needed dynamic named ranges. Here is a link to a wb which does not use named ranges. The set up (including the layout) is the same as before.

http://tinyurl.com/ba23w2n

In addition I am also calculating 6 other values from the same data set. I have working formulas for those, so I did not post that here.

So say I have a data set in C1:C36 that I need to make reference to numerous times.

In Cell E2 I type C1:C36.
Is there a way I can refer to the data set called out in Cell E2 within the formula that will work?

So if I needed an average,
Instead of
=AVERAGE(C1:C36)
I could say
=AVERAGE(ContentsOfCellE2)

That way I can use that in all the formulas, and when I paste the formulas to the next data set I only have to edit 2 cells that contain the sets of data instead of 16 formulas?

That would be:

=INDIRECT(E2)

 

[nicolelost]

Thank you so very much. I was able to modify this to work with my larger data set and it works perfectly. Your kindness and generosity is so greatly appreciated.

 

[Aladin Akyurek]

Thank you so very much. I was able to modify this to work with my larger data set and it works perfectly. Your kindness and generosity is so greatly appreciated.

You are welcome. Thanks for providing feedback.