The JLD Excel Blog - Tips and Help for MS Excel

Backgrounds and watermarks in Excel spreadsheets.

2006-06-12T21:47:00.000+03:00

People who downloaded the World Cup Tournament Calendar noticed the background with the official logo

So, how do you insert a background in an Excel worksheet? Follow these steps (source):

1 - Click the worksheet that you want to display with a sheet background. Make sure only one worksheet is selected.
2 - On the Format menu, point to Sheet, and then click Background.
3 - Select the picture that you want to use for the sheet background, and then click Insert.

Because a sheet background is not printed, it cannot be used as a watermark, but you can mimic a watermark. If you want to display a graphic on every printed page (for example, to indicate that the information is confidential), you can insert the graphic in a header or footer. This way, the graphic appears behind the text, starting at the top or bottom of every page. You can also resize or scale it to fill the page.
The steps to follow are:

1 - On the View menu, click Header and Footer.
2 - In the Page Setup dialog box, click Custom Header or Custom Footer.
3 - Click in the Left section, Center section, or Right section box.
4 - In the row of buttons in the Header or Footer dialog box, click Insert Picture and then find the graphic you want to insert.
5 - Double-click the graphic to insert it in the header or footer section box.
6 - To resize or scale the graphic, click Format Picture in the row of buttons in the Header or Footer dialog box and then, in the Format Picture dialog box, select the options you want on the Size tab.

Categories: Miscellaneous_

Technorati Tags: Watermarks, Backgrounds

Preventing Excel to display #DIV/0! when dividing by 0 – The shortest formula.

2006-06-09T22:17:00.000+03:00

Sometimes dividing by 0 is inevitable, and Excel will show a #DIV/0! result. To avoid this you can use formulas like =IF(ISERROR(A1/A2),0,A1/A2). A shorter formula will be =IF(A2=0,0,A1/A2).

But the shortest formula I know to perform this was posted by KL in the formula forum of Exceluciones (an Excel forum in Spanish):

=--IF(A2,A1/A2). !!!!

To understand how this formula works you have to notice that:

- the IF function in this example uses only two arguments: the logical_test and the value_if_true.
- Excel associates the logical result FALSE with 0 (zero) and TRUE with 1

In this way, when A2 = 0, the value for the logical_test argument is FALSE, and Excel returns the value for the value_if_false argument, which is FALSE.

The two minus signs ("-") preceding the function are equivalent to multiply the function by 1 (-1 X -1). This will cause Excel to return 0 when the result is FALSE (FALSE x 1 = 0)

Categories: Functions&Formulas_

Technorati Tags: #DIV/0!

If you enjoyed this post add to del.icio.us

Formatting part of a cell content in MS Excel

2006-06-06T22:05:00.000+03:00

Sometimes you need to format part of the content in a cell. For example, you want to produce a reference to a footnote in the column header in cell D1:

The reference number should appear like a superscript.
To do this, follow these steps:

1 – edit cell D1 and add "1" immediately after "March"
2 – Select "1" and press the right button in the mouse to open the Format Cells dialog.

3 – Open the Format cells dialog and check the Superscript option.

Hit Ok and then Enter to finish the cell edit. Add the footnote. Your table will look now like this

In the same way you can change the font and/or the color of part of the cell's content, or use any of the other format options.

Categories: Miscellaneous_

Technorati Tags: Excel

If you enjoyed this post add to del.icio.us

Ranking a list in Excel according to two criteria – A soccer standings table example.

2006-06-02T13:03:00.001+03:00

Following my post about ranking a list in Excel with more than one criterion, here there is a simplified example of a soccer league standing table.
In this example I rank the teams according to points (wins = 3 pts.; draw = 1 pt.; lose =0 pts.) and goals difference.
In this model, you don't have to rearrange the table with Data-->Sort. The teams will appear in the right position automatically.

In this model we use an auxiliary table to do all the calculations.

The standings table uses a formula of nested INDEX and MATCH functions to put the right data in the right place.
The formula in cell B7 is =INDEX(K$7:K$11,MATCH($A7,$U$7:$U$11,0)); the formula in cell C7 is =INDEX(L$7:L$11,MATCH($A7,$U$7:$U$11,0)), and so on.

The formulas in the auxiliary table are:
Cell Q7: =O7-P7, calculates the goal difference

Cell R7: =L7*3+M7, calculates the points (win=3 + draw=1)

Cell S7: =RANK(R7,$R$7:$R$11), calculates the relative position of the team

Cell T7: =SUMPRODUCT(($R$7:$R$11=R7)*(Q7<$Q$7:$Q$11)), calculates a rank number for teams with the same points, according to the goals difference. The rank number begins with 0 (zero), that means, if three teams have the same quantity of points, the one with the biggest goal difference for will rank 0, the second 1 and the third 2. In case of teams with an unique quantity of points the formula retrieves 0 (zero).

Cell U7: =S7+T7, calculates the final position.

Categories: Functions&Formulas_

Technorati Tags: Excel RANK

If you enjoyed this post add to del.icio.us

Limiting a worksheet scroll area in Excel

2006-05-31T22:21:00.000+03:00

When you want to protect formulas or data in a worksheet you can hide columns and/or rows, hide formulas and more and then apply the Tools--->Protection menu.
But if you want to limit the users to a certain area in the worksheet, that means, to create a "free zone" and an "out of limits" zone in you worksheet, you can use the following method.
Suppose you have a standings table like in my post on ranking a list with RANK and SUMPRODUCT functions:

Now suppose you want to limit the user to the range B5:J19, protecting the auxiliary columns and preventing him to move to any cell outside this range. To do this follow these steps:

1 – Open the Control Toolbox bar

2 – Press the Properties button. The Sheet1 Properties window will open

3 – In the ScrollArea line enter the range B5:J19

Finally, close the Properties window. From this moment there is no way to select any cell outside the range, until you open the properties window again and change or delete the range.

Categories: Miscellaneous_

Technorati Tags: ScrollArea

World Cup 2006 Tournament Calendar in MS Excel

2006-05-29T00:00:00.000+03:00

If you like soccer and Excel, here is a must: a World Cup 2006 Tournament Calendar developed by the people of Excely.com in a plain Excel worksheet.

This calendar works without macros and in their own words: The calendar is an ideal tool to keep track of World Cup 2006 and impress your friends with insightful predictions.

World Cup 2006 Tournament Calendar is available free of charge from the Excely.com internet site. The calendar speaks 20 languages and supports different time zones. Just download it, extract the archive and enjoy the comfort of the automated game calendar.

Categories: Miscellaneous_

Ranking a list with more than one criteria in MS Excel – using the RANK and the SUMPRODUCT functions

2006-05-27T19:01:00.000+03:00

I've already discussed the use of the RANK function in my post about ranking values in a list with Excel functions. The RANK function returns the rank of a number in a list of number.
I was asked by one of my readers in the JLD Excel blog in Spanish, how to build a soccer standings table according to these criteria:
1 – if two or more teams share the same position, then they will rank according to the number of goals for.
2 – If the draw persists the criteria will be the best goal difference (goals for – goals against).

In this case we have to rank the teams according to three criteria: points, if the have the same score of points then according to the quantity of goals for and in case the have the same quantity of points and goals for then according to the goal difference.

Of course, RANK can't manage this. The solution I found is to combine RANK with SUMPRODUCT, and the use of some auxiliary columns.

Suppose this standings table (press here to download the workbook):

As you can see Teams 4, 5 and 6 and teams 7, 8 and 9 have the same points.

The solution: create tree auxiliary columns holding formulas to rank the teams according to each criterion (points, goals for, goals difference) and combine the three in a fourth column holding a formula that will show the final standing for each team.

In our example we add columns K, L and M

with these formulas:

Column K: =RANK(J5,$J$4:$J$19). When you use RANK you evaluate the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. When two numbers in the list are equal Excel will assign them the same rank. In our case we'll take advantage of this "problem".

Column L: =SUMPRODUCT((J5=$J$4:$J$19)*(G5<$G$4:$G$19)). This formula will give a rank number for teams with the same points, according to the quantity of goals for. The rank number begins with 0 (zero), that means, if three teams have the same quantity of points, the one with the biggest quantity of goals for will rank 0, the second 1 and the third 2. In case of teams with an unique quantity of points the formula retrieves 0 (zero).

Column M: =SUMPRODUCT((J5=$J$4:$J$19)*(I5<$I$4:$I$19)). This formula works the same than the one in column L, but with the goals difference criteria.

Finally we add the fourth auxiliary column in column A with this "mega-formula":

=RANK(J5,$J$4:$J$19)+SUMPRODUCT((J5=$J$4:$J$19)*(G5<$G$4:$G$19))+SUMPRODUCT((K5=$K$4:$K$19)*(L5=$L$4:$L$19)*(M5>$M$4:$M$19))

This formula has three sections:

RANK(J5,$J$4:$J$19) will retrieve the rank number of each team. When teams have the number of points, RANK will give them the same rank number.

SUMPRODUCT((J5=$J$4:$J$19)*(G5<$G$4:$G$19)): the first part of the formula evaluates if there are another teams with the same number of points. If not, the result will be 0 (zero) which will not affect the rank number of RANK. If there are other teams with the same number of points, a rank number starting with 0 will be retrieved for each team, according to the quantity of goals for.

SUMPRODUCT((K5=$K$4:$K$19)*(L5=$L$4:$L$19)*(M5>$M$4:$M$19)): this part works with two criteria, same number of points and same quantity of goals for.

Now you can sort the table according to column A with Data-->Sort

Categories: Functions&Formulas_,Array Formulas_

Technorati Tags: RANK, SUMPRODUCT

If you enjoyed this post add to del.icio.us

Converting a Decimal Number in Excel to hh:mm:ss Format

2006-05-24T06:09:00.000+03:00

Some attendance systems produce reports that use decimal notation. For example, the total for a worker who worked from 8:00 to 17:30 appears in the report as 9.50. The total for another worker who worked of 8:00 to 17:45 will appear in report as 9.75.
When these data are exported to Excel, the total hours worked by both workers will appear in Excel as 9.50 + 9.75 = 19.25. This result could be misleading. The total of worked hours is 19 hours and 15 minutes.
In order to convert the hours and minutes in format decimal to the format hh: mm: ss in Microsoft Excel we use the following formula: ="Hours.minutes in decimal format"/24.
This formula will return a serial number. In order to show the serial number in the corresponding format, change the number format in the Format--->Number menu to hh: mm: ss.

If we want to turn minutes in decimal format to hh: mm: ss, we'll use the formula:

="minutes in format decimal" /1440.

Finally if we want to turn seconds in decimal format to hh: mm: ss, we'll use:

="seconds in format decimal" /86400

We use these numbers, 24, 1440, 86400, because as I explained in the post about dates and time in MS Excel, Excel uses decimal numbers to carry out calculations of time. The unit (1) represents a complete day, that is equivalent to 24 hours, 1440 minutes and 86400 seconds.

Categories: Miscellaneous_

Technorati Tags: Time calculations in Excel

If you enjoyed this post add to del.icio.us

MS Excel Custom Number Formats.

2006-05-21T21:32:00.000+03:00

One of the most interesting features in Excel is the ability to create custom number formats. You do this activating the Format Cells--->Number--->Custom menu

It's important to keep in mind that number formatting only affects the way the value is displayed in the workbook, and not the actual number. You can find an example of this in my post on dates and time in MS Excel, and in my post on calculating elapsed time with MS Excel.

The need to create a custom format number arises when you can't find an appropriate format in one of the number format categories in Excel.

Suppose you want to display your figures with the word "Euro" after the numbers, and negative numbers in red and between parentheses. You can use this format:

#,###0 "Euro";[Red](#,##0) "Euro";

The syntax for custom number format has four sections:

Positive number format; negative number format; zero format; text format

You don't have to use all the sections. If you use one section, the format will apply to all values. If you use two sections, the first applies to the positives numbers and zeros, and the second to the negative numbers.

Excel uses codes to define the format in each section. Part of these codes are:

"General": displays the general format.

"#": displays only significant digits and does not display insignificant zeros.

"0" (zero): displays insignificant zeros if a number has fewer digits than there are zeros in the format.

"?": adds spaces for insignificant zeros on either side of the decimal point so that decimal points align when formatted with a fixed-width font. You can also use "?" for fractions that have varying numbers of digits.

"%": to display numbers as a percentage of 100, include the percent sign (%) in the number format.

"text": to display text as in our example use double quotation marks.

[color]: to set the color for a section of the format, type the name of one of the following eight colors in square brackets in the section. The color code must be the first item in the section. [Black], [Blue], [Cyan], [Green],[Magenta], [Red], [White] [Yellow].

You can conditional formats too. To set number formats that will be applied only if a number meets a condition you specify, enclose the condition in square brackets. The condition consists of a comparison operator and a value. For example, the following format displays numbers less than or equal to 100 in a red font and numbers greater than 100 in a blue font.

[Red][<=100];[Blue][>100]

For a most efficient way to apply conditional formatting use the Conditional Formatting command on the Format menu.

Categories: Miscellaneous_

Technorati Tags: Custom Number Format

If you enjoyed this post add to del.icio.us

Calculating elapsed time with MS Excel – Time Functions Overview

2006-05-16T20:34:00.000+03:00

In a previous post I discussed the "negative time problem" when calculating elapsed time and a post with a general explanation about how Excel deals with date and time calculations.

In this post I'll write a quick overview on time related functions in MS Excel. To download the workbook with the time related functions click the link.

NOW() - Returns the serial number of the current date and time. If the cell format was General before the function was entered, the result is formatted as a date. The NOW function is a "volatile" and changes only when the worksheet is calculated or when a macro that contains the function is run.
The formula "=NOW()" will return "16/05/2006 20:05". If you want to the current time without the associated date, you can use the formula "=NOW()-TODAY()". You have to format the cell with an appropriated time format like "hh:mm:ss".

HOUR() - Returns the hour of a time value. The hour is given as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.). For example "=HOUR(0.885)” returns "21:00", being 0.885 the serial number for 21:14:24.

TIMEVALUE(time_text) - Returns the decimal number of the time represented by a text string. The decimal number is a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.). For example, "=TIMEVALUE("21:14:24")" returns 0.885.

MINUTE(serial_number) - Returns the minutes of a time value. The minute is given as an integer, ranging from 0 to 59. In our example, "=MINUTE(0.885)" returns 14.

SECOND(serial_number) – the as MINUTE for seconds.

TIME(hour,minute,second) - Returns the decimal number for a particular time. If the cell format was General before the function was entered, the result is formatted as a date. The decimal number returned by TIME is a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).
The interesting thing about TIME, is that this functions corrects automatically "invalid" arguments. For example, if you use 65 as argument for the minutes "=TIME(21,65,24)", Excel will return 10:05 PM, adjusting the hour to 22:00 (or 10 PM) and the minutes to 05.

Categories: Functions&Formulas_

Technorati Tags: Time Functions

If you enjoyed this post add to del.icio.us

Calculating elapsed time in MS Excel – When the difference between times results in a negative value.

2006-05-12T18:05:00.000+03:00

In a previous post about dates and time in Excel, I explained that Excel "sees" dates as whole numbers. In other words, dates in Excel are a series of whole numbers starting with 1 for the 1st of January 1900 (or 1904, as explained in this previous post). Hours are calculated by Excel as fractions of the number 1. For example, 6:00 in the morning will be to Excel 0.25.
When using base 1900, if the result of a time calculation is a negative value, Excel will not be able to represent the result as a date. Instead you will see a series of # signs (#######).
The "negative time problem" generally occurs when calculating elapsed times, like calculating the hours worked by workers in a shift. Consider this example of a negative result when calculating the difference between times:

Because the shift of Worker 2 ended next day at 3:00 in the morning, the formula in cell D9 (=C9-B9) results in a negative value.
There are several ways to overcome this problem. The obvious one is to use the ABS function, which returns the absolute value of the operation. But in certain cases, like in our example, you can't use ABS; it will return a wrong result!
You can "download the workbook with the different alternatives to solve this problem:

1 – Enter the hours with the corresponding dates, like "08/05/2006 19:00". To Excel the number representing the start hour will be "38845.79167". The number for the end hour (which includes the date, the next day at 3:00 in the morning) is " 38846.125" and the difference between end and start will result in a positive number.

2 – Use a formula as in cell D21: " =C21+(C21"<"B21)-B21". When the end time is smaller than the start time (C21"<"B21) will be equal to 1 (TRUE), and will be added to C21 (1 = a whole day)/

3 – Use the MOD function as shown in cell D27: =MOD(C27-B27,1).

The MOD functions returns the remainder after number is divided by divisor. According to the on-line help the MOD function can be expressed in terms of the INT function:

MOD(n, d) = n - d*INT(n/d).

In our case "n" is 3:00 AM – 7:00 PM, but to Excel it is (0.125 - 0.791666667) which results in -0.66666667

n-d*INT(n/d) will be -0.66666667 -1*INT(--0.66666667/1), which is -0.66666667-(-1) = 0.33333333 = 8:00 hours

Categories: Functions&Formulas_, Miscellaneous_

Technorati Tags: Excel Time Calculations

If you enjoyed this post add to del.icio.us

Dates and Time in MS Excel

2006-05-09T21:24:00.000+03:00

Excel represents dates and hours (time) as serial numbers. Whole numbers represent a date and decimals the hour. The date 1-January-1900 is represented by number 1. The hours are represented by fractions of 1, for example 6 in the morning, the fourth part of the day, is represented by number 0.25.
This way, when we see in a cell of Excel the date of today, 9-May-2006, Excel “sees” the number 38846, the number of days from 1-January-1900 to the present day. The number 38846.25 means the 9th of May at 6:00 in the morning.
Excel supports two different bases to calculate dates and time: 1-January-1900 or 1-January-1904. Excel supports base 1904 for compatibility reasons with the version for Macintosh.
The difference between base 1900 and base 1904, is that with 1904 Excel allows calculations of time with negative results, which is “illegal” with base 1900.
In the next post on this subject I will give an example.
By definition Excel installs base 1900. In order to change to 1904, it is necessary to activate the menu “Options” and in the “Calculation” tab, check option 1904

Categories: Miscellaneous_

Technorati Tags: Dates and Time in Excel

If you enjoyed this post add to del.icio.us

Data Analysis with MS Excel – Sensitivity analysis with the Data Table menu.

2006-05-05T17:42:00.000+03:00

A sensitivity analysis shows how changes in certain values in a model will affect the outcome of the formulas in the model. For example, how changes in the volume of sales and variable costs rate will affect the net profit rate.
With Excel you can perform this analysis easily using the Data--->Table menu.
Suppose this simplified P&L report (download the Data Table example)

This will be the sensitivity analysis mentioned before:

You can build this table entering a formula in each cell, or, if you are an intermediate to advanced user, enter one array multi-cell formula.
Instead, you can use the Data--->Table command.
To build this Data Table with Excel follow these steps:
1 – Build a table, with a reference to the formula being evaluated in the let upper corner; values for one of the variables in the upper row and values for the second variable in the leftmost column of the table

2 – Select the table, including the blank cells (in our example select the range B12:G17)

3 – Activate Data--->Table from the menu. In the dialog that opens you have to enter a reference to the row input cell (Sales, B4 in our example) and to the column input cell (variable costs rate, C6 in our example).

Press OK and Excel will build the sensitivity analysis table automatically.

4 – You can hide the reference to the formula being evaluated (or the formula itself) in the upper left corner of the table, formatting the font with the same color like the background.

This example shows a two-variable data table. There are also one-variable data tables.

Categories: Data Management_

Technorati Tags: Data+Table

If you enjoyed this post add to del.icio.us

Using MS Excel's SUBTOTAL function.

2006-05-01T21:17:00.000+03:00

I've already discussed the use of the SUBTOTALS command in the Data menu. Generally is easier to create a list with subtotals using the Subtotals command, which uses the SUBTOTAL function.
With the SUBTOTAL function you can consolidate data in many ways, like calculating an average for a subset of data in a list and other operations.

The syntax of SUBTOTALS is:

SUBTOTAL(function_num,ref1,ref2,...)

Function_num is a number that specifies which function to use in calculating subtotals within a list. These functions are:

1 - AVERAGE
2 - COUNT
3 - COUNTA
4 - MAX
5 - MIN
6 - PRODUCT
7 - STDEV
8 - STDEVP
9 - SUM
10 - VAR
11 - VARP

Ref1, ref2, are 1 to 29 ranges or references for which you want the subtotal.

A very important characteristic of this function is that SUBTOTAL will ignore any hidden rows that result from a list being filtered using the AUTOFILTER command. This is important when you want to subtotal only the visible data that results from a list that you have filtered.

You can check this in this file containing examples of the use of SUBTOTALS with filtered lists (see the AVERAGE and the SUM sheets in the workbook).

A cool use of SUBTOTAL in a filtered list is shown in the COUNTA sheet. In the list each member has a correlative number (1 for the first member in the list, 2 for the second and so on). Using SUBTOTAL with the COUNTA function as first argument, the numbering stays correlative for the visible rows.

Categories: Functions&Formulas_

Technorati Tags: SUBTOTAL

If you enjoyed this post add to del.icio.us

Linear regression analysis with MS Excel Data Analysis tool.

2006-04-26T22:42:00.000+03:00

To perform regression analysis with Excel, activate the Tools--->Data Analysis menu. This will open a dialog, where you can choose, among others, the Regression option

If the Data Analysis menu is not available, you need to install the Data Analysis Toolpak. To do this, press the Tools--->Add-Ins menu, and check the Analysis Toolpak option.

The Regression analysis tool performs linear regression analysis by using the "least squares" method to fit a line through a set of observations. You can analyze how a single dependent variable is affected by the values of one or more independent variables.

Let's see how the regression analysis works in Excel with this example (click the link to download):

To run the regression analysis, open the Data Analysis menu:

The different options in the dialog are almost self-explanatory. In our example, the data ranges included the labels, so we checked "labels". You can decide where to place the results, in the active worksheet, in another sheet in the current workbook, or in a new workbook.

The summary output of our example will be

You can see the output in the workbook with the example.

Categories: Data Management_

Technorati Tags: Linear Regression

If you enjoyed this post add to del.icio.us

Excel FORECAST function – predicting a future value by using existing values

2006-04-22T22:43:00.000+03:00

The very first post in this blog was about how to find where two curves cross in a graph. There I proposed to use the Excel's trendline feature and the SOLVER add-in.

If your task is to predict a future value by using existing values, then you can use the FORECAST function.

Description in the online Help is:

FORECAST: Calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value. The known values are existing x-values and y-values, and the new value is predicted by using linear regression. You can use this function to predict future sales, inventory requirements, or consumer trends.

Suppose a table where you list quantities sold according to price level

To predict the number of sold units using FORECAST (click the link to download the workbook with the example) when price is 8, we use this formula:

=FORECAST(A11,B4:B10,A4:A10), where cell A11 holds the data point for which you want to predict a value

You can use also the Trendline feature. Create a XY graph, then point to the curve and right click to open the Add Trendline menu. In the Type tab, choose Linear, and in the Options tab check "display equation on chart".

Excel will display the equation in the graph

Now you can calculate the predicted value with a simple worksheet formula,

=-417.86*B16+10771

where B16 (see the example in the worksheet) holds the data point for which you want to predict a value.

Categories: Functions&Formulas_

Technorati Tags: FORECAST

If you enjoyed this post add to del.icio.us

Array formulas and array constants in MS Excel

2006-04-21T21:31:00.000+03:00

I've already discussed the concept of array formulas in a post where I showed how to calculate a weighted average with Excel's array formulas.
In this post I will discuss briefly two other aspects: "multi-cell" array functions and array constants.
Like with any other formula, the array formula I discussed in that previous post will retrieve the result in the cell you entered the formula. But with Excel, you can create "multi-cell" formulas. I'll show this with an example (you can download the workbook with the multi-cell array formulas and array constants pressing the link).
In the previous post we calculated the weighted average for the age of the members in this list

with a "single-cell" array formula {=SUM(A6:A16*B6:B16)/SUM(B6:B16)}

With the multi-cell technique we will proceed this way:

1 – Select the C6:C16 range
2 – Enter the =A6:A16*B6:B16 formula in cell C6 as an array formula (pressing Ctrl+Shift+Enter). Excel will place brackets around the formula,

As you can see, the formula is the same in all the cells in the range. Remember that arrays must be the same size and direction (you can use TRANSPOSE when you have a mix of horizontal and vertical arrays).
Because Excel prevents you from deleting or changing individual cells in a multi-cell array, you can use this technique to protect you formulas from accidental overwriting.

More interesting, in my opinion, is the array constant feature in Excel. So far we saw examples where the arrays were stored in worksheets' cells. But arrays don't have to be stored in cells. For example, we can create these arrays: {1,2,3} and {4,5,6} and then use them in this (regular) formula =SUM({1,2,3}*{4,5,6}). This is equivalent to =SUM(1*4+2*5+3*6)

Arrays can be one-dimensional or two-dimensional. One dimensional arrays can be horizontal oriented or vertical oriented. Use commas (",") to generate horizontal arrays, and semicolons (";") for vertical arrays (please, refer to the example workbook).

The best way to work with array constants is using names. For example, in the workbook example we defined the name "week" as ={"Mon","Tue","Wed","Thu","Fri","Sat","Sun"}

Then we used the array in this formula =INDEX(weekdays,2) which retrieves "Tue".

Categories: Array Formulas_

Technorati Tags: Array Formulas

If you enjoyed this post add to del.icio.us

Using MS Excel SUMPRODUCT function with column and row mixed ranges

2006-04-17T22:28:00.000+03:00

I've already discussed in previous posts how to calculate a weighted average using SUMPRODUCT, summing and counting with SUMPRODUCT and how to perform conditional summing and counting with more than one criterion with SUMPRODUCT.

In all these post we assumed that we meet two mandatory conditions:
1 – Ranges being evaluated are identical in size;
2 – The ranges are of the same kind: rows or columns.

The Excel SUMPRODUCT function will not accept a mix of rows and columns in the same function. If you have a situation like this, the way to overcome the problem is to use the TRANSPOSE array function.

Generally we have a situation like this (you can download the SUMPRODUCT-TRANSPOSE example pressing the link):

The formula in cell F8 is =SUMPRODUCT(B4:B13,C4:C13). As you can see both ranges are columnar.

But suppose that the price list is set in a row range, as shown in the range A17:K17 in the example

As said, we'll use the TRANSPOSE function to convert the A17:K17 range in columnar range. TRANSPOSE definition in Excel's help:

Returns a vertical range of cells as a horizontal range, or vice versa. TRANSPOSE must be entered as an array formula in a range that has the same number of rows and columns, respectively, as an array has columns and rows. Use TRANSPOSE to shift the vertical and horizontal orientation of an array on a worksheet.

Because TRANSPOSE is an array formula, we will have to enter the SUMPROUCT function as an array formula too (pressing Ctrl+Shift+Enter)
The formula in cell D20 is ={SUMPRODUCT(B4:B13,TRANSPOSE(B17:K17))}
The "{" in the beginning and the "}" at the end of the formula are added automatically by Excel when pressing Ctrl+Shift+Enter.

We can use, alternatively, the SUM function as an array formula, as in cell D21 in the example: ={SUM((B4:B13)*TRANSPOSE(B17:K17))}

Categories: Functions&Formulas_, Array Formulas_

Technorati Tags: SUMPRODUCT, TRANSPOSE, Array Formulas

If you enjoyed this post add to del.icio.us

Create an Histogram with Excel Data Analysis Add In

2006-04-13T22:42:00.000+03:00

Suppose you have a list of names of and their age.

You want to group the members in the list according to some intervals and to count how many members are in each interval.
You can do this with formulas. But you can take advantage of the Histogram add-in the Analysis Tool Pack.
In order to use the Histogram you have to install the Analysis Tool Pack. To do this activate the Tools--->Add Ins, and check the Analysis Tool Pack

Once you activate the Analysis Tool Pack, the Data Analysis menu will be added to the Tools menu.
Let's see how to create an Histogram with Excel Data Analysis (click the link to download the workbook with the example).
First we have to create a "bin range", a range containing the values for the intervals

Then we open the Data Analysis menu and choose Histogram

In the dialog box enter the data range, the bin range and the output definitions

Press OK and you get the frequency table almost instantly

Categories: Data Managemet_, Miscellaneous_

Technorati Tags: Histogram

If you enjoyed this post add to del.icio.us

Placing controls into Excel worksheets – Part II

2006-04-11T22:10:00.000+03:00

In the previous Placing controls into Excel worksheets post, I discussed the use of controls in Excel worksheets. You don't need to know Vba (or use macros) in order to take advantage of the controls' functionality in a worksheet.

Controls give to your worksheets a more "professional" look, but most important, give you the power to control the data input.
In general, you link control to a cell in the worksheet. Some controls will retrieve a value in the linked cell, like TRUE or FALSE, 0 or 1, or a value from a list of values.

When you want to place a control into a worksheet follow these steps:

1 – Open the Forms toolbar

2 – Activate the control and drag it into the desired location

3 – Activate the control (right click) and open the Format Control menu (Ctrl+1 or from the menu Format--->Control)

In the "Control" tab of the Format--->Control menu you set the control's definitions.
In this post I'll show the use of the "spinner" and the "scroll bar" controls. You can download the workbook with the spinner and scroll bar example here.
The example is a simple model to calculate the monthly payment for a loan given the interest rate, the number or periods (term) and the amount of the loan. We use the spinner to control the number of periods and the scroll bar to control the amount of the loan.

The definitions of the spinner are easy to understand

Pay attention to the fact that the linked cell is an argument of the formula that calculates the payment.

The same is valid for the scroll bar

As you can see both linked cells are arguments in the =PMT function in cell C13 of the model

Categories: Functions&Formulas_, Miscellaneous_

Technorati Tags: Excel worksheet controls

If you enjoyed this post add to del.icio.us

Excel RANK function – Two values with the same rank.

2006-04-05T23:04:00.000+03:00

In this post I discussed the use of some "ranking" Excel functions like SMALL, LARGE and RANK.
When you use RANK you evaluate the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list.
But, what happens when two numbers in the list are equal. In that case Excel will assign them the same rank.

As you can see, the rank for B5 and B6 is the same, 14, and there is no 15th rank.
If you want to have a rank of 14 for the first appearance of the value and a rank of 15 for the second, enter this formula in cell D4 and copy-paste to the rest of the range:

=RANK(B4,sales)+COUNTIF($C$4:C4,C4)-1

The COUNTIF function evaluates how many times the rank in the current row appears. If it appears only once, COUNTIF($C$X:CY,CY)-1 will retrieve 0, and the result for the RANK function will not change. If the rank value appears twice, COUNTIF will add a value of 1 to the value of the RANK function.

You can download the workbook with the example here.

Categories: Functions&Formulas_

Technorati Tags: RANK

If you enjoyed this post add to del.icio.us

Consolidating Data with Pivot Tables

2006-04-01T16:23:00.000+03:00

We have already saw different ways to consolidate data:
1 – Consolidating Data with Excel
2 – Data consolidation with Subtotals

You can also consolidate data from different worksheets using the pivot table feature.
Suppose you have a certain number of sales agents reporting sales by products and months. You have one worksheet for every sales agent (you can download the workbook here).
Your task is to consolidate the different reports in one report. I'll show you how to do the consolidation with an example.
In our workbook we have two sheets (one for each agent) and another sheet ("cons") where we will consolidate the data.
The steps to follow are:
1 – Open the Pivot Table menu. In step1 check "multiple consolidation ranges"

2 – In step 2 you have to set the page field. In our example we will check "create single page field".

3 – In step 3 you have to tell Excel where the data ranges are. Enter the ranges in the "Range" window of the dialog and press add (once for each sheet)

4 – In this step you instruct Excel where to place the pivot table report

Hit "Finish" and you'll get the consolidated report

You can use the Page control in order to see data from each agent (they will appear as Item1 and Item2) or consolidated data (All).

Because the columns headers are formatted as dates we can use the pivot table functionality to consolidate the data by quarters.

How? Follow these steps:
1 – Right click the "Column" field (or one of the column's headers), click "Group and Show Detail" ---> "Group"

2 – Uncheck the "Months" option (click once) and check "Quarters"

Excel will consolidate the data by quarters automatically.

Categories: Functions&Formulas_, Data Management_

Technorati Tags: Data Consolidation, Pivot Table

If you enjoyed this post add to del.icio.us

Ranking values in a List with Excel Functions – SMALL, LARGE, MIN, MAX and RANK.

2006-03-31T14:34:00.000+03:00

Para leer esta entrada en español, apretar aquí

Suppose a table like this in a worksheet (to download the worksheet with the examples press here)

Now suppose you want to find the largest value, or the smallest, or the 2nd largest (or smallest) or to rank a number (to find its place relative to the other values in the list).
To perform these tasks you can use these functions:

MIN: Returns the smallest number in a set of values
MAX: Returns the largest value in a set of values
SMALL: Returns the k-th smallest value in a data set
LARGE: Returns the k-th largest value in a data set
RANK: Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list.

To investigate these functions, see the examples in the worksheet.

These functions are very useful specially when combined with other functions (nested functions). For example, you can combine RANK with VLOOKUP to return the position of an agent in the list according to his sales, as I did in cell E14 with the formula

=RANK(VLOOKUP(D14,A2:B20,2,0),Sales)

As usual, I use named ranges in my formulas. You can find the list of the names in the worksheet. In cells E14 and E17 I'm using Data Validation to generate an drop list with the names of the agents or ranking places in the list.

Categories: Functions&Formulas_

Technorati Tags: MIN, MAX, SMALL, LARGE, RANK

If you enjoyed this post add to del.icio.us

Placing Controls into Excel Worksheets.

2006-03-28T20:56:00.000+02:00

Para leer esta nota en español, apretar aquí.

You can find two types of controls in Excel which can be used directly in a worksheet. You don't have to write any Vba code to take advantage of these controls. I'll show how, using an example.

Controls are user interface objects such as text box and check boxes that let you control actions.
There are two types of controls in Excel: ActiveX controls and Forms Toolbar Controls

In this post I'll show with an example how to use controls from the Forms Toolbar. You can download the workbook with the example here.

Suppose a business running a Shopping Club. Members are rewarded with a discount and every customer can have an additional discount according to the pay terms he chooses.

In order to build the form I used named ranges, data validation and, of course, control from the Forms Toolbar: one checkbox and three option buttons. The calculation form is placed in Sheet1 and a price list in Sheet2. Our form, before we enter any data, looks like this:

Steps to place the controls in the worksheet are:
1 – Activate the Forms Toolbar and choose the required control (in our case the check box). Place the control in the desired location

2 – Open the Control Format menu. Here you can change the look of he control

3 – In the "Control" tab you will link the control to some cell in the worksheet. In our case we link the check box to cell A3.

When you check the control, value in the cell link is set automatically to TRUE. When the control is unchecked the value in the link cell will be FALSE.

4 – Do the same with the option buttons. Link all the option buttons to the same cell (A7 in our case). Doing so the link cell value will tell us which option button is checked. If the first option button is checked ("Cash") the value in cell A7 will be "1". If "Up to 30 days" was chosen, value in cell A7 will be "2".

Link cells will be arguments in the formulas that control the discount calculation:

- formula in cell C23 is =IF(A3=TRUE,D22*10%,0)
- formula in cell C24 is =D22*CHOOSE(A7,10%,5%,0)

As you can see controls are "controlling" the results in our model.

We use Data Validation with the option "List" in range A16:A21. We have defined two named ranges: Price_List that we use in the VLOOKUP functions in range C16:C21, and Product for the List argument of Data Validation.

To avoid error values (#N/A, #DIV/0!, etc) when input cells are blank, we use the ISBLANK function nested in the formulas.

After we enter data, the form will look like this

You can check this post about using the spinner and the scroll bar controls in Excel worksheets.

Categories: Functions&Formulas_, LOOKUPS_

Technorati Tags: Excel Controls, VLOOKUP

If you enjoyed this post add to del.icio.us

Excel can't open .xls files with macros

2006-03-25T20:47:00.000+02:00

After reinstalling Office (or Excel) for some reason (upgrade for example), when you try to open a file that contains a macro chances are that you will see a dialog like this

By default, Excel's security definition is

To change this definition, just activate the Tools-->Macro-->Security menu

And turn security level to

Medium or Low.

Categories: Miscellaneous_

Technorati Tags: Macros

If you enjoyed this post add to del.icio.us