Advanced Excel

It is not necessary that you read this section in order to use Excel. If you have a goodgrasp of the issues already described in the book, you can already use Excel with good results. This section goes a little further and looks at the more advanced types of formulas and functions you can use. In other words,

this section will raise your level from skilled user to advanced user.

Excel has a myriad of functions that you can use in your formulas, and sometimes the biggest challenge seems to be able to find your way around to get what you need. I have worked with Excel for many years and I have discovered that, although I have solved many and diverse tasks in Excel, there are some features that I use again and again simply because they are useful in many contexts. It is these features that I will describe in this section. You can then explore the more specialised functions on your own.

1.1 Nested Functions

When you write a formula, you can use functions as arguments inside other functions. You might want to calculate the square root of a sum of a series of cells. It could look like this:

=sqroot (SUM(B2:B20)

Here we have a SUM function as argument in a SQ ROOT function. It is not that hard, but if you start working with many nested functions in a formula, you need to keep your tongue straight in the mouth in order to place start and end brackets in the right places.

1.2 Look-up

Look-up functions can find values in a table from reference values. Imagine that you have an index of members in an organisation. The index contains a column with membership numbers and then columns

of name, address, member type, etc.

1.2.1 VOOKUP (Vertical Lookup)

VLOOKUP finds values in a table using a search value. If we have a membership directory with membership number, name, address and telephone number, the function may, for example, look up a certain name using the corresponding membership number, which we have entered into a cell. The

syntax is:

WLOOKUP lookup value; table array; col index num;equals)

"lookup value is what is sought after. If we want to find a name using a membership number, the lookup value is the membership number "table array" is the cell range for the entire table. In our example it is the membership directory.

"col index num" is the number of the column a value will be returned from. If the table has four columns and the members names appear in the second column, we write the number 2 to make the function return the name.

"Equals" is an optional argument. It is a so-called "logical" value, which can be either TRUE or FALSE. This is because the function can search in two different ways. Normally we use the value TRUE, which is also the default position if you omit this argument. The value TRUE results in a search for the largest value that is less than or equal to the lookup value. The value FALSE results in a search for a value which is exactly equal to the lookup value.

 Figure 01

In Figure 130 there is an example of the membership index. To the left is the actual table in the cell range A4: D13 with members listed. To the right of the table, there is something which can look up members using the number typed into cellF4.

To be able to look up members names, the formula in cell G4 is as follows:

=WLOOKUP (F4, A4: D13, 2, FALSE)

To look up address and telephone number the col index num should be 3 and 4 respectively.

The logical value Equals" is set to FALSE, since TRUE may give false results. Imagine that Jane Jensen was member number 11 instead of 10, and that there was no number 10. If Equals" was set to TRUE, the formula would return Niels Nielsen" because he is member number 9, which is the highest number that is less than or equal to the lookup value which is 10.

When equals is set to FALSE, only the exact value will be sought after, and in the aforementioned case, the formula would show a mistake, because there were no members with the number 10.

When the formula cannot find the lookup value, it shows the error code FIT.

1.2.2 HLOOKUP (Horizontal Lookup)

HLOOKUP works exactly like WLOOKUP, but instead of searching. downwards in the first column, it

searches along the first row. Instead of a column number there is also a row number.

1.3 Mathematical and Statistical Functions

This is an overview of the main mathematical and statistical functions.

1.3.1 SUM

Calculates the sum of numbers in one or more cell regions.

1.3.2 AVERAGE

Calculates the average number in one or more cell regions. Empty cells and cells with text are ignored in the calculation.

1.3.3 SOROOTSquare Root)

This Function is self-explanatory. It calculates the square root of a number.

1.3.4 Trigonometric Functions

Excel has the usual trigonometric functions SINO COSO TANO and the inverse arcsin(), arccos() and arctan. In addition there are the hyperbolic versions, like SINHO, COSHO and TANHC).

Please note that angles are expressed in radians.

1.3.5 PI

PI() makes no arguments, but is just the known constant with 14 decimal places.

1.3.6 ABS (AbsoluteValue)

Has nothing to do with carbrake systems, but returns the absolute value of a number. If the argument is a positive number, only the number is returned. If the argument is a negative number the number is

returned as a positive number.

1.3.7 SHORTEN

SHORTEN (number, number of decimal places) returns a number with the specified number of decimal places. It is almost like rounding off, but SHORTEN ignores rounded off and simply deletes the excess decimals. If the argument "Decimal Places" is left out, the number is shortened to 0 decimal places, that

is an integer.

1.3.8 ROUNDOFF

Works like SHORTEN and uses the same arguments. But here the figure is rounded off normally. The argument "Decimal places" is not optional in the ROUND OFF function.

1.3.9 MIN (Minimum Value)

MIN (valuel value2,...) or MIN(cellareal; cell area2, ...) Returns the smallest value of a quantity. The

argument is one or more cell areas and/or two or more values.

1.3.10 MAX (Maximum Value)

ls of course the opposite of the MIN function.

1.3.11 SUMIF (Conditional Sum)

Adds numbers in a table together on condition that they, or other values in same row, meet a certain

criterion.

In the following example we have a small table with two columns. The first column contains some text,

and the second some figures. In the example, we put the figures together that are on the same line as the text "blue":

1.3.12 RAND (Random Numbers)

This function has no arguments but returns a random number between 0 and 1. The value changes over time when making a new calculation in the spreadsheet.

1.3.13 COUNT(Number of Cells with Numbers)

COUNT(cell area) Counts the number of cells in an area that contains numbers. Empty cells and cells

with text are not counted. Cells with formulas that return figures are counted.

1.4 Logical functions

Logical functions do not return a number, but a response in the form of TRUE, FALSE or an error

code. I often use the logical functions to get a formula to choose between different methods depending

on some values.

1.4.1 AND Function

AND(statement statement;2;...) evaluates one or more statements. If all allegations are true, it returns

TRUE. If only one statement is false, it returns FALSE. An example might be:

=AND(2-3:4=4:5-4) returns TRUE, since all statements are accurate.
=AND(2-3,4-4,5-4) returns FALSE, since one of the statements is wrong.

In practice we often use cell references or nested functions instead of numbers.

1.4.2 OR Function

ls used in the same way as the AND function, but here returns TRUE if just one claim is correct.

1.4.3 NOT Function

NOT(statement) returns FALSE if the allegation is true and TRUE if the allegation is false. You could also say that it lies about the outcome!

1..4 4 IF Function

IF(statement: TRUE"range; FALSE"range") evaluate a statement. If the claim is TRUE, it returns that

which is in the true part, otherwise it returns what is in the false part.

1.4.5 FERRORFunction

IEERROR(formula: result if error) shows the result of a formula if it can be calculated. If it cannot be calculated, an alternative outcome is shown. It could, for example, be if you try to divide something by 0, which returns an error.

= IEERROR23:10) returns 6, since the formula 23 can be calculated =IFERROR(20:10) returns 10, since the formula tries to divide 2 by 0, which returns an error value. Instead it shows the alternative result.

1.5 Analyse Datawith Analysis Toolpak

Excel has an add-on called "Analysis ToolPak, which can perform a statistical analysis of a quantity of data. Analysis Tool Pakis an add-on that is not installed initially, so it must beloaded before you can use it.

1.5.1 installation of Analysis Toolpak

To install Analysis Toolpak, do the following:

. Click on the Office Button top left on the screen.

2. Click on the Excel Option button. 3. In the left side of the window that opens, click on Add-Ins. 4. Select Analysis Toolpak from the list of add-ins. 5. At the bottom of the window, make sure that by "Manage there is Excel Add-Ins. 6. Click on the Finish button. 7. In the box that opens, ensure there is a "check" mark next to Analysis Toolpak. 8. Click on OK.

During this procedure, the program might require the installation of something from the installation disk, which you should just go ahead and do.

1.5.2 A Quick Analysis with Analysis Toolpak

In Figure 131, there are some numbers which we will use for a small analysis

            Figure 02

• Make a list like the one in Figure 02.
• Click on the Data Tab in the Ribbon. 
• Click on the Data Analysis button, which is located furthest to the right in the Ribbon if you have installed Analysis Toolpak.
• A window opens with a list of tools for analysis. Choose Descriptive Statistics and click OK.

      Figure 03

• A new window opens, where you must specify a number of settings. Set the settings as shown in Figure 03.
• Click OK.

A new Tab is added to the Workbook. It is called "Score" and contains the results of the analysis. If you frequently need to perform a statistical analysis on a volume of data, the Analysis ToolPak in many cases can save you a lot of time.

1.6 GoalSeek

GoalSeek is a function to adjust a cell value, so a calculation will have a desired outcome. Let us illustrate this with an example.

Accountant Sigvardtsen has prepared a budget for the global firm NAILS & SCREWS Inc.(Figure 04). Sigvardtsen proudly presents it to the boss, who frowns and asks: "How many nuts must we sell to avoid a deficit?

Budget that needs to be balanced. In his budget, Sigvardtsen projects that he will sell 500,000 nuts at a price of DKK 1.40 each, but the boss is not happy with a deficit of 127,500.00. He needs at a minimum to balance the budget.

In this case, it is easy to figure out how many more nuts you have to sell to get rid of the deficit, but some problems might be harder to calculate. Sigvardtsenhappens to be an Excel expert, so he uses Goal Seek to solve those cases.

• He clicks on the Data Tab in the Ribbon, then on the What If Analysis button.

The "What-if anaiysis" Botton.

Setting for Goal Seek.

• In the menu that appears, he clicks on Goal Seek. 
• He indicates that cell E14 should reach the value 0 by changing cell C4.
• When he clicks OK, Excel reports that a solution has been found, and he clicks OK.

The revised budget.

Sigvardtsen can now tell his boss that they need to sell 591,071 nuts to balance the budget.