# Solving Simultaneous Equations

## Solving Simultaneous Equations

This section describes how to use formulas to solve simultaneous linear equations. The following is an example of a set of simultaneous linear equations:

```3x + 4y = 8 4x + 8y = 1
```

Solving a set of simultaneous equations involves finding the values for x and y that satisfy both equations. For this set of equations, the solution is as follows :

```x = 7.5 y = -3.625
```

The number of variables in the set of equations must be equal to the number of equations. The preceding example uses two equations with two variables. Three equations are required to solve for three variables (x, y, and z).

The general steps for solving a set of simultaneous equations follow. See Figure 10-3, which uses the equations presented at the beginning of this section.

1. Express the equations in standard form. If necessary, use simple algebra to rewrite the equations such that the variables all appear on the left side of the equal sign. The two equations that follow are identical, but the second one is in standard form:

```3x -8 = -4y 3x + 4y = 8
```
2. Place the coefficients in an n x n range of cells , where n represents the number of equations. In Figure 10-3, the coefficients are in the range I2:J3.

3. Place the constants (the numbers on the right side of the equal sign) in a vertical range of cells. In Figure 10-3, the constants are in the range L2:L3.

4. Use an array formula to calculate the inverse of the coefficient matrix. In Figure 10-3, the following array formula is entered into the range I6:J7. (Remember to use Ctrl+Shift+Enter to enter an array formula.)

```{=MINVERSE(I2:J3)}
```
5. Use an array formula to multiply the inverse of the coefficient matrix by the constant matrix. In Figure 10-3, the following array formula is entered into the range J10:J11. This range holds the solution.

```{=MMULT(I6:J7,L2:L3)}
```
 Cross Ref Refer to Chapter 14 for more information on array formulas. Chapter 16 demonstrates how to use iteration to solve some simultaneous equations.
 On the CD You can access the workbook,  simultaneous equations.xlsx , shown in Figure 10-3, on the companion CD-ROM. This workbook solves simultaneous equations with two or three variables.

## Rounding Numbers

Excel provides quite a few functions that round values in various ways. Table 10-1 summarizes these functions.

Table 10-1: EXCEL ROUNDING FUNCTIONS

Function

Description

CEILING

Rounds a number up (away from zero) to the nearest specified multiple

DOLLARDE

Converts a dollar price expressed as a fraction into a decimal number

DOLLARFR

Converts a dollar price expressed as a decimal into a fractional number

EVEN

Rounds a number up (away from zero) to the nearest even integer

FLOOR

Rounds a number down (toward zero) to the nearest specified multiple

INT

Rounds a number down to make it an integer

MROUND

Rounds a number to a specified multiple

ODD

Rounds a number up (away from zero) to the nearest odd integer

ROUND

Rounds a number to a specified number of digits

ROUNDDOWN

Rounds a number down (toward zero) to a specified number of digits

ROUNDUP

Rounds a number up (away from zero) to a specified number of digits

TRUNC

Truncates a number to a specified number of significant digits

 Caution It's important to understand the difference between rounding a value and formatting a value. When you format a number to display a specific number of decimal places, formulas that refer to that number use the actual value, which may differ from the displayed value. When you round a number, formulas that refer to that value use the rounded number.
 Cross Ref Chapter 6 contains examples of rounding time values.

The following sections provide examples of formulas that use various types of rounding.

### Basic Rounding Formulas

The ROUND function is useful for basic rounding to a specified number of digits. You specify the number of digits in the second argument for the ROUND function. For example, the formula that follows returns 123.40 (the value is rounded to one decimal place):

```=ROUND(123.37,1)
```

If the second argument for the ROUND function is zero, the value is rounded to the nearest integer. The formula that follows, for example, returns 123.00:

```=ROUND(123.37,0)
```

The second argument for the ROUND function can also be negative. In such a case, the number is rounded to the left of the decimal point. The following formula, for example, returns 120.00:

```=ROUND(123.37,-1)
```

The ROUND function rounds either up or down. But how does it handle a number such as 12.5, rounded to no decimal places? You'll find that the ROUND function rounds such numbers away from zero. The formula that follows, for instance, returns 13.0:

```=ROUND(12.5,0)
```

The next formula returns –13.00 (the rounding occurs away from zero):

```=ROUND(-12.5,0)
```

To force rounding to occur in a particular direction, use the ROUNDUP or ROUNDDOWN functions. The following formula, for example, returns 12.0. The value rounds down.

```=ROUNDDOWN(12.5,0)
```

The formula that follows returns 13.0. The value rounds up to the nearest whole value.

```=ROUNDUP(12.43,0)
```

### Rounding to the Nearest Multiple

The MROUND function is useful for rounding values to the nearest multiple. For example, you can use this function to round a number to the nearest 5. The following formula returns 135:

```=MROUND(133,5)
```

### Rounding Currency Values

Often, you need to round currency values. For example, you may need to round a dollar amount to the nearest penny. A calculated price may be something like \$45.78923. In such a case, you'll want to round the calculated price to the nearest penny. This may sound simple, but there are actually three ways to round such a value:

• Round it up to the nearest penny.

• Round it down to the nearest penny.

• Round it to the nearest penny (the rounding may be up or down).

The following formula assumes a dollar and cents value is in cell A1. The formula rounds the value to the nearest penny. For example, if cell A1 contains \$12.421, the formula returns \$12.42.

```=ROUND(A1,2)
```

If you need to round the value up to the nearest penny, use the CEILING function. The following formula rounds the value in cell A1 up to the nearest penny. For example, if cell A1 contains \$12.421, the formula returns \$12.43.

```=CEILING(A1,0.01)
```

To round a dollar value down, use the FLOOR function. The following formula, for example, rounds the dollar value in cell A1 down to the nearest penny. If cell A1 contains \$12.421, the formula returns \$12.42.

```=FLOOR(A1,0.01)
```

To round a dollar value up to the nearest nickel, use this formula:

```=CEILING(A1,0.05)
```

You've probably noticed that many retail prices end in \$0.99. If you have an even dollar price and you want it to end in \$0.99, just subtract .01 from the price. Some higher-ticket items are always priced to end with \$9.99. To round a price to the nearest \$9.99, first round it to the nearest \$10.00 and then subtract a penny. If cell A1 contains a price, use a formula like this to convert it to a price that ends in \$9.99:

```=ROUND(A1/10,0)*10-0.01
```

For example, if cell A1 contains \$345.78, the formula returns \$349.99.

A simpler approach uses the MROUND function:

```=MROUND(A1,10)-0.01
```

### Working with Fractional Dollars

The DOLLARFR and DOLLARDE functions are useful when working with fractional dollar value, as in stock market quotes.

Consider the value \$9.25. You can express the decimal part as a fractional value (\$9 1/4, \$9 2/8, \$9 4/16, and so on). The DOLLARFR function takes two arguments: the dollar amount and the denominator for the fractional part. The following formula, for example, returns 9.1 (the .1 decimal represents 1/4):

```=DOLLARFR(9.25,4)
```
 Caution In most situations, you won't use the value returned by the DOLLARFR function in other calculations. In the preceding example, the result of the function will be interpreted as 9.1, not 9.25. To perform calculations on such a value, you need to convert it back to a decimal value by using the DOLLARDE function.

The DOLLARDE function converts a dollar value expressed as a fraction to a decimal amount. It also uses a second argument to specify the denominator of the fractional part. The following formula, for example, returns 9.25:

```=DOLLARDE(9.1,4)
```
 Tip The DOLLARDE and DOLLARFR functions aren't limited to dollar values. For example, you can use these functions to work with feet and inches. You might have a value that represents 8.5 feet. Use the following formula to express this value in terms of feet and inches. The formula returns 8.06 (which represents 8 feet, 6 inches). ```=DOLLARFR(8.5,12) ```

Another example is baseball statistics. A pitcher may work 6 2 / 3 innings, and this is usually represented as 6.2. The following formula displays 6.2:

```=DOLLARFR(6+2/3,3)
```

### Using the INT and TRUNC Functions

On the surface, the INT and TRUNC functions seem similar. Both convert a value to an integer. The TRUNC function simply removes the fractional part of a number. The INT function rounds a number down to the nearest integer, based on the value of the fractional part of the number.

In practice, INT and TRUNC return different results only when using negative numbers. For example, the following formula returns –14.0:

```=TRUNC(-14.2)
```

The next formula returns –15.0 because –14.3 is rounded down to the next lower integer:

```=INT(-14.2)
```

The TRUNC function takes an additional (optional) argument that's useful for truncating decimal values. For example, the formula that follows returns 54.33 (the value truncated to two decimal places):

```=TRUNC(54.3333333,2)
```

### Rounding to an Even or Odd Integer

The ODD and EVEN functions are provided for situations in which you need to round a number up to the nearest odd or even integer. These functions take a single argument and return an integer value. The EVEN function rounds its argument up to the nearest even integer. The ODD function rounds its argument up to the nearest odd integer. Table 10-2 shows some examples of these functions.

Table 10-2: RESULTS USING THE EVEN AND ODD FUNCTIONS

Number

EVEN Function

ODD Function

3.6

4

5

3.0

4

3

2.4

4

3

1.8

2

3

1.2

2

3

0.6

2

1

0.0

1

0.6

2

1

1.2

2

3

1.8

2

3

2.4

4

3

3.0

4

3

3.6

4

5

### Rounding to n Significant Digits

In some cases, you may need to round a value to a particular number of significant digits. For example, you might want to express the value 1,432,187 in terms of two significant digits (that is, as 1,400,000). The value 9,187,877 expressed in terms of three significant digits is 9,180,000.

If the value is a positive number with no decimal places, the following formula does the job. This formula rounds the number in cell A1 to two significant digits. To round to a different number of significant digits, replace the 2 in this formula with a different number.

```=ROUNDDOWN(A1,2-LEN(A1))
```

For non-integers and negative numbers, the solution gets a bit trickier. The formula that follows provides a more general solution that rounds the value in cell A1 to the number of significant digits specified in cell A2. This formula works for positive and negative integers and non-integers.

```=ROUND(A1,A2-1-INT(LOG10(ABS(A1))))
```

For example, if cell A1 contains 1.27845 and cell A2 contains 3, the formula returns 1.28000 (the value, rounded to three significant digits).