compare user defined functions and built in functions
Answers
In build
SUM
Summation, the addition of a sequence of numbers
This Sum function will add the value of anything it finds within the specified range.
"=SUM(A1:A20)" - This will give us the Sum of Column A between A1 and A20.
We can also stipulate each number manually as well, for example: =SUM(12,4,6,8) would give us the value 30. The function will total the value of all reference or values within the parenthesis.
AVERAGE
Gives you the mean average of values within the specified range.
This is not to be confused with the Mode or Median average.
"=AVERAGE(A1:A20)"
Taking the average of multiple ranges of data
In order to take the average of more than one range using a single function, first start the = AVERAGE function. After adding your first range press "," (comma) and then select the second range of values, end the function with a parenthesis (bracket) and press enter. (this is shown at the end of the GIF animation above)
MIN/MAX
MIN - Will return the smallest number found in a set of values.
MAX - Will return the highest number found in a set of values.
Can be very useful if you'd like to divide/multiply a value by the highest/lowest value found in a set of numbers. This means as you incorporate more data the number can update freely.
COUNT and COUNTA
COUNT - Counts the number of cells in a range than contain numbers
COUNTA - Counts the number of cells in a range that are not empty
Very useful for counting the number of values in a data set, especially when that data set may not start at the top of a spreadsheet.
User Defined
Excel allows you to create custom functions using VBA, called "User Defined Functions" (UDFs) that can be used the same way you would use SUM() or other built-in Excel functions. They can be especially useful for advanced mathematics or special text manipulation or date calculations prior to 1900. Many Excel add-ins provide large collections of specialized functions.