Using one column range to get values from another column range

[wsickles]

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]

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]

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]

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]

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]

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]

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]

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

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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]

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]

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:

Please Login or Register  to view this content.

and for HittingBAAVG it would be:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER

D10:

Please Login or Register  to view this content.

confirmed with ENTER only

E10:

Please Login or Register  to view this content.

confirmed with ENTER only

F10:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER

As far as the LARGE function is concerned:

You have in C5:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.