Ask Your Question
1

multiple ifs [closed]

asked 2013-11-04 19:58:31 +0100

ugurakin gravatar image

updated 2013-11-04 21:20:15 +0100

so, i have a 4 columned spreadsheet of team player name salary and player role. what im trying to do is finding the player with most salary. i can find the highest salary with =max() function but i cannot find the other atrributes of the player who has that salary. i could do it with too many ifs but i have 350 players. so its not something to be done by ifs in my opinion. and the spreadsheet is also filtered so that means cell rows and columns are not stable. and another problem i countered was that stating the statement: "the player with the highest salary in 'a team name' is 'player name'. " and/or the statement: "highest amount of salary of players in 'team name' "

ex1: lets say Alex Rodriguez has the highest salary in the league and his salary is 25000000. i want a cell to show alex rodriguez by using this atrribute of his.

ex2. lets assume in red sox the highest salary is 30000000 and i want to show this in a cell by using this attribute.

i would say something like if a1:a30 = 'team name', max(c1:c30) but this obviously would not work.. but by this syntax what im trying to tell is: in between a1 and a30 find 'team name' s and among those find the max value of the same row but in the c column

edit: unfortunately i dont have the time for uploading but simply i can create a smaller one for you.

  1. team name - player name - salary - player role
  2. red sox - alex - 11000000- pitcher
  3. white sox - gary - 5000000 - catcher

so here is a simple example. original has 14 teams and averagely 29 players per team but the same format

i want to simply find the player name of the highest value under the salary column but since the table is filtered i cannot just find the cell of this information basically it is not stable. lets assume my mini table start from a1 so when i sort it by salary(descending) the information i want is in b2 but when i sort it by salary (acsending) its in b3...

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-11-13 05:39:49.918784

Comments

Perhaps it would be easier to provide help if you uploaded a sample calc file.

carnendil gravatar imagecarnendil ( 2013-11-04 20:53:01 +0100 )edit

2 Answers

Sort by » oldest newest most voted
2

answered 2013-11-04 23:19:20 +0100

m.a.riosv gravatar image

I think a good way can be to use Pivot Table. Select the range with data, and Menu/Data/Pivot Table/Create

image description]

edit flag offensive delete link more
1

answered 2013-11-05 03:47:28 +0100

ROSt52 gravatar image

Would sorting the entire table be a solution? If so you can either use

data > sort and continue there according what you need In this case you need to select
or
add autofilter >data > filter > autofilter which gives you even additional possibilies

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2013-11-04 19:58:31 +0100

Seen: 567 times

Last updated: Nov 05 '13