Difference Between Functions and Formulas in Excel 20 Master

Difference Between Functions and Formulas in MS-Excel:

The function is a pre-defined operation that means it already exists and were developed by Microsoft Corporation as a part of an Excel Application. Whereas Formula is a user-defined operation that means it is like a general calculation doing by the wish of the user. 

Difference Between Functions and formulas in Excel

Function

Formula

FunctionFormula
It is a pre-defined/built-in operation.It is a user defined operation.
A function can be a formula.A formula can’t be a function.
Eg: =sum(b2:d4).Eg: =20+60+40/60 (or),
=sum(b2:d4)*256%.
Functions can be updated by the developer.Formulas are like general calculations, so you can’t be.
You can either combine formulas in the functions or functions in the formulas for calculations. But it is treated as a formula.You can either combine formulas in the functions or functions in the formulas for calculations and it is treated as a formula.
Function starts with an equal sign.You must begin formula with an equal sign.

Example of Difference Between Functions and formulas in MS-Excel

Function

Formula

Difference Between Functions vs formulas in MS-Excel

List of Examples for Functions

Sum, Average, Count, Counta, Countblank, Sumif, Sumifs, Sumproduct, Countif, Countifs, Averageif, Averageifs, Datedif, Vlookup, Hlookup, Xlookup, PMT, PPMT, PMT, Index, Match, Concatenate, If, Time, Date, Text, Yield, XMATCH, Subtotal, and so on.

List of Examples for Formulae

  1. =a1+b1+c1+f5+ss3+hj+s56
  2. =sum(a2:h12)*16/3
  3. =15554*6+455/3
  4. =max(d23:h12)/3
  5. =54*545/5.

Sum Function in Difference Between Functions and Formulas

Excel Sum Function (Pre-defined): An excel sum function adds the selected numbers in a cell or a range of cells. The value can be number, cell reference (address), range, or array. If you select any other character/s besides numbers for addition, the sum function ignores without errors by adding the numeric value.

Sum Function in Difference Between Functions and Formulas​

Sum Function in Difference Between Functions and Formulas

Syntax for Sum Function: 

=SUM(number, number2, number3…)

Or,

=SUM(first cell reference : last cell reference)

Example:

=SUM(A1:D1)

Method-1

=SUM(b2:b7)

In the above SUM function, b2:b7 is the range getting by selecting from the first cell reference b2 to the last cell reference b7.

Method-2

=SUM(b2:b7)

In the above SUM function, b2:b7 is the range. And each reference in a range is taken as given below.

B2 → It is the first cell reference getting by typing or selecting b2

:    →  Shift + ;

B7  → It is the last cell reference getting by clicking or typing b7

Method-3

Sum Function in Excel Using Range

=SUM(b2:b7)

In the above SUM function, b2:b7 is the range. And each reference in a range is taken as given below.

b2 → It is the first cell reference getting by selecting or typing b2

:b7 →  press and hold down “Shift + → or Shift + ↓”  (selecting the range till the last cell b7 is to be selected)

Method-4

Sum Function in Excel using Range:

=SUM(b2:b7)

In the above SUM function, b2:b7 is the range. And each reference in a range is taken as given below.

b2 → It is the first cell reference getting by selecting or typing b2

:b7 → Keep hold down the “Shift” key, and then click the last cell (b7).

Method-5

Auto Sum → Alt + =

=SUM(b2:b7) function is taken as describing below.

Auto Sum Function in Excel:

·        Select the range that you want to add the numbers, then press Alt + =.

(Or)

·        Select the range of cells that you want to ad its number value.

·        On the Home tab, in the Editing group, click the “AutoSum” command.

Methode-6

=sum(a3,b2:b7,d6)

Reference, Range, and Reference: In the above method, you can select the cell, range, then cell that contain numbers to ad as given below

·        a3                → select the cell that contain number.

·        b2:b7        → Select the range of cells that contain numbers.

·        d6             → select the cell that contain number.

Insert Sum Function in Difference Between Functions and Formulas

There are two ways to insert Sum function in excel, these are explained below.

  • Type the number value that you want to add, then click below the last number of your number list.
  • Click on the fx icon that appears right to the formula bar to open the insert function dialogue box.
  • Type the “Sum” to search for a function in the functions text box.
  •  Then click on “Go”, and then select the “Sum” as it is a function.
  • Now click on “OK” to open a insert function dialogue box.
Insert Sum Function in Difference Between Functions and Formulas ​

Insert Sum Function in Difference Between Functions and Formulas

  • Now select one-by-one number or select the range of cells that you want to add.
  • Lastly, click OK.

Insert Sum Function in Difference Between Functions and Formulas

Sum Formulas in Difference Between Functions and Formulas

Excel Sum Formula (User-defined): An excel sum formula adds the entered or selected numbers in a cell or a range of cells. The value can be number, cell reference (address). If you select or enter any other character besides numeric value, it shows an error.

Formula-1

= 20+60+352+4+54+54

1.      Type an equal sign {=}

2.      Start entering the number value then enter the plus sign {+} between the numbers.

Formula-2

= f5+b1+c3+d1+n3

1.      Type an equal sign {=}

2.      Start selecting the cells that contain number then enter the plus sign {+} between the cell references.

Eg. to Use Minimum and Maximum Functions

Minimum and maximum functions are used to return the smallest and highest values from the selected cells or in a range that contain numbers.

How to use minimum and maximum functions in excel

Minimum:

Returns the smallest value from the selected cells or in a range of cells that contain numbers.

Syntax:

=min( number1, number2, number3,….)

(or)

=min(first cell reference : last cell reference)

Ex: =min(b1:c6)

Follow the steps below to Return the MINIMUM value:

  1. Type the number value in a range that you want to find the smallest number.
  2. Then type =min(
  3. And then select one by one cell containing number or select the range of cell containing numbers.
  4. Now, close the brocket ), then strike the enter key to return the smallest value from the selection.
How to use minimum and maximum functions in excel.png2

Maximum:

Returns the highest value from the selected cells or in a range of cells that contain numbers.

Syntax:

=MAX( number1, number2, number3,….)

(or)

=MAX(first cell reference : last cell reference)

Ex: =MAX(b2:d6)

Follow the steps below to Return the MINIMUM value:

  1. Type the number value in a range that you want to find the highest value.
  2. Then type =min(
  3. And then select one by one cell containing number or select the range of cell containing numbers.
  4. Now, close the brocket ), then strike the enter key to return the smallest value from the selection.

Related Posts