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(<logical test>,<value if true>, <value if false>)	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( <index num>, value1, value2)
2	HLOOKUP	Looks up a value in the first row of a table and returns a value.	Hlookup( <lookup value>, <table Array>, <row index num>, <range lookup>)
3	INDEX	Returns a value based on its column number and row numbers.	INDEX (array, <row num>, <[col num]>, <[area num]>)
4	LOOKUP	Looks up values either horizontally (in rows) or vertically (in columns) .	Lookup( < lookup value>, <range of cols or table>, <boolean result>)
5	MATCH	Searches for a value in a list and returns its position.	MATCH (<lookup value>, <lookup array>, <[match type]>)
6	TRANSPOSE	Changes the orientation of a range of cells. Vertical to horizontal and vice versa.	Transpose(<array or range that needs to be flipped>)
7	VLOOKUP	Looks up a value in the first column of a table and returns a value.	Vlookup( <lookup value>, <table Array>, <col index num>, <range lookup>)

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( <serial number> )
4	DAYS360	Returns difference (in days) between 2 valid dates in a 360-day year.	DAYS360( < start date> , <end date> , <method>
5	EDATE	Returns a valid date, “n” months away from a valid start date.	EDATE(<start date>, <months> )
6	EOMONTH	Returns the last day of the month, “n” months away from a valid date.	eomonth(<start date>, <months>)
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(<serial number>)
9	MONTH	Returns the month as a number (1-12) from a valid date.	month(<serial number>)
10	NETWORKDAYS	Returns the number of working days between 2 valid dates.	networkdays(<start date>, <end date>, [<holidays>])
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(<start date> , <end date> , [<weekend>], [<holidays>])
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(<serial number>)
14	TIME	Returns the time from a valid combination of hour, minute, and second.	time(<hour>, <minute>, <second>)
15	TIMEVALUE	Converts a time stored as text into a valid time.	timevalue(<time text>)
16	WEEKDAY	Returns the day of the week as an integer (1-7).	weekday(<serial number>, [<return type>])
17	WEEKNUM	Returns the week number in a year (1-52).	weeknum(<serial number>, [<return type>])
18	WORKDAY	Returns the date n working days from a valid date.	workday(<start date>, days, [ <holidays >])
19	YEAR	Returns the year from a valid date.	year(< serial number >)
20	YEARFRAC	Returns the fraction of a year between 2 valid dates.	yearfrac(<start date , <end date> , [<basis>])

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(<value>, < from unit> , < to unit > )

Financial Functions

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

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(<function number>, <options>, <array >, [k] >
3	CEILING	Rounds a number up, to the nearest specified multiple and returns the value.	ceiling( < number > , <significance> )
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( <database> , <field> , <criteria> )
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( <number> )
10	FLOOR	Rounds a number down to the nearest specified multiple.	floor( <number> , <significance> )
11	GCD	Returns the greatest common divisor.	gcd( < number 1> , [<number 2>], [<number 3>],……[<number n>])
12	INT	Rounds a number down to the nearest integer.	int( < number > )
13	LCM	Returns the least common multiple.	lcm( < number 1> , [<number 2>], [<number 3>],……[<number n>])
14	LN	Returns the natural logarithm of a number.	LN( < number > )
15	LOG	Returns the logarithm of a number to a specified base.	log( <number> , [<base>])
16	LOG10	Returns the base-10 logarithm of a number.	log10( < number > )
17	MROUND	Rounds a number to a specified multiple.	mround( < number > , <multiple> )
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> , <power> )
21	PRODUCT	Multiplies an array of numbers.	product( < number 1> , [<number 2>], [<number 3>],……[<number n>])
22	QUOTIENT	Returns the integer result of division.	quotient( < numerator> , <denominator> )
23	RADIANS	Converts an angle into radians.	radians( < angle > )
24	RANDBETWEEN	Calculates a random number between two numbers.	randbetween( < bottom> , <top> )
25	ROUND	Rounds a number to a specified number of digits (specified in a parameter).	round( < number> , <num digits> )
26	ROUNDDOWN	Rounds a number down (towards zero).	ROUNDDOWN( <number> , <num digits> )
27	ROUNDUP	Rounds a number up (away from zero).	ROUNDUP( <number> , <num digits> )
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>, <ref 1>, [<ref 2>], [<ref 3>],…..[<ref n>])
30	SUM	Adds numbers together and returns the total value.	sum( < number 1> , [<number 2>], [<number 3>],……[<number n>])
31	SUMIF	Sums numbers that meet a criteria.	sumif( <range> , <criteria> , [<sum range>])
32	SUMIFS	Sums numbers that meet multiple criteria.	sumifs(< sum range>, <criteria range 1> , < criteria 1> , [<criteria range 2>, <criteria 2>],[<criteria range 3>, <criteria 3>],….[<criteria range n>, <criteria n>])
33	SUMPRODUCT	Multiplies arrays of numbers and adds up the resultant array.	sumproduct( < array 1> , [<array 2>], [<array 3>], [<array 4>],….[<array 4>])
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> , [<number 2>], [<number 3>],……[<number n>])
2	AVERAGEIF	Averages given numbers that meet a criteria.	averageif( <range> , <criteria> , [<average range>])
3	AVERAGEIFS	Averages given numbers that meet multiple criteria.	averageifs(< average range>, <criteria range 1> , < criteria 1> , [<criteria range 2>, <criteria 2>],[<criteria range 3>, <criteria 3>],….[<criteria range n>, <criteria n>])
4	CORREL	Calculates and returns the correlation of two series.	correl( <array 1> , <array 2> )
5	COUNT	Counts and returns the number of cells that contain a specific value.	count( <value 1> , [<value 2>], [<value 3>], [< value 4>]….[<value n>] )
6	COUNTA	Counts and returns the number of cells that are non-blank.	COUNTA( <value 1> , [<value 2>], [<value 3>], [< value 4>]….[<value n>] )
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( <range> , <criteria> )
9	COUNTIFS	Counts and returns the number of cells that meet multiple criteria.	countifs( <criteria range 1> , < criteria 1> , [<criteria range 2>, <criteria 2>],[<criteria range 3>, <criteria 3>],….[<criteria range n>, <criteria n>])
10	FORECAST	Predict future y-values from a linear trend line.	forecast( <x> , <known_ys>, <known_xs> )
11	FREQUENCY	Counts values that fall within specified ranges.	frequency(< data array> , <bins array> )
12	GROWTH	Calculates Y values based on exponential growth.	growth( <knowns ys> , [<known xs>], [<new xs>], [const])
13	INTERCEPT	Calculates the Y intercept for the “best-fit” line.	intercept(<known ys>, <known xs>)
14	LARGE	Returns the kth largest value.	large( <array> , k)
15	LINEST	Returns statistics about a trendline.	linest( < known ys> , [<known xs>], [<const>] , [<stats>] )
16	MAX	Returns the largest number from the given series.	max( < number 1> , [<number 2>], [<number 3>],……[<number n>])
17	MEDIAN	Returns the median number from the given series.	median( < number 1> , [<number 2>], [<number 3>],……[<number n>])
18	MIN	Returns the smallest number from the given series.	min( < number 1> , [<number 2>], [<number 3>],……[<number n>])
19	MODE	Returns the most common number in the given series.	mode( < number 1> , [<number 2>], [<number 3>],……[<number n>])
20	PERCENTILE	Returns the kth percentile.	percentile( <array>, <k>)
21	PERCENTILE.INC	Returns the kth percentile where k is inclusive.	percentile.inc( <array> , <k> )
22	PERCENTILE.EXC	Returns the kth percentile where k is exclusive.	percentile.exc( <array> , <k> )
23	QUARTILE	Returns the specified quartile value.	quartile( < array> , <quart> )
24	QUARTILE.INC	Returns the specified quartile value (Inclusive) .	quartile.inc( <array> , <quart> )
25	QUARTILE.EXC	Returns the specified quartile value (Exclusive).	quartile.exc( <array> , <quart> )
26	RANK	Returns the rank of a number within a series.	rank(< number> , <ref> , [<order>])
27	RANK.AVG	Returns the rank of a number within a series. (Averages).	rank.avg (< number> , <ref> , [<order>])
28	RANK.EQ	Returns the rank of a number within a series. (Top Rank).	rank.eq (< number> , <ref> , [<order>])
29	SLOPE	Calculates and returns the value of slope from linear regression.	slope( < known ys> , <known xs>)
30	SMALL	Returns the smallest value in a series of numbers.	small( < array>, <k>)
31	STDEV	Calculates and returns the standard deviation.	stdev( < number 1> , [<number 2>], [<number 3>],……[<number n>])
32	STDEV.P	Calculates and returns the Standard deviation of an entire population.	stdev.p( < number 1> , [<number 2>], [<number 3>],……[<number n>])
33	STDEV.S	Calculates and returns the Standard deviation of a sample.	stdev.s( < number 1> , [<number 2>], [<number 3>],……[<number n>])
34	STDEVP	Calculates and returns the Standard deviation of an entire population	stdevp( < number 1> , [<number 2>], [<number 3>],……[<number n>])
35	TREND	Calculates and returns Y values based on a trendline.	trend( <knowns ys> , [<known xs>], [<new xs>], [const])

String (or) Text Functions

S.no	Function	Description	Syntax
1	CLEAN	Removes all  characters that are not printable.	clean( <text> )
2	DOLLAR	Adds currency format and decimals to a number.	dollar( < number> , [<decimals>])
3	FIND	Locates position of lookup text within a cell. Case-sensitive.	find( < search text > , <search source text>, [<start pos>] )
4	LEFT	Returns the specified number of characters from the left side of a string	LEFT(<text>, < 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> , [ <instance num>] )
13	TEXT	Converts a value into text data type with a specific number format.	text( < string value> , <format text > )
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 & ":" & 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 " & tot
    
    'find the average marks
    avg = WorksheetFunction.Average(sci, soc, mat, eng, lan)
    Debug.Print "The average marks of the student is " & 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.)