April 30, 2004

Quick Navigation, Part 1

A couple of days ago I noticed that Dick Kusleika wrote about hyperlinks on his blog, Daily Dose of Excel (I read it everyday, most definitely recommended!). He gave advice about using hyperlinks and it gave me an idea for this post - quick navigation around your workbooks.

There are quite a few ways to move quickly around a workbook. One of the simplest ways is to enter a cell reference such as H176 into the name box (the little box next to the formula bar with a drop down arrow) and push Enter. Presto! You've been teleported there in a flash!



Great trick, but you have to know your destination in advance, otherwise you're going to have to scroll there anyway.

How to get around this? There is at least one way if you are going to navigate there on a regular basis and don't want to burden yourself with the horrific task of remembering multiple cell references.

Using the Insert Menu, select Name and then Define. At the top enter a name such as "AccountsTotals", then at the bottom enter the cell reference (you can use the little red arrow to get there). You can also go to the cell reference and enter the name directly into the Name Box.

Now, you are ready to get around in a jiffy. Just use the drop down arrow in the Name Box and select where you want to go. You can also use the Goto dialog box. Get there by clicking the Edit menu or try either Ctrl + G or just use the F5 button.

So where do hyperlinks come in? Well, first of all you need to open the Hyperlinks dialog box. The easiest way is to use the right click menu, or you could find it in the Insert menu. (The shortcut keys are Ctrl + K)

Select Place in This Document and enter the cell reference. You can do this manually by entering the cell reference and name of your choice, or you can select an existing name if you've defined one already.



When finished, you should see a link appear. It's blue and underlined but you can change the font color and remove the underline if you want to, it's entirely editable as far as appearance goes.

Okay, that's it for today. More on navigation next time I post.  

Posted by andrewe at 18:14

April 29, 2004

LOOKUP

There are different kinds of lookup functions available. VLOOKUP, HLOOKUP, INDEX, MATCH, CHOOSE and many more. But LOOKUP by itself is probably the most simple and easiest to use.

How does it work?
First we need a reference cell and at least 2 lists to make a comparison (cross-reference). In the regular LOOKUP function the 2 lists should be vertical.

Say I wanted a quick reference to the days in a month.

As you can see I used reference numbers from 1-12 fro the months of the year in cells A2:A13 and the days in the month in cells C2:C13. (Cell B2:B13 show the month's names just to make it easier to understand. Although I can use text for reference in LOOKUP, I prefer to use numbers because it is so much faster)



For February, I've entered the month's reference number shown in cell A3 into cell A16. Then in cell B16, I've entered the following formula =LOOKUP(A16,A2:A13,C2:C13). This means that the number I've entered in cell A16 will be searched for in list A2:A13, and then cross-referenced in list C2:C13 to give me a value of 28.

But what about leap years? Okay, I can use another LOOKUP function for this too, by adding a new list in cells D2:D13 and entered this formula into cell C16, =LOOKUP(A16,A2:A13,D2:D13).



Yesterday I used logic formulas to choose alternatives between different customers to calculate their shipping time. With just 2 customers, it was starting to get quite difficult to avoid potential errors in calculation. Now let's apply LOOKUP to the task.

Say my customers have now increased. I have a total of 10 customers and I use 2 different modes of shipping for each of them. (It doesn't matter whether it is by courier, truck, air or sea. The important thing is there are 2 modes available for each customer)

I've entered the customer's reference numbers in cells A2:A11, customer's names in cells B2:B11, and days required for either shipment mode 1 or 2 in cells C2:C11 and D2:D11 respectively.



I enter the customer's reference number in cell A14 and the shipment mode in cell B14.
In cell C14, I have entered this formula, =IF(B14=1,LOOKUP(A14,A2:A11,C2:C11),LOOKUP(A14,A2:A11,D2:D11)).

It means to check the shipment mode in cell B14 and then cross-reference the list A2:A11 with list C2:C11 or list D2:D11 depending on the shipment mode. Then in cell D14 I enter the shipment date, and enter =C14+D14 in cell E14 to give me the arrival date.

Somewhat complex, but trying to do all of this with only logic formulas by themselves would involve a lot more work. LOOKUP does it all for me.  
Posted by andrewe at 18:10

April 28, 2004

More on Logic Functions

I covered quite a bit of territory yesterday and thought some revision might be in order.

Okay, let's use IF, AND and OR for some potential real life applications.

I've mentioned that I use Excel for logistics. To calculate shipping time, I could just use a calendar and count the days, but I could also use Excel to do it for me. Let's consider time required for shipments to arrive at two different customer's locations, one is domestic and the other is overseas.

Customer A (Domestic)
Courier = 2 days (Overnight Courier)
Truck = 3 days

Customer B (Overseas)
Air = 3 days
Sea = 10 days

I enter the following information in the appropriate cells.
Customer Name in cell A1
Shipment Mode in cell B1
Shipment Date in cell C1



Here's a possible formula

=IF(AND(A1="Customer A",B1="Courier"),C1+2,IF(AND(A1="Customer B",B1="Sea"),C1+10,C1+3))

What does this mean?
If I ship to Customer A and I am shipping by courier, I add 2 days to the shipment date in C1.
If this is not the case, then I move on to the second IF formula which says, if I ship to Customer B and I am shipping by Sea then I add 10 days to the shipment date. The third alternative means that I am neither shipping to Customer A by courier or Customer B by sea, so I add 3 days for either shipment by Truck or Air.

Here's the catch. What happens if I enter Customer A into cell A1 and Sea into cell B1 or Customer B into cell A1 and Courier into cell B1? In this case, the above formula will give me an incorrect answer so here is a solution. (Shipping time for truck or air is the same, so it won?ft matter if you mix them up in this case)

To keep things simple, let's say that I had entered the above formula into cell D1 and the next formula into cell E1.

=IF(OR(AND(A1="Customer A",B1="Sea"),AND(A1="Customer B",B1="Courier")),"Please re-select Customer Name or Shipment Mode",D1)

This will have the effect of telling me to re-select the Customer Name or Shipment Mode if I make a mistake or telling me the correctly calculated shipping time calculated in cell D1.

Alternatively I could combine both formulas to be entered in cell D1 like this.

=IF(OR(AND(A1="Customer A",B1="Sea"),AND(A1="Customer B",B1="Courier")),"Please re-select Customer Name or Shipment Mode",IF(AND(A1="Customer A",B1="Courier"),C1+2,IF(AND(A1="Customer B",B1="Sea"),C1+10,C1+3)))

Either way will do the job. But what if there are more than 2 customers or other forms of transport? To accommodate these extra criteria, you could just keep making increasingly elaborate formulas but remember there is a limit as to how many times you can use IF, AND or OR functions.

You could try spreading the formula out to 2 or more cells like above in cells D1 and E1, each cell calculating a part of the overall formula.

Or you could use something like a LOOKUP function. This will simplify things a great deal. I'll be looking at LOOKUP the next time I post.  
Posted by andrewe at 09:54

April 27, 2004

Logic Functions

To the best of my knowledge there are just 6 logic functions within Excel. But these 6 functions should be all that you will ever need.

IF
AND
OR
NOT
TRUE
FALSE

I would like to have a look at the first four of these functions. (To be honest, I don't think the TRUE and FALSE functions are particularly useful by themselves. Also please note that you can combine these functions to make custom made functions such as NOR by using NOT and OR, or NAND using NOT and AND)

IF
This is definitely one of the most useful functions in Excel. It basically works like this, =IF(criteria is true, TRUE, FALSE). For example, if in cell A1 you have the number 10, =IF(A1=10,TRUE,FALSE) will give you TRUE. If I changed the number in A1 to 9, the same function will give me FALSE because A1 does not equal 10.

In addition to the equal sign (=), you can also use greater than (>), less than (<), or make combinations such as <=, >=, and <>. Text is also recognized so formulas such as =IF(A1="ten",TRUE,FALSE) will give me TRUE if the A1 cell had been entered as 'ten' instead of just 10. Upper case or lower case is not affected, you could even write TeN or tEn and still get the same result. Note that numbers, cell references, TRUE and FALSE do not require quotation marks.

You can also change the TRUE and FALSE response to something like =IF(A1="","Please enter something","Thanks"). The double quotation marks with nothing between them mean that the A1 cell is blank.

Finally, you can use equations and other functions combined such as =IF(A1=100,A1+20,IF(A1>100,A1+30,"Less than 100")).

This is an example of a nested IF formula, you can use up to seven IFs together in the same formula, just don't forget to add extra brackets.

AND
You can use this formula by itself to get TRUE or FALSE. It's a multicriteria function, you can add up to 30 criteria that must all be correct to get TRUE. Here's an example - =AND(A1=12,B1="John") will give you TRUE only if both 12 and 'John' are entered into cells A1 and B1.

OR
This is another multicriteria function (up to 30 criteria just like AND). Use this to choose at least one criteria. For example, =OR(A1=25,A1=40,B1=75) will give you TRUE if at least one of the criteria are met. Notice that I used cell A1 twice. You can multi-query the same cell with OR but not AND. There is no way that a single cell can have more than one value at the same time.

NOT
NOT seems to be applicable to just one criteria at a time. No problem, you can use it together with IF, AND or OR, such as in
=NOT(OR(A1=32,B1=60,C1="Overdue",D1=E1+F1)) or perhaps =IF(AND(A1>24,OR(NOT B1>100),C1=TRUE),D1="Paid"),"Great!","Try again").

A bit complex but practice makes perfect :)

Andrew of 2005 says "When using Conditional Formatting, keep in mind that only a TRUE condition is required for it to take effect. For Data Validation (custom), the input must be TRUE to be allowed."  
Posted by andrewe at 00:06

April 25, 2004

More Custom Formatting

I could probably write a book on Custom Formatting and still not cover everything :)

I see that the options offered on my Excel 2003 are becoming more diverse but here are some simple formats that you should know - adapt them to what best suits you. Right click the cell(s) you want to change and select the Format Cells dialog box, then choose Number, Custom.

Numbers & Currency
0 a general number format
0.00 a number with 2 decimal places (add or delete zeros for more or less decimal places)
$0.00 dollar currency with 2 decimal places
#,##0 a number with commas added for every thousand, million, billion...
$#,##0.00 dollar currency with 2 decimal places and commas added for every thousand, million, billion...
#,##0, will round your number to thousands
#,##0,, will round your number to millions

Fractions
# ?/? simple fraction rounded up to 1 denominator
# ?/?? simple fraction rounded up to 2 denominators
# ?/??? simple fraction rounded up to 3 denominators
# ?/???? simple fraction rounded up to 4 denominators (This appears to be the limit for fractions, I don't know the limit for decimals but I can tell you it's a lot!)
# #/4 fraction divided into quarters
# #/16 fraction divided into 16ths
# #/100 fraction divided into 100ths

Dates and Time
Dates (see my previous post)
h:mm:ss hours, minutes and seconds
h:mm AM/PM hours and minutes with AM or PM

Percentage
% Just add a percentage mark to a number format (decimal numbers or commas are okay)

Text
Very useful. You can use this to enter almost anything and what you enter will be what is displayed. Note: You'll have to change to another format in many cases such as entering formulas otherwise they will not work.

@ You can use this to add whatever word you enter to text that is already entered into your format. For example, "This is a "@ will give you 'This is a boat', if you enter 'boat' into the cell. You can enter text before and after the @ mark. (Use double quotation marks for formatting, single quotation marks are just to show my examples)

Colors and Equations
You can do simple conditional formatting by entering colors or equations into square brackets. For example, [Red][=<100] will turn the font red for any number less than or equal to 100, but might cause the value to disappear if over 100. I suggest using a semicolon and using a formula such as [Red][=<100];[Black]. Combinations are also possible, such as [Red][=<100];[Blue][=>200] which means red font for any number equal or less than 100 and blue font for any number equal to or more than 200. (You might find that Excel adds it's General format as an automatic anti-error feature, this usually works to your advantage)

Okay, that's it for now. There is another simple way to make custom formats, I'll cover this sometime in the future.  
Posted by andrewe at 18:07

April 24, 2004

Date Clock

This is something I like to have on my files at work. I call it a Date Clock. It doesn't tell the time, just the date, but it's good to have if you can't remember what day or date it is and want to know in a hurry.

In the cell of your choice enter =TODAY(). You can also use =NOW().

This will give today's date (if you use =NOW(), it will also give the time that you open the file, enter the formula for the first time or it is re-calculated. Not so useful if you are just making a date clock).

The date by itself may be useful, but you can improve on it a number of ways. For example, you can right click the cell and select the Format Cells dialog box. Choose the Number tag and then Custom.

Depending on the format that already exists, you might see something like yyyy/mm/dd. You might also guess that 'y' means year, 'm' means month and 'd' means day. You can make your own custom format such as 'd mmmm, yyyy (ddd)'. Note: Don't enter the quotation marks and period in my example, the brackets are optional.

One 'd' or two 'd's will give you the day of the month, three 'd's or four 'd's will give you the weekday. One to four 'm's will give you the month in either numerical or text format, and any number of 'y's will give the year. I notice in my version of Excel that if I enter just one 'y', it automatically enters two 'y's, and if I enter three 'y's it automatically enters four 'y's. Same situation for more than four 'd's, 'm's or 'y's. It seems to be an automatic correction feature.

Anyway, the above 'd mmmm, yyyy (ddd)' custom format should give you 24 April, 2004 (Sat), at least for today. You can also add hyphens, slashes, brackets and spaces as well as text (use commas such as "Today is "). Have fun and experiment, I'll cover other custom format features sometime in the near future.

One last thing, I've noticed one change with later versions of Excel. Depending on the language your system is, formatting will vary accordingly. I have Excel in Japanese so I still get the same results, but not in English which was the default in earlier versions of Excel. You should be able to get around this by selecting the Date format first, then Custom format and adjusting to suit. (To get 24 April, 2004 (Fri) in Excel 2003, Japanese version I now have to enter [$-409]d mmmm, yyyy (ddd);@ to get it in English format because the Japanese format is now default. Of course this is a big improvement for people to have automatic formatting in the same way that they would normally write it. Like I say, experiment and have fun. Excel can be very adaptable.  
Posted by andrewe at 23:09

April 23, 2004

Introduction

Hi there. I'm starting up this blog to give some tips on Excel (Microsoft Spreadsheet Program). My level now is intermediate but hopefully my tips might be useful to some other people who use Excel. This idea was suggested to me by my new friend, Masaru Kaji (aka Colo) who is a Microsoft MVP. This stands for Most Valued Professional and is awarded by Microsoft to people who have made major contributions to their field of speciality. You can see his site at this link - Colo's Junk Room Thanks Colo, hope to meet you soon :)  
Posted by andrewe at 18:14