More Complex Formulas in Excel
Sum up Columns and Rows
SUM an Array
You can sum not only column or rows but full arrays like following.
- To make the previous table, first enter the various labels (John, ..., apples, ..., Total, ...).
- Then in cell E8, type =sum(
- Automatically, Excel will recognize that you are willing to do the sum of something and will give you control of the cursor.
- Move you cursor to the cell B3 and drag it to the cell E5.
- Then release the button and press ENTER (you can also click on the little green tick).
- The number 286 will appear !!! which is the total number of fruits....
Finding Minimums and Maximums in Arrays
Excel can also be used to sort out data, to find "things" out of a bunch of numbers.
Finding the maximum and the minimum is sometimes very useful. Here how.
The formula is enterered the same way as previously described. You can select an array just a row or a column.
Same as per previous formulas, the references are entered as following:
To make an array, type Reference1:Reference2
To average various values, separate them with comma.
Here also you can enter other elements in between the brakets.
You can enter the array only as per '=min(A1:A8)
or you can enter '=max(A1:A8, 15) which would give you the maximum between the biggest number in the table and 15,
or you could enter also '=max(A1:A8,sum(A1:A8)/35))
tip: avoid too big formulas, they tend to be undreadable. Try splitting them in pieves if possible.
- 101 AVERAGE: average
- 102 COUNT: count the total number of numerical cells
- 103 COUNTA: count the total number of cells
- 104 MAX: maximum
- 105 MIN: minimum
- 106 PRODUCT: product of all the numbers
- 107 STDEV: standard deviation
- 108 STDEVP: standard deviation for a population
- 109 SUM: sum
- 110 VAR: variance
- 111 VARP: variance for a population (more accurate)