## Formulas in EXCEL

Microsoft Excel offers several built-in functions that can be used as formulas in Excel worksheets. It displays the categories of functions available in the Insert Function window, as seen in the image below:

## Usage of Formula in an Excel Sheet

Just like how mathematical formulas help us tackle easy and quick calculations in math, the formulas offered by Ms. Excel help us maintain good databases with data and formulas that can be auto-calculated and stored for ease of use and reference.

### Example of an Excel Formula:

After typing the “SUM" formula in the formula bar or any cell, (what you type in the cell starting with an equal (=) sign will display on the formula bar), if we press the enter key, click on the check mark on the formula bar, or select some other cell, we can see the auto-calculated answer.

When the formula bar or the cell is not in edit mode, the check and cross marks on the formula bar are disabled.

## Usage of Formulas as Functions in VBA

Excel VBA offers most of these formulas as built-in functions that can be easily used in our code to achieve the same result. These are called Worksheetfunctions.

## What is a WorksheetFunction?

The application object of MS Excel has a method called “WorksheetFunction." It can give access to most of the standard built-in functions that are available as “Formulas" in Excel.

`Application.<function call> `can be used straight away to access the functions. However, using `Application.worksheetfunction.<function call>` can help the programmer with Intellisense that can guide him through the parameters to be used to finish typing the function.

## A List of Worksheet Functions – VBA

Here is a list of some worksheet functions that are commonly used.

### List of Logical Functions

 S.no Function Description Syntax Example Output 1 AND Checks whether all conditions are met. Possible output result can be either TRUE or FALSE. Worksheetfunction.And(logic1,logic2) Worksheetfunction.And(5>3,4<6) TRUE 2 IF If conditions are met, do something (second parameter), if not, do something else (third parameter) . IF(,, ) IF(2<6,”2 is less”,”6 is less”) “2 is less” 3 IFERROR This is a combination of IF and ISERROR. IF(ISERROR( < calculation> ), 0, < calculation result >) IF(ISERROR(2/0),0,2/0) 0 4 OR Checks whether any one of the conditions are met. Possible output result can be either TRUE or FALSE. Worksheetfunction.If(logic1,logic2) Worksheetfunction.If(5>3,14<6) TRUE

### List of Lookup and Reference Functions

 S.no Function Description Syntax 1 CHOOSE Chooses a value from a list based on its index number and the index number provided in the parameter. choose( , value1, value2) 2 HLOOKUP Looks up a value in the first row of a table and returns a value. Hlookup( , , , ) 3 INDEX Returns a value based on its column number and row numbers. INDEX (array, , <[col num]>, <[area num]>) 4 LOOKUP Looks up values either horizontally (in rows) or vertically (in columns) . Lookup( < lookup value>, , ) 5 MATCH Searches for a value in a list and returns its position. MATCH (, , <[match type]>) 6 TRANSPOSE Changes the orientation of a range of cells. Vertical to horizontal and vice versa. Transpose() 7 VLOOKUP Looks up a value in the first column of a table and returns a value. Vlookup( , , , )

### Date and Time Functions

A valid date here means `IsDate(<valid date>)` returns the Boolean value “TRUE"

 S.no Function Description Syntax 1 DATE Returns a date from a combination of year, month, and day. DATE(Year, month, day) 2 DATEVALUE Converts a date that is stored as text into a valid date (in acceptable data format). datevalue(< date text> ) 3 DAY Returns the day as a number (1-31) from a valid date. day( ) 4 DAYS360 Returns difference (in days) between 2 valid dates in a 360-day year. DAYS360( < start date> , , 5 EDATE Returns a valid date, “n” months away from a valid start date. EDATE(, ) 6 EOMONTH Returns the last day of the month, “n” months away from a valid date. eomonth(, ) 7 HOUR Returns the hour as a number (0-23) from a valid time. hour(< serial number>) 8 MINUTE Returns the minute as a number (0-59) from a valid time. minute() 9 MONTH Returns the month as a number (1-12) from a valid date. month() 10 NETWORKDAYS Returns the number of working days between 2 valid dates. networkdays(, , []) 11 NETWORKDAYS.INTL Returns the number of working days between 2 valid dates. By default , the weekends are excluded and also provides an option to add a list of holidays that needs to be excluded from the count. networkdays.intl( , , [], []) 12 NOW Returns the current date and time as in the system. now() 13 SECOND Returns the second as a number (0-59) from a valid time. second() 14 TIME Returns the time from a valid combination of hour, minute, and second. time(, , ) 15 TIMEVALUE Converts a time stored as text into a valid time. timevalue(

### Engineering Functions

 S.no Function Description Syntax 1 CONVERT Convert a number or value from one unit to another. Eg: Cm to inches, feet to cm, m to cm, kg to g . convert(, < from unit> , < to unit > )

### Financial Functions

 S.no Function Description Syntax 1 FV Calculates and returns the future value of an investment. fv(, , , [pv], []) 2 PV Calculates and returns the present value of an investment. fv(, , , [fv], []) 3 NPER Calculates and returns the total number of payment periods in an investment. nper(, , [], [type]) 4 PMT Calculates and returns the payment amount in an investment. pmt(, , , [], []) 5 RATE Calculates and returns the interest rate for an investment. rate(, ,, [], [], []) 6 NPV Calculates and returns the net present value of an investment. npv(, , [], [],….., []) 7 IRR Calculates and returns the internal rate of return for a set of periodic CFs. irr( , [] ) 8 XIRR Calculates and returns the internal rate of return for a set of non-periodic CFs. xirr(, , [] ) 9 PRICE Calculates and returns the price of a bond. price(, , , , , , []) 10 INTRATE Returns the interest rate of a completely invested security. intrate(, , , , , [])

### Information Functions

 S.no Function Description Syntax 1 ISERR Tests if cell value is an error, ignores #N/A. Returns boolean value TRUE/FALSE iserr( < value > ) 2 ISERROR Tests if cell value is an error. Returns a boolean value TRUE/FALSE iserror( < value> ) 3 ISEVEN Tests if cell value is even. Returns a boolean value TRUE/FALSE iseven( < number> ) 4 ISLOGICAL Tests if cell is logical (TRUE or FALSE). Returns a boolean value TRUE/FALSE islogical( < value > ) 5 ISNA Tests if cell value is #N/A. Returns a boolean value TRUE/FALSE isna( < value > ) 6 ISNONTEXT Tests if cell is not text (blank cells are not text). Returns a boolean value TRUE/FALSE isnontext( < value > ) 7 ISNUMBER Tests if cell is a number. Returns a boolean value TRUE/FALSE isnumber( < value > ) 8 ISODD Tests if cell value is odd. Returns a boolean value TRUE/FALSE isodd( < number> ) 9 ISTEXT Tests if cell is text. Returns a boolean value TRUE/FALSE istext( < value > ) 10 TYPE Returns the type of value stored in a cell. type( < value > )

### Math Functions

 S.no Function Description Syntax 1 ABS Calculates and returns the absolute value of a number. abs( < number> ) 2 AGGREGATE It defines and performs calculations for a database or a list. AGGREGATE(, , , [k] > 3 CEILING Rounds a number up, to the nearest specified multiple and returns the value. ceiling( < number > , ) 4 COS Returns the cosine value of an angle. cos( < number > ) 5 DEGREES Converts radians to degrees and returns the value. degrees( < angle> ) 6 DSUM Sums up database records that meet certain criteria. dsum( , , ) 7 EVEN Rounds to the nearest even integer and returns the value. even( < number > ) 8 EXP Calculates the exponential value for a given number and returns the value. exp( < number > ) 9 FACT Returns the factorial value. fact( ) 10 FLOOR Rounds a number down to the nearest specified multiple. floor( , ) 11 GCD Returns the greatest common divisor. gcd( < number 1> , [], [],……[]) 12 INT Rounds a number down to the nearest integer. int( < number > ) 13 LCM Returns the least common multiple. lcm( < number 1> , [], [],……[]) 14 LN Returns the natural logarithm of a number. LN( < number > ) 15 LOG Returns the logarithm of a number to a specified base. log( , []) 16 LOG10 Returns the base-10 logarithm of a number. log10( < number > ) 17 MROUND Rounds a number to a specified multiple. mround( < number > , ) 18 ODD Rounds to the nearest odd integer. odd( < number > ) 19 PI The value of PI. PI() 20 POWER Calculates a number raised to a power. power( < number> , ) 21 PRODUCT Multiplies an array of numbers. product( < number 1> , [], [],……[]) 22 QUOTIENT Returns the integer result of division. quotient( < numerator> , ) 23 RADIANS Converts an angle into radians. radians( < angle > ) 24 RANDBETWEEN Calculates a random number between two numbers. randbetween( < bottom> , ) 25 ROUND Rounds a number to a specified number of digits (specified in a parameter). round( < number> , ) 26 ROUNDDOWN Rounds a number down (towards zero). ROUNDDOWN( , ) 27 ROUNDUP Rounds a number up (away from zero). ROUNDUP( , ) 28 SIN Returns the sine value of an angle. sin( < number > ) 29 SUBTOTAL Returns a statistical summary for a series of data. subtotal( < function num>, , [], [],…..[]) 30 SUM Adds numbers together and returns the total value. sum( < number 1> , [], [],……[]) 31 SUMIF Sums numbers that meet a criteria. sumif( , , []) 32 SUMIFS Sums numbers that meet multiple criteria. sumifs(< sum range>, , < criteria 1> , [, ],[, ],….[, ]) 33 SUMPRODUCT Multiplies arrays of numbers and adds up the resultant array. sumproduct( < array 1> , [], [], [],….[]) 34 TAN Returns the tangent value of an angle. tan( < number > )

### Statistical Functions

 S.no Function Description Syntax 1 AVERAGE Averages given numbers. average( < number 1> , [], [],……[]) 2 AVERAGEIF Averages given numbers that meet a criteria. averageif( , , []) 3 AVERAGEIFS Averages given numbers that meet multiple criteria. averageifs(< average range>, , < criteria 1> , [, ],[, ],….[, ]) 4 CORREL Calculates and returns the correlation of two series. correl( , ) 5 COUNT Counts and returns the number of cells that contain a specific value. count( , [], [], [< value 4>]….[] ) 6 COUNTA Counts and returns the number of cells that are non-blank. COUNTA( , [], [], [< value 4>]….[] ) 7 COUNTBLANK Counts and returns the number of cells that are blank. countblank( < range > ) 8 COUNTIF Counts and returns the number of cells that meet a criteria. countif( , ) 9 COUNTIFS Counts and returns the number of cells that meet multiple criteria. countifs( , < criteria 1> , [, ],[, ],….[, ]) 10 FORECAST Predict future y-values from a linear trend line. forecast( , , ) 11 FREQUENCY Counts values that fall within specified ranges. frequency(< data array> , ) 12 GROWTH Calculates Y values based on exponential growth. growth( , [], [], [const]) 13 INTERCEPT Calculates the Y intercept for the “best-fit” line. intercept(, ) 14 LARGE Returns the kth largest value. large( , k) 15 LINEST Returns statistics about a trendline. linest( < known ys> , [], [] , [] ) 16 MAX Returns the largest number from the given series. max( < number 1> , [], [],……[]) 17 MEDIAN Returns the median number from the given series. median( < number 1> , [], [],……[]) 18 MIN Returns the smallest number from the given series. min( < number 1> , [], [],……[]) 19 MODE Returns the most common number in the given series. mode( < number 1> , [], [],……[]) 20 PERCENTILE Returns the kth percentile. percentile( , ) 21 PERCENTILE.INC Returns the kth percentile where k is inclusive. percentile.inc( , ) 22 PERCENTILE.EXC Returns the kth percentile where k is exclusive. percentile.exc( , ) 23 QUARTILE Returns the specified quartile value. quartile( < array> , ) 24 QUARTILE.INC Returns the specified quartile value (Inclusive) . quartile.inc( , ) 25 QUARTILE.EXC Returns the specified quartile value (Exclusive). quartile.exc( , ) 26 RANK Returns the rank of a number within a series. rank(< number> , , []) 27 RANK.AVG Returns the rank of a number within a series. (Averages). rank.avg (< number> , , []) 28 RANK.EQ Returns the rank of a number within a series. (Top Rank). rank.eq (< number> , , []) 29 SLOPE Calculates and returns the value of slope from linear regression. slope( < known ys> , ) 30 SMALL Returns the smallest value in a series of numbers. small( < array>, ) 31 STDEV Calculates and returns the standard deviation. stdev( < number 1> , [], [],……[]) 32 STDEV.P Calculates and returns the Standard deviation of an entire population. stdev.p( < number 1> , [], [],……[]) 33 STDEV.S Calculates and returns the Standard deviation of a sample. stdev.s( < number 1> , [], [],……[]) 34 STDEVP Calculates and returns the Standard deviation of an entire population stdevp( < number 1> , [], [],……[]) 35 TREND Calculates and returns Y values based on a trendline. trend( , [], [], [const])

### String (or) Text Functions

 S.no Function Description Syntax 1 CLEAN Removes all  characters that are not printable. clean( ) 2 DOLLAR Adds currency format and decimals to a number. dollar( < number> , []) 3 FIND Locates position of lookup text within a cell. Case-sensitive. find( < search text > , , [] ) 4 LEFT Returns the specified number of characters from the left side of a string LEFT(, < no of characters> ) 5 LEN Counts number of characters in a string. Spaces are also included. len( < string value> ) 6 MID Extracts text from the middle of a string from the mentioned position. mid( < string value> , < start position>, < num characters>) 7 PROPER Converts text to proper case. proper( < string value>) 8 REPLACE Replaces a portion of text , with another text in a string. replace( < string value>), < string to find>, < string to replace> ) 9 REPT Repeats text a number of times in a string. rept( < string value> , < number of times>) 10 RIGHT Returns the specified number of characters from the right side of a string right( < string value>, < number of characters> ) 11 SEARCH Locates position of text within a string.This search is not Case-sensitive. search( < search text> , < position to start searching> ) 12 SUBSTITUTE Finds and replaces text. This search and replace is Case-sensitive. substitute( < string> , < old text> , < new text> , [ ] ) 13 TEXT Converts a value into text data type with a specific number format. text( < string value> , ) 14 TRIM Removes all the spaces in front of ( leading) and at the end of ( trailing) the text/ string expression. trim( < string value > )

## Examples of Using Worksheetfunctions in VBA

### Deletion of Rows That are Blank in a Range

```Sub row_del_demo()

' declare variable
Dim range_new

' loop to iterate through the rows
For i = 1 To 60
' get the range of rows
Set range_new = Worksheets("Wonders").Rows(i &amp;amp;amp; ":" &amp;amp;amp; i)

' find the number of blank columns in that row
fill_cols = Application.WorksheetFunction.CountA(range_new)

' delete the entire row if all columns in it are blank
If fill_cols = 0 Then
Sheets("Wonders").Rows(i).Delete
End If
Next

End Sub
```

### Using Worksheet Function to Find the Sum of Integers in an Array

```Sub worksh_demo()

' declare variables and objects
Dim a(10) As Integer
Dim sum_10 As Integer

' loop to assign integer values to all the 10 array elements
For i = 0 To 9
a(i) = i + 10
Debug.Print a(i)
Next

' find the sum of all numbers in the array using the worksheetfunction
sum_10 = Application.WorksheetFunction.Sum(a)

' Print the sum
Debug.Print sum_10

End Sub

```

### Using Average and Sum Worksheet Functions

```Sub wksfun_Demo()
' declare variables
Dim tot, avg, res, sci, soc, mat, eng, lan

' assigning values
sci = 67
soc = 78
eng = 90
mat = 89
lan = 45

' find the total using a worksheet function
tot = WorksheetFunction.Sum(sci, soc, eng, lan, mat)
Debug.Print "The total of all marks is " &amp;amp;amp; tot

'find the average marks
avg = WorksheetFunction.Average(sci, soc, mat, eng, lan)
Debug.Print "The average marks of the student is " &amp;amp;amp; avg

End Sub
```

## Worksheet Function That Uses the Data From Excel Sheet as Parameter

```Sub wksh_demo()
maximum_value = WorksheetFunction.Max(range("D2:D9"))
Debug.Print maximum_value
End Sub
```

Output :

528

## Conclusion

Worksheetfunction object helps us use proper format at our convenience to code our program instead of using the macro recorded to code any formula. We can either use object variables from our procedure or cell values from worksheets / any other Excel workbooks as parameters while we use the Worksheetfunction. The application object in front of it is optional. (Application.Worksheetfunction.)