Using Built-In Functions to Solve Various Problems with Excel VBA

As already mentioned, the capabilities of MS Excel are convenient for solving a wide range of mathematical, physical, economic, and other problems. It is enough to properly arrange the information on the worksheet, i.e., prepare the initial data, determine where the results will be placed, and enter the required formulas.

Excel’s built-in functions can be found on the Formulas tab in the Function Library group. Access to all available functions can also be organized via the Insert Function dialog box.

Excel provides a wide variety of built-in functions for processing both numerical values and other types of data stored in cells. To view available categories and descriptions of functions, use the Function Wizard: click Insert Function in the Function Library group on the Formulas tab.

Let us consider several examples of using functions and formulas to solve specific problems.

Membership of Points in a Plane Region

On a plane, coordinates of points are given. Determine how many of these points belong to the region defined by the system of inequalities:

Steps:

1. Enter the necessary labels into cells A1, A3, A4, B4, D4, A5:B19 (depending on the number of points), A21.
   • Tip: Use + to move to the next line in a cell. Then merge cells A3 and B3 using Merge & Center on the Home tab.

1. According to the inequalities, create a formula that checks whether a point satisfies at least one of the groups of conditions. If so, assign a value of 1.

In C5, enter:

=IF(OR(AND(A5^2+B5^2<=25,A5^2+B5^2>=9),AND(B5<=(-A5)-10,A5>=-10,B5>=-10)),1,0)

Copy this formula down to the range C6:C19.

• For text output, add in D5 (and copy to D5:D19):

=IF(C5=1,"this point belongs to the region","")

Apply the custom number format for cells D5:D19:

;;[White]

• In C21, enter the formula:

=SUM(C5:C19)

• Format the cells using the Styles group on the Home tab as needed.

Example: Solving a System of Linear Equations

In general, the solution of the system AX=BAX = B, where A is the coefficient matrix, B is the column vector of constants, and X is the vector of unknowns, has the form:

X=A⁻¹B.

This follows from the fact that in solving matrix equations, the identity matrix I must remain with X. Multiplying both sides of AX=Bon the left by A⁻¹, we obtain the solution.

Consider solving the system AX=B, where:

The result is shown in Figure.

Steps:

• Enter the values of matrix A into cells A4:B5.
• Enter the values of vector B into cells D4:D5.
• In cell A8, enter the formula:
• =MMULT(MINVERSE(MMULT(A4:B5,A4:B5)),D4:D5)

(It is recommended to use the Function Wizard when entering this formula.)

• To obtain the numerical results, select the range A8:A9, place the cursor in the formula bar, and press Ctrl+Shift+Enter (since this is an array formula).
• Format the task results as required.

Example: Creating a Final Construction Based on a Given Pattern

Suppose, from the available information:

• Full Name
• Phone
• Street, house

you need to create a construction of the form:

Full Name, Phone {format: #00-00-00}, St. Street, House

The result for this example is shown in Figure.

Steps:

• Enter the data in cells A3:C13 according to the specified pattern (see Figure 3.34).
• In cell E3, enter the heading: Full name + phone + address.
• In cell E4, enter the formula:
• =$A4 & « ,  » & TEXT($B4, »\ ???-00-00, ») &  » St.  » & $C4
• Copy the formula to the range E5:E13 (you can use the AutoFill handle).
• Format the data.

Example: Splitting Information Contained in One Cell

Split the following information contained in one cell:

City!Institution!Director!Number of employees

Result should be:

• City
• Institution
• Director
• Number of employees

The result is shown.

Steps:

• In cells B2 and D2, enter the labels: Initial information and Result.
• In cells B3:B6, enter the heading and the necessary data of the initial structure.
• Use the formulas in Table 3.18 for calculations.
• Format the data and results.

Table . Formulas for Parsing Institution Information

Example: Creating a Statement for Movie Rental Accounting

Form a statement for movie rental accounting with the following columns:

• №,
• Movie title,
• Last name,
• Date issued,
• Date returned,
• Duration (in hours),
• Duration (in days),
• Payment.

Payment rules:

• If duration ≤ 24 hours → Payment = base rate (chosen arbitrarily).
• If duration > 24 and ≤ 48 hours → Payment = base rate + 0.8 × base rate.
• If duration > 48 hours → for each overdue day, Payment = 3 × base rate.
• If the cassette or CD is lost → Fine = 30 × base rate.

Steps:

• Create the header row of the statement and enter the necessary data in the columns: №, Title, Last name, Date issued, Date returned.
• In cell D34, enter the base rate value, e.g., 5.
• In G38, enter the formula for rental time (in hours):
• =YEAR(F38-E38)-1900+MONTH(F38-E38)+DAY(F38-E38)*24-1
• In H38, enter the formula for rental time (in days):
• =YEAR(F38-E38)-1900+MONTH(F38-E38)+DAY(F38-E38)-1
• In I38, enter the formula for payment:
• =IF(G38<=24,$D$34,IF(AND(G38>24,G38<=48),$D$34+$D$34*0.8,IF(G38>48,$D$34+$D$34*0.8+(G38-48)/24*3*$D$34)))

• Copy the formulas into the corresponding ranges G39:G44, H39:H44, I39:I44.
• Format all results on the worksheet.