SUM, AVERAGE, COUNT, rounding and percentages: the calculations behind almost every spreadsheet.
10 articles
An Excel absolute reference uses dollar signs, like $F$1, to lock a cell so it doesn't change when you copy or fill a formula. Use it for a value every row shares, such as a tax rate: =B2*(1+$F$1). Press F4 while editing to add the $ signs. $F1 locks only the column; F$1 locks only the row.
The Excel AutoSum shortcut is Alt+= on Windows and ⌘+Shift+T on a Mac. Click the empty cell below a column of numbers, press the shortcut, and Excel writes a SUM formula with a guessed range, such as =SUM(C2:C5). Check the highlighted range, then press Enter to accept it.
AVERAGE returns the mean of a range, MIN returns the smallest number and MAX returns the largest. All three take the same input: =AVERAGE(B2:B6), =MIN(B2:B6) and =MAX(B2:B6). They skip empty cells and text but count zeros, and =MAX(B2:B6)-MIN(B2:B6) shows how spread out the values are.
The Excel COUNT function counts cells that contain numbers, such as =COUNT(B2:B7). COUNTA counts every cell that isn't empty, numbers and text alike, and COUNTBLANK counts the empty cells. Use =COUNTA(B2:B7)-COUNT(B2:B7) to find values that are filled in but aren't real numbers.
The Excel LARGE function returns the k-th largest number in a range: =LARGE(B2:B9,2) gives the second highest. SMALL works the same way from the bottom, so =SMALL(B2:B9,2) gives the second lowest. To total the top 3, add three LARGE results with k = 1, 2 and 3.
Use =ROUND(number, digits) to round to a set number of decimal places, so =ROUND(B2,2) turns 3.4567 into 3.46. ROUNDUP always rounds away from zero and ROUNDDOWN always rounds towards zero, using the same two arguments. Unlike number formatting, these functions change the stored value that later formulas use.
MEDIAN returns the middle value of a range once it is sorted, and MODE returns the value that appears most often. Type =MEDIAN(B2:B8) or =MODE(D2:D11). Use MEDIAN instead of AVERAGE when one extreme value, like a founder's salary, would pull the average away from what is typical. MODE returns #N/A if nothing repeats.
The percent change formula in Excel is =(new-old)/old. If last year's sales are in B2 and this year's in C2, type =(C2-B2)/B2, then press Ctrl+Shift+% to show it as a percentage. A positive result is an increase and a negative result is a decrease. Always divide by the old value.
The Excel SUM function adds numbers. Type =SUM( then the cells to add, then ), for example =SUM(B2:B5) to add B2 through B5. A colon makes a range, and a comma adds separate cells or ranges, like =SUM(B2,B4:B5). SUM skips text and empty cells, so it doesn't break on gaps.
When Excel SUM is not working, the cause is usually one of three things: the range misses a row, some numbers are stored as text (SUM skips them), or the total cell is inside its own range, creating a circular reference. Check the range, compare =COUNTA and =COUNT on it, and end the range above the total.