Site Loader

Formulasin EXCEL

Microsoft Excel provides several built-in functions that can be used as formulas in Excel worksheets. It shows the function categories available in the Insert Function window, as shown in the following figure:

Using a formula in an Excel worksheet

Just as mathematical formulas help us solve simple and quick mathematical problems, so do Mrs. B.’s. Excel provides formulas for keeping good databases and formulas that can be automatically calculated and saved for easy use and reference.

Example of an Excel formula:

After we enter the SUM formula in the formula bar or in any cell (what you enter in a cell that starts with an equal sign (=) is displayed in the formula bar), if we press Enter, click a check mark in the formula bar, or select another cell, the automatically calculated answer is displayed.

If a row or formula cell is not in edit mode, the checkboxes and crosses in the formula row are disabled.

Using formulas as functions in VBA

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

What is the spreadsheet function?

The MS Excel application object has a method called WorksheetFunction. It has access to most standard built-in functions that are available as formulas in Excel.

Application. can be used immediately to access the functions. However, using Application.worksheetfunction. can help a programmer who can use Intellisense to browse through the parameters that should be used to complete the feature set.

List of worksheet functions – VBA

The following is a list of some commonly used spreadsheet functions.

List of logical functions

no Function Description Syntax Example Output
1 И Check that all conditions are met. The possible result of the output can be TRUE or FALSE. Function of the worktop. And(Logic1,Logic2) Function of the worktop. И(5>3,4<6) TRUE
2 IF If the conditions are met, do something (second parameter), otherwise do something else (third parameter). IF IF(2<6, 2 min, 6 min) 2 means less
3 IFERROR It is a combination of IF and ISERROR. IF(ISERROR( <calculation>), 0, <result of calculation>) IF(ISERROR(2/0),0,2/0) 0
4 OR Verifies that one of the conditions is met. The possible result of the output can be TRUE or FALSE. Function of the worktop. Si(Logic1,Logic2) Function of the worktop. If(5>3,14<6) TRUE

List of search and reference functions

no Function Description Syntax
1 SELECT Selects a value from a list based on the index number and index number specified in the parameter. select( , value1, value2)
2 HLOOKUP Looks for a value in the first row of the array and returns it. Hlookup( ,

, , )

3 INDEX Returns a value based on the column number and the row number. INDEX (matrix, , <[col num]>, <[area num]>)
4 LOOKUP Search for values in horizontal (rows) or vertical (columns) direction. Lookup( <search value>, , )
5 MATCH Searches for a value in the list and returns its position. MATCH (,, <[type of match]>)
6 TRANSPORT Changes the alignment of some cells. From vertical to horizontal and vice versa. Transpose()
7 VLOOKUP Searches for and returns a value in the first column of the array. Vlookup( ,

, , )

Date and time functions

A valid date here means that IsDate() returns the boolean value TRUE.

no Function Description Syntax
1 DATE Returns the date from a combination of year, month and day. Date (year, month, day)
2 DATEVAL Converts the date stored as text to a valid date (in an acceptable data format). date value(<text-date> )
3 DAY Returns the day as a number (1-31) from the current date. Tag()
4 DAY360 Returns the difference (in days) between two valid dates in a 360-day year. DAYS360( <starting date> , ,
5 EDATE Returns the current date, n months from the start date. DATE(, )
6 EOMONTH Returns the last day of the month, n months from the current date. Month
7 CHARLIE Returns the time as a number (0-23) from the current time. Time(<serial number>)
8 MINUTE Returns the minute as a number (0-59) from the current time. Minute()
9 MONTHS Returns the month as a number (1-12) of a valid date. Month()
10 POLICY NETWORK Returns the number of working days between 2 valid dates. network days(, , [])
11 NETWORK DAYS.INTL Returns the number of working days between 2 valid dates. By default, weekends are excluded and it is possible to add a list of holidays to be excluded from the count. netzwerktage.intl( , , [], [])
12 NOW Returns the current date and time as they appear in the system. now()
13 SECOND REPORT Returns the second as a number (0-59) from the current time. Second()
14 TIME Returns the time from a valid combination of hours, minutes and seconds. Time (, , )
15 TIME Converts time stored in text form to real time. Time value()
16 WEEKLY Returns the day of the week as an integer (1-7). Day of the week(, [])
17 WEEKLY NUMBER Returns the week number of the year (1-52). week number(, [])
18 WORK DAY Returns the date of n working days from the valid date. Working day (, days, [ ])
19 BIRTHDAY Returns the year of a valid date. Year(<serial number>)
20 YEARFRAC Returns the fraction of a year between two valid dates. yearfrac( , [])

Technical functions

no Function Description Syntax
1 CONVERT Converts a number or value from one unit to another. E.G.: Cm in inches, ft in centimeters, m in centimeters, kg in g. convert(, <from unit> , <to unit> )

Financial functions

no Function Description Syntax
1 FV Calculates and provides the future value of the investment. fv(, , [pv], [])
2 PV Calculates and provides the current value of the investment. fv(, , [], [])
3 NPER Calculates and gives the total number of pay periods for the investment. nper(<Rate, , [], [type])
4 PMT Calculates and reimburses the payment amount when making an investment. pmt(, , [], [])
5 TARIFFS Calculates and returns the interest rate of the investment. Quotes (, ,, [], [], [])
6 CASH VALUE Calculates and returns the net present value of the investment. npv(, , [], [], ….., [])
7 IRR Calculates and reports internal profitability for a range 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 bond price. Price (, , , [])
10 INTRATE Returns the interest rate of a fully invested security. within(, , , [])

Information functions

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

Mathematical functions

no Function Description Syntax
1 ABS Calculates and returns the absolute value of the number. abs( <a number>)
2 AGGREGAT It defines and performs calculations for a database or a list. AGGREGATE(, , [k] >
3 COVER Rounds the number to the nearest specified multiple and returns the value. Ceiling( <a number> , )
4 COS Returns the cosine value of the angle. cos( <a number>)
5 REVIEWS Converts radians to degrees and returns the value. Degree( <angle>)
6 TSUM provides a summary of the records in the database that meet certain criteria. dsum( , , )
7 EVEN Round to the nearest even number and return the value. even( <a number>)
8 EXP Calculates an exponential value for a given number and returns the value. exp( <a number> )
9 DONE Returns the factorial value. Done()
10 DISCUSSION Round the number to the nearest indicated multiple. soil ( , )
11 GCD Returns the greatest common divisor. gcd( <number 1> , [], [],……[])
12 INT Round the number to the nearest whole number. int( < number > )
13 LCM Returns the least common multiple. lcm( <number 1> , [], [],……[])
14 LN Returns the natural logarithm of the number. LN( <a number> )
15 LOG Returns the logarithm of the number with the specified base. log( , [])
16 LOG10 Returns the logarithm of the number in base 10. log10( <a number> )
17 MROUND Rounds a number to the specified plural. mround( < number > , )
18 ODD Round to the nearest odd number. odd( <a number > )
19 PI Meaning of PI. PI()
20 POWER Calculate the estimated number. Power( <a number> , )
21 PRODUCT Multiplies a series of numbers. product( <number 1> , [], [],……[]).
22 QUOTIENT Returns the integer of the division. Quotient( <counter> , )
23 RADIANS Converts the angle to radians. radians( <angle> )
24 RANDBETWEEN Calculates a random number between two numbers. borderbetween( <Bottom> , )
25 ROUND Rounds the number to the specified number of digits (specified in the parameter). around( <a number> , )
26 ROUNDDOWN Round the number down (to zero). ROUNDDOWN( , )
27 ROUNDUP Round the number up (away from zero). ROUNDUP( , )
28 SIN Returns the value of the sine of the angle. sin( <a number>)
29 SYNOPSIS Returns a statistical summary of a set of data. subtotal( <function num>, , [], [],…..[]).
30 SYNOPSIS Add up the numbers and return the sum. Sum( <number 1> , [], [],……[])
31 SUMIF The sums of the numbers that meet the criteria. somif( , , [])
32 SUMIFS Sum of numbers satisfying different criteria. sumifs(<sum range>, , <criterion 1> , [, ],[, ],….[, ]])
33 SYNOPSIS multilayer arrays of numbers and folds the resulting array. sumproduct( <table 1> , [], [], [],….[])
34 TAN Returns the value of the tangent of the angle. tan( <a number > )

Statistical functions

no Function Description Syntax
1 AVERAGE Average of the given numbers. Average( <number 1> , [], [],……[])
2 AVERAGE A set of average numbers that meet the criteria. averageif( , , [])
3 AVERAGEIFS Average value of numbers that meet different criteria. averageifs(<average range>, , <criterion 1> , [, ],[, ],….[, ]])
4 CORREL Calculates and gives the correlation of two series. correlate( , )
5 COUNT Counts the number of cells that contain a specified value and returns this number. count( , [], [], [<value 4>]….[] )
6 COUNTRY Counts the number of empty cells and returns them. COUNTA( , [], [], [<value 4>]….[] )
7 COUNTBLANK Counts the number of empty cells and returns them. countblank( <range> )
8 COUNTIF Counts and returns the number of cells that meet the criteria. counttif( , )
9 CONFIGURATIONS Counts and returns the number of cells that meet various criteria. counttifs( , <criterion 1> , [, ], [, ],….[, ])
10 PREDICTION Predict future y-values from the linear trend line. Prediction ( , )
11 FREQUENCY Counts the values that fall within the specified ranges. frequency(<data table> , )
12 GROW Calculates Y values based on exponential growth. Growth( , [], [], [], [const])
13 INTERCEPT Calculates the intercept of the line with the best match. interception(, )
14 MORE Returns the kth largest value. large(, k)
15 LINES Returns a trendline statistic. linest( <known ys> , [], [] , [] )
16 MAX Returns the largest number in the specified row. max( <number 1> , [], [],……[])
17 MEDIAN Returns the median value of the specified range. Median ( <number 1> , [], [],……[])
18 MIN Returns the smallest number in the specified row. min( <number 1> , [], [],……[])
19 MODE Returns the most frequent number in the specified row. mode( <number 1> , [], [],……[])
20 PERCENTAGE CRANE Returns the 10th percentile. Percentile( , )
21 PERCENTAGE CRANE Returns the k percentile, where k is included. Percentile.
22 PERCENTAGE CRANE Returns the k percentile, where k is exclusive. Percentile.
23 QUARTIL Returns the value of the specified quartile. quartile( <table> , )
24 NEIGHBORHOOD Returns the value of the specified quartile (inclusive). quartile.
25 OVERVIEW OF THE APARTMENT Returns the value of the specified quartile (exclusive). quartile.exc( , )
26 RANK Returns the rank of the number in the row. rank(<number> , , []))
27 RANK.AVG. Returns the rank of the number in the row. (Average). rank.avg (<a number> , , [])
28 RANK.E.M.T. Returns the rank of the number in the row. (Front row). rank.eq(<number> , , [])
29 SLOP Calculates and returns the value of the slope of a linear regression. Slope( < known ys> , )
30 LITTLE Returns the smallest value in the number sequence. small( <table>, )
31 STDEV Calculates and returns the standard deviation. stdev( <number 1> , [], [],……[])
32 STDEV.P. Calculates and gives the standard deviation of the entire population. stdev.p( <number 1> , [], [],……[])
33 STDEV.S. Calculates and gives the standard deviation of the sample. stdev.s( <number 1> , [], [],……[])
34 STDEVP Calculates and gives the standard deviation of the whole population stdevp( <number 1> , [], [],……[])
35 TREND Calculates and returns Y values based on the trend line. trend( , [], [], [], [const])

String (or) text functions

no Function Description Syntax
1 NUMBER Deletes all characters that cannot be printed. pure
2 DOLLAR Adds the currency format and decimals to the number. dollar( <a number> , [])
3 FIND IT Indicates the position of the search text in the cell. Upper and lower case letters are case sensitive. find( < search text > , , [] )
4 LEFT Returns the specified number of characters to the left of the string. LEFT(, <no characters>))
5 LEN Counts the number of characters in a string. Fields are also included. len( <string value> )
6 MID Extracts text from the middle of a line from a specified position. mid( <string value> , <start position>, <numeric character>)
7 OWN Converts the text to the desired capital letter. actual( <line value>)
8 REPLACEMENT Replaces part of the text with other text on the line. replace( <string value>)), <string to find>, <string to replace> )
9 ANSWER: Repeat the text several times on one line. rept( <row value> , <a number of times>))
10 RULES Returns the specified number of characters to the right of the string. right( <line value>, <number of characters>))
11 SEARCH The search is not case sensitive. search( <search text> , <position to start search>) )
12 SUBSCRIBE Search and replace text. Search and replace is case sensitive. replace( <string> , <old text> , <new text> , [ ] )
13 TEXT Converts the value to a text data type with a specified numeric format. text( <string value> , )
14 TRIM Removes all spaces before ( master ) and at the end ( trailing ) of a text/line expression. trim( < string value > )

Examples of using worksheet functions in VBA

Delete the empty rows in the range.

Sub row_del_demo()

Declare variable
Dim range_new

Loop to go through the rows
For i = 1 To 60
‘ Get the range of the rows
Set range_new = Worksheets(Wonders).Rows(i & : & i)

Get the number of empty columns in this row
fill_cols = Application.WorksheetFunction.CountA(range_new)

‘ delete the entire row if all columns are empty
If fill_cols = 0, then
Sheets(Wonders).Rows(i).Delete
End If
Next

Sub-sub final

Use a spreadsheet function to find the sum of integrals in a table.

Under worksh_demo()

Declare variables and objects
Dim a(10) as integer
Dim sum_10 as integer

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

Calculate the sum of all the numbers in the table using worksheet function
sum_10 = Application.WorksheetFunction.Sum(a)

Print sum
Debug.Print sum_10

Sub-sub final

Using the Average and Summary functions in the worksheet

Sub wksfun_Demo()
‘ Declare variables
Dim tot, avg, res, sci, soc, mat, eng, lan

Value assignment
sci = 67
soc = 78
eng = 90
mat = 89
lan = 45

Get sum with worksheet function
to = WorksheetFunction.Sum(sci, soc, eng, lan, mat)
Debug.Print Sum of all scores & Sum of all scores is & total

Get average grades
avg = WorksheetFunction.Average(sci, soc, mat, eng, lan)
Debug.Print Average grades student & avg

Sub-sub final

Spreadsheet function, which uses the data from the Excel spreadsheet as parameter.

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

Go away:

528

Supplement

The Worksheetfunction object helps us use the correct format to code our program, rather than using a macro written to code an arbitrary formula. We can use object variables from our procedure or cell values from worksheets/any other Excel workbook as parameters when we use the Worksheet function. The preceding object of application is optional. (Appendix. Worksheet function.)

Post Use any formula: The post VBA worksheet function appeared first on Software Solutions Online.

frequently asked questions

How do you use the worksheet function in VBA?

Blog

Can we use Vlookup in VBA?

vlookup-in-vba-excel

How do you use a variable in an Excel VBA formula?

Forum 86819-vba code…

Related Tags:

using excel formulas in vbaworksheetfunction.sum vbavba worksheetfunction vlookuphow to write excel formula in vba codeapplication.worksheetfunction vbaworksheetfunction.match vba,People also search for,Feedback,using excel formulas in vba,worksheetfunction.sum vba,vba worksheetfunction vlookup,how to write excel formula in vba code,application.worksheetfunction vba,worksheetfunction.match vba,vba worksheetfunction column,excel vba worksheet functions list

Post Author: jasmine Loteyj

Leave a Reply

Your email address will not be published. Required fields are marked *