Using one column range to get values from another column range

**wsickles** · 03-09-2011, 03:42 PM

I am working on a spreadsheet to project the value of baseball stats and have run into an issue. I need to grab the nth lowest value from column A, but only using the top 180 ranked players from column B. My issue is that the spreadsheet consists of 1000+ hitters and if I sort by another column then the top 180 will change. So basicly:

Runs scored = A2:A1001
Rank = B2:B1001

Out of the top 180 ranked hitters, I need the lowest amount of runs a player scored returned.

Please let me know if you have any questions and thanks ahead of time for your help!

**NBVC** · 03-09-2011, 03:53 PM

So are the rankings in column B from highest (1) to lowest (1000)?

If so, something like:
=SMALL(IF(B2:B1001<=180,A2:A1001),n)

where n is the k for the SMALL function (i.e. the nth smallest you want).

confirmed with CTRL+SHIFT+ENTER not just ENTER

**wsickles** · 03-09-2011, 04:17 PM

The rank was not sequential, but there is no reason it cant be. I reranked them 1,2,3... then pluged in your formula. Didn't work at first, but then changed SMALL to LARGE and it seems to have worked. To Test, i just sorted by the rank column, and did

=MIN(A2:A181)

and it returned teh same value as

=LARGE(IF(B2:B1001,A2:A1001),180)

Thanks for your help!

**wsickles** · 03-09-2011, 04:50 PM

Looks like I need a little more help... I assumed once I knew how to do that, I could then just apply the same logic to get the AVERAGE, STDEVP, and MAX but I cant seem to figure it out. What do I need to do differently to use those three functions?

**NBVC** · 03-09-2011, 04:59 PM

As long as this part: IF(B2:B1001<=180,A2:A1001) is in there, they should be similar

e.g.

=AVERAGE(IF(B2:B1001<=180,A2:A1001))

=STDEVP(IF(B2:B1001<=180,A2:A1001))

=MAX(IF(B2:B1001<=180,A2:A1001))

each confirmed with CTRL+SHIF+ENTER

**wsickles** · 03-09-2011, 07:53 PM

The code for MAX works, but for AVERAGE it seems that it is taking the sum of the top 180, but then deviding by the entire range (1000). I assume it is doing something similar for STDEVP. Do you have an idea of how to avoid this? Thanks!

**wsickles** · 03-09-2011, 08:43 PM

Another issue I am now having when doing:

=LARGE(IF(B2:B1001,A2:A1001),180)

is that if a value in the range A2:A1001 is negative it will return a value of 0, and not the negative number.

**wsickles** · 03-09-2011, 09:45 PM

I believe I have found the issue with AVERAGE and STDEVP, My functions now look like this:

=AVERAGE(IF(HittingRank<=$Q$7,IF(ISNUMBER(1/HittingRunsAvg),HittingRunsAvg)))

and

=STDEVP(IF(HittingRank<=$Q$7,IF(ISNUMBER(1/HittingRunsAvg),HittingRunsAvg)))

Its not perfect since there are one or two cells that contain a zero, but its close enough for me. I am still having an issue with the LARGE function when the range contains a negative number.

=LARGE(IF(HittingRank<=$Q$7,HittingBA),$Q$7)

the 24th cell in HittingBA contains the first negative value, if I set Q7 to 23 it returns a value, anything over 23 and it displays zero.

**wsickles** · 03-10-2011, 08:20 PM

Attaching my spreadsheet. The Values I am having trouble with are the ones highlighted in red on the MATH tab. I want to modify these cells so they perform the same function, but only for the top number of players (set in Q7).

**NBVC** · 03-11-2011, 09:26 AM

Firstly, your named ranges cover more than the data ranges... so if you want to keep the ranges dynamic, so that you can add/remove players, you need to redefine each named range.

E.g.

For HittingRank define the Refers To field as:

='        Hitting        '!$T$2:INDEX('        Hitting        '!$T$2:$T$29999,COUNTA('        Hitting        '!$E$2:$E$29999))

and for HittingBAAVG it would be:

='        Hitting        '!$AH$2:INDEX('        Hitting        '!$AH$2:$AH$29999,COUNTA('        Hitting        '!$E$2:$E$29999))

so basically leaving the COUNTA(' Hitting '!$E$2:$E$29999) as is for each, but changing the other parts to suit the column.

Then you can use formulas like in:

C10:

=MIN(IF(HittingRank<=$Q$7,HittingBA))

confirmed with CTRL+SHIFT+ENTER

D10:

=AVERAGEIF(HittingRank,"<="&$Q$7,HittingBAAVG)

confirmed with ENTER only

E10:

=SUMIF(HittingRank,"<="&$Q$7,HittingHits)/SUMIF(HittingRank,"<="&$Q$7,HittingAB)

confirmed with ENTER only

F10:

=STDEVP(IF(HittingRank<=$Q$7,HittingBAAVG))

confirmed with CTRL+SHIFT+ENTER

As far as the LARGE function is concerned:

You have in C5:

=LARGE(IF(HittingRank<=$Q$7,HittingRuns),$Q$7)

this finds the 180th largest value in HittingRuns where HittingRank is less than or equal to 180. Is that really what you want?

I think that is the same as