change the formula by changing contents of cell

[AJSloss]

I have a table that ranks a list of players by their statistics, for example,
when I change cell B2 to "walks", my formula (large) lists the player with
the most walks in cell B4, 2nd most in B5, 3rd in B6... that works well
because more walks are good. But, when I change cell B2 to "strikeouts" I
want it to list the player with the fewest number of strikeouts in B4, next
fewest in B5 and so on.

Is there a way to change the "large" function to the "small" function within
the formula?

I have set up a helper cell (C2) that changes from "large" when I have
"walks" in B2 to "small" when I have "strikeouts" in B2.

 

[Pete_UK]

Maybe you can incorporate a multiply in your formula:

* 1 for when you select walks, and

* -1 for when you select strikeouts.

If you supply your formula then we can see how this could be included.

Hope this helps.

Pete

 

[Eduardo]

Hi,
You can use an if statement

=if(B2="Walks",your large formula,if(B2="strikeouts",your small formula))

 

[AJSloss]

This does work, but my formula is rather lengthy as it is. I was hoping to
find a shorter alternative so that I can use it (changing the function used
based on what I type in a cell) for a number of other situations.

 

[AJSloss]

{=IFERROR(INDEX(Batters, MATCH(LARGE(IF(INDIRECT(B$1)="x", INDEX(Batters, ,
MATCH(B$2, INDEX(Batters, 1, ), 0)), 0), $A4), INDEX(Batters, , MATCH(B$2,
INDEX(Batters, 1, ), 0)), 0), 1), "")}

-Batters is the large table from where I get the data.

-Cell B$1 is a position indicator that I've set up so that I can sort
through only first basemen, second basemen, etc.

-Cell B$2 is the stat indicator (walks, strikeouts, hr...)

-Cell $a4 is the rank (so that when I copy down, $a4 is 1, $a5 is 2, $a6 is
3...)

So when reading this way, in cell B4 the guy with the most of a stat is
listed, in B5 the second most and so on. If a guy does not fit the position
parameter, then his stat is counted as a zero and he won't show up at the top
of the leaderboard.

What I would like, is when I change the stat indicator (B$2) to one in which
in which I want a small number (strikeouts), the formula changes from "large"
to "small" and in the if portion, the "0" for a false statement changes to a
"1000" (so that if a player doesn't meet the position parameter, his stat is
counted as 1000 and he won't show up at the top of the leaderboard).

As I said before, I've created helper cells so that when I change the stat
in B$2, a "large" or "small" will come up in one cell, along with a "0" or
"1000" in another cell. I was hoping to incorporate these into the original
formula (tried using an indirect-type function), but was unsuccessful.

Thanks for the help so far (and in the future).