Formula and function in excel – data validation excel – ms excel

Formula and functions, types and Data Validation

BBA | BBA-TT | BHCM

Formula and functions and their types are given below:

Formula

The formula is a mechanism to process numeric data to give a specified result. The results are always formula oriented. If we are confused in the formula, we cannot get the correct answer. The formula is also defined as the sequential structure of the operators which are used to perform many types of education.

Functions

Ms-excel has set of pre-formula stored on the library which is called functions. Functions differ from the formula, in that case, we supply the values but not operators.

For e.g. we have to find an average of five numbers A1, B1, C1, D1, E1 then the functions for this calculation is = Average (A1: E1).

Some commonly used functions in Ms-excel.

• Sum (cell range): To calculate the sum of cells.
• Product (cell range): To calculate the product or multiple of cells.
• Average (cell range): To calculate the average of cells.
• Max (cell range): To find the maximum value of the cells.
• Min (cell range): To find the minimum value of the cells.
• Sqrt (cell or value): To calculate the squire root value of the cell or value.
• Log (cell or value): To find log value of the cells.
• Count (cell range): To count the number of numeric value.
• Counta (cell range): To count the number of data containing cells.
• Now ( ): To find the current date and time.
• Today ( ): To find the current date only.
• Count if (range, criteria or condition): To count the number of cells if the condition is met.
• Sum if (range, criteria or condition): To calculate the sum of cells if the criteria are met.
• AND (multiple criteria or multiple conditions): To check condition or criteria if the condition is more than one with AND logic.
• OR (multiple criteria or multiple conditions): To check condition or criteria if the condition is more than one with OR logic.
• NOT (<>): It gives the not value or opposite value.

 

  Different types of formula and functions

1. Date and time function

It includes

Today: Returns the serial number of today’s sheet.

Syntax: TODAY ( )

 

Date: Returns the serial number of a particular date.

Syntax: DATE (year, month, day)

 

Day: Convert a serial number to a day of the month.

Syntax: DAY (serial_number)

 

Now: Returns the serial number of the current date and time.

Syntax: NOW ( )

 

Time: Returns the serial number of a particular time.

Syntax: TIME (hour, minute, second)

 

2. Math and trigonometry function

ABS: The ABS function returns the absolute value of a number.

Syntax: ABS (number)

 

SQRT (number)

Syntax: SQRT (number)

 

SUM (cell-range)

Syntax: (number1, number2 ………)

Also,

• SUMIF (range, criteria, sum-range)
• SUMPRODUCT (ARAY1, ARAY2, ……)
• PRODUCT (number1, number2, ……..)
• PI ( )
• SIN, COS, TAN (number)
• LOG 10 (number)

 

3. Random and rounding number function

4. Logical function

IF

Syntax: IF (logical_test, value_if_true, value_if_false)

 

AND

Syntax: AND (logical1, logical2…)

 

OR

Syntax: OR (logical1, logical2…)

 

NOT

Syntax: NOT (logical)

 

5. Text and information function

Text

• CLEAN (text)
• CONCATENATE (text1, text2…)
• TRIM (text)

Information

• INFO (type_text)
• ISBLANK (value)
• ISEVEN (number)
• ISLOGICAL (value)
• ISNUMBER (value)
• ISODD (number)
• ISTEXT (value)

6. Count and database function

• DATABASE (database, field, criteria)
• DCOUNT (database, field, criteria)
• DMAX (database, field, criteria)
• DMIN (database, field, criteria)
• PRODUCT (database, field, criteria)
• DSUM (database, field, criteria)

7. Financial function

FV: Returns a future value of an investment.

Syntax: FV (rate, nPer, Pmt, Pv, type)

 

PMT: Returns the periodic payment for an annuity based on constant payment and a constant interest rate.

Syntax: PMT (rate, nper, PV,fv, type)

 

PV: Returns the present value.

Syntax: PV (rate, nper, pmt, fV type)

8. Lookup and reference function

• HLOOKUP (lookup_value, table_arry, column_index_num, range_lookup)
• VLOOKUP (lookup_value, table_arry, column_index_num, range_lookup)

9. Statistical function

• Average (number1, number2…)
• VAR (number1, number2…)
• MIN (number1, number2…)
• MAX (number1, number2…)

 

 

Data validation

Data validation is used for making the data error free in Ms-excel. Data validation is a feature available in Ms-excel. It is a technique by which we can control the input by providing the dropdown list to the user.

Steps to create data validation:

1. At first, select the cell.
2. Click on the data menu.
3. Then, click on data validation.
4. Then, click on the data validation option.

On the setting tab

• In the allow list, choose desire option like decimal.
• In the data list, click between.
• Enter the minimum and maximum value.

On the input message

• Mark in input message when a cell is selected.
• Enter a title.
• Enter an input message.

On the error alert

• Mark in show alert after invalid data is entered.
• Choose style.
• Enter a title.
• Enter an error message

For offline read download PDF file Formulaandfunctions

Related articles

Features and definition of Ms-excel