There is a matching Excel file that you can use to follow along and practice, which you can download from here.
This is a more sophisticated way to rank your records and to account for ties.
=RANK(This Number, $Start Range$:$End Range$, Order)
This Number should be the cell where your data starts.
Start Range should be the cell where your data starts. Anchor with dollar signs.
End Range should be the last cell of your data. Anchor with dollar signs.
Order is either a 1 (smallest value will get assigned #1) or a 0 (largest value will get assigned #1).
Example: =RANK(B2,$B$2:$B$100,1)
Returns the rank — or relative standing - within the dataset as a percentage. So for example, if you had a list of the payrolls for all of the Major League Baseball Teams, you could do a percent rank on the payroll to find out which team (the Yankees, of course) have the greatest percentage of the total.
=PERCENTRANK(array, x, significance)
Array: The range of data that you want to compare each item to
X: the value for which you want to know the percent rank
Significance: an optional value that allows you to set the number of digits
Example:
=PERCENTRANK($a$2:$a$30, a2, 2)
Also check out PERCENTILE and QUARTILE functions
=ROUND(cell, num_digits): For this one you tell it which cell to do its work on and then the number decimals you want to round to. For the num_digits you can use something like this.
These examples show how it would round the number 1234.5678
0 puts it to the nearest integer (1235)
1 goes to one decimal place (1234.6)
-1 goes to the nearest tenth (1230)
-2 to the nearest hundreth (1200)
-3 to the nearest thousandth. (1000)
Are you sick of typing cell addresses? You can set up your worksheet so that the headers you’ve typed for each column can be used as cell addresses in your formulas instead. Here’s how it works…. First thing to do is make sure your headers are all filled out, that they are single words (no spaces, no punctuation), and that they are stored on the first line of your worksheet. Next, highlight all of your data (my favorite way to do this is to put your cursor somewhere in your dataset and hit Control-Shift-Asterisk).
Go to the Formulas ribbon and look for “Name Manager” and a button that says “Create from selection.” In the dialog box that comes up make sure that ONLY the “top row” choice is selected.
You can click on “Name Manager” in the formulas ribbon to see which names you’ve already defined (and delete any you don’t want)
#DIV/0! : This almost always means the formula is trying to divide by zero or a cell that is blank. So to fix this, first check to make sure that your underlying data is correct. In many cases, you will have zeros. For example, the number of minority students in some schools in Minnesota might be zero, so I have to use an IF statement whenever trying to calculate the percentage of minority students. Here’s how I get around the error, assuming the number of minority students is in cell B2 and the total enrollment is in C2. If the number of minority students is greater than zero, it does the math. Otherwise it puts zero in my field.
=if(b2>0, b2/c2, 0)
#N/A: This is short for “not available” and it usually means the formula couldn’t return a legitimate result. Usually see this when you use an inappropriate argument or omit a required argument. Hlookup and Vlookup return this if the lookup value is smaller than the first value in the lookup range.
#NAME?: You see this when Excel doesn’t recognize a name you used in a formula or when it interprets text within the formula as an undefined name. In other words, you’ve probably got a typo in your formula.
#NUM!: This means there’s a problem with a number in your formula (usually when you’re using a math formula).
#REF!: Your formula contains an invalid cell reference. For example, it might be referring to a blank cell or to a cell that has since been deleted.
#VALUE!: Means you’ve used an inappropriate argument in a function. This is most often caused by using the wrong data type.