[HW 2.2c] Conditional Maximums, Minimums, and Percentiles

 

 

Here is a dataset of salaries by player in Major League Baseball from 2005.  Please summarize the data by team, producing a table in the following format, with the numbers populated.   For each of the 16 teams in the data set, please show the maximum salary, the 75th percentile salary, the median salary, the 25th percentile salary, the minimum salary, and the range between the 75th percentile and the 25th percentile salaries.

 

 

baseballchart1b

Please repeat the exercise and report the same metrics, but by position, rather than by team.

NLBB.salaries.2005

To complete this assignment, it will likely be necessary to use array formulas.  Here are a few examples of why and how you might write such a formula.  In each case, they would be entered as an array, by pushing control+shift+enter, and then copying the formula to any other cells that require it.

  • Sum Of Absolute Values – You can sum a range of number treating them all as positive using the ABS function.  =SUM(ABS(A1:A10))
  • Sum Ignoring Errors – Normally, if there is an error in a cell, the SUM function will return that error. The following formula will ignore the error values.  =SUM(IF(ISERROR(A1:A10),0,A1:A10))
  • Sum Of Integer Portion Only – This formula will sum only the integer portion of the numbers in A1:A10. The fractional portion is discarded.  =SUM(TRUNC(A1:A5))
  • Average Ignoring Zeros – This formula will ignore zero values in an AVERAGE function.  =AVERAGE(IF(A1:A10<>0,A1:A10,FALSE))

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: