Issues with Array Functions I am lost overcoming

nicolelost

New Member
Joined
Jan 13, 2013
Messages
3
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.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,154
Consider A:B on Sheet1...
Name:
Performance Value:
A
1
B
2
C
0
D
2
E
5
F
6
G
8
P
6

<tbody>
</tbody>

Define Lrow with Scope set to Sheet1 by means of Insert | Name | Define or Formulas | Name Manager as referring to:
Rich (BB code):
=MATCH(9.99999999999999E+307,Sheet1!B:B)
Define Name with Scope set to Workbook as referring to:
Rich (BB code):
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,Sheet1!Lrow)
and Pvalue with Scope set to Workbook as referring to:
Rich (BB code):
=Sheet1!$B$2:INDEX(Sheet1!$B:$B,Sheet1!Lrow)
The processing...

1)
Name:
Performance Value:
5
A
1
6
B
2
Largest Scores
Top Performers
C
0
8
G
D
2
6
F
E
5
6
P
F
6
5
E
G
8
2
B
P
6
2
D

<tbody>
</tbody>

E1: 5 (Meaning Top 5)

E2, just enter:
Rich (BB code):
=COUNTIF(Pvalue,">="&LARGE(Pvalue,E1))
E4, just enter and copy down:
Rich (BB code):
=IF(ROWS($E$4:E4)<=$E$2,LARGE(Pvalue,ROWS($E$4:E4)),"")
F4, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=IF(ISNUMBER(E4),
  INDEX(Name,SMALL(IF(Pvalue=E4,ROW(Pvalue)-ROW(INDEX(Pvalue,1,1))+1),
   COUNTIF(E$4:E4,E4))),"")
2)
Name:
Performance Value:
5
5
A
1
6
6
B
2
Largest Scores
Top Performers
Lowest Scores
Top Performers
C
0
8
G
1
A
D
2
6
F
2
B
E
5
6
P
2
D
F
6
5
E
5
E
G
8
2
B
6
F
P
6
2
D
6
P

<tbody>
</tbody>

H1: 5 (Meaning Top 5)

H2, control+shift+enter:
Rich (BB code):
=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:
Rich (BB code):
=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:
Rich (BB code):
=IF(ISNUMBER(H4),INDEX(Name,
  SMALL(IF(Pvalue=H4,ROW(Pvalue)-ROW(INDEX(Pvalue,1,1))+1),
   COUNTIF(H$4:H4,H4))),"")
 

nicolelost

New Member
Joined
Jan 13, 2013
Messages
3
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,154
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

New Member
Joined
Jan 13, 2013
Messages
3
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.
 

Forum statistics

Threads
1,089,210
Messages
5,406,868
Members
403,109
Latest member
gamer527

This Week's Hot Topics

Top