June 20, 2004
Andrew's Excel Tips is moving
Today marks the beginning of my new site. You can find it here at Andrew's Excel Tips (http://www.andrewsexceltips.com)
Special thanks to my friend Colo who helped me set everything up.
Please update your links accordingly.
Today marks the beginning of my new site. You can find it here at Andrew's Excel Tips (http://www.andrewsexceltips.com)
Special thanks to my friend Colo who helped me set everything up.
Please update your links accordingly.
June 19, 2004
The VALUE function
Last time I showed how to change numbers to text. Although this is a very useful function, once a number becomes text, functions that add, count, multiply etc are no longer valid.
This is a case where the Value function comes in very handy.
In the TEXT function post below, the numbers have become text with the first four characters either "EUR " or "USD " in front (this includes the space in between the currency and the number)
In addition to converting the text to a value, we must remove these four characters which I will do with the LEFT and SUBSTITUTE functions.
=LEFT(D3,4) will give me the first four letters, "EUR " in cell D3. (Fortunately both "EUR " and "USD " have four characters we can use the same formulas in either case)
Then I use SUBSTITUTE to remove the characters like this.
=SUBSTITUTE(D3,LEFT(D3,4),"")
It works like this, =SUBSTITUTE(original text, text to be converted, replacement text), so if the D3 entry was Money makes the world go round, then =SUBSTITUTE(D3,"Money","Love") will give me Love makes the world go round. (Note: Text must be entered with apostrophe marks, but not cell references and certain other exceptions. It is also case sensitive although there are ways around this limitation) The quotation marks "" will leave a blank instead of either "EUR " or "USD ".
EUR 500:00 in cell D3 has now become 500:00 using the above formula, but it is still text and not a value that we can use numeric formulas with.
The final part of the formula calls for the VALUE function as in my example below
=VALUE(SUBSTITUTE(D3,LEFT(D3,4),""))
Here's a pic to make the above easier to follow.
Finished and none too soon, it's way past my bedtime...G'night!
Last time I showed how to change numbers to text. Although this is a very useful function, once a number becomes text, functions that add, count, multiply etc are no longer valid.
This is a case where the Value function comes in very handy.
In the TEXT function post below, the numbers have become text with the first four characters either "EUR " or "USD " in front (this includes the space in between the currency and the number)
In addition to converting the text to a value, we must remove these four characters which I will do with the LEFT and SUBSTITUTE functions.
=LEFT(D3,4) will give me the first four letters, "EUR " in cell D3. (Fortunately both "EUR " and "USD " have four characters we can use the same formulas in either case)
Then I use SUBSTITUTE to remove the characters like this.
=SUBSTITUTE(D3,LEFT(D3,4),"")
It works like this, =SUBSTITUTE(original text, text to be converted, replacement text), so if the D3 entry was Money makes the world go round, then =SUBSTITUTE(D3,"Money","Love") will give me Love makes the world go round. (Note: Text must be entered with apostrophe marks, but not cell references and certain other exceptions. It is also case sensitive although there are ways around this limitation) The quotation marks "" will leave a blank instead of either "EUR " or "USD ".
EUR 500:00 in cell D3 has now become 500:00 using the above formula, but it is still text and not a value that we can use numeric formulas with.
The final part of the formula calls for the VALUE function as in my example below
=VALUE(SUBSTITUTE(D3,LEFT(D3,4),""))
Here's a pic to make the above easier to follow.
Finished and none too soon, it's way past my bedtime...G'night!
June 16, 2004
The TEXT function
When is a number not a number? When it becomes text.
Let me explain, although you can do an similar thing with the Format Cells dialog box, formatting by use of the TEXT function enables to you choose formatting under certain conditions.
Suppose you have copied some monetary amounts from another Worksheet or Workbook. You can copy the values easily enough, but because you are using different currencies for certain customers, you may find yourself having problems in retaining the original format Although there are other ways to avoid this, using the TEXT function must be one of the more easier choices.
As an example, look at the picture below.
I have customers listed in column B:B and some amounts in column C:C. How did I change these amounts to Euro for Company ABC and US Dollars for Company XYZ in column D:D?
Let's look at the formats first. For Euro I have used [$EUR] #,##0.00, and for US Dollars I have used [$USD] #,##0.00. (Notice I have used square brackets and $ signs for EUR and USD for an Accounting format, whereas with the Format Cells dialog box, I could use either this format or just use inverted commas as in "EUR" #,##0.00 or "USD" #,##0.00. Either will do the job, although there may be some slight differences in spacing)
The TEXT formula is written as =TEXT(value, format_text), so for cell D3 I want to reference C3 which shows the amount or value, then I write the format I want the amount to change to, EUR or USD.
There are only 2 companies invloved in my example so a simple IF function is enough to decide between the EUR and USD formats, the company names in cell B3 being the deciding factor
=IF(B3="Company ABC",TEXT(C3,"[$EUR] #,##0.00"),TEXT(C3,"[$USD] #,##0.00"))
This formula will usually work, but if I have blank cells in column B, the above formula will automatically determine the format must be for US Dollars, so as a precaution I'll add an extra condition to be on the safe side.
=IF(B3="Company ABC",TEXT(C3,"[$EUR] #,##0.00"),IF(B3="Company XYZ",TEXT(C3,"[$USD] #,##0.00"),""))
The formula is now a nested IF formula which determines that if the customer is Company ABC to format in Euro, otherwise if the customer is Company XYZ to format in US Dollars, otherwise the two inverted comas shown as "" at he end of the formula will just leave a blank cell.
Next time I'll have a look at the "opposite" of the TEXT function...the VALUE function.
When is a number not a number? When it becomes text.
Let me explain, although you can do an similar thing with the Format Cells dialog box, formatting by use of the TEXT function enables to you choose formatting under certain conditions.
Suppose you have copied some monetary amounts from another Worksheet or Workbook. You can copy the values easily enough, but because you are using different currencies for certain customers, you may find yourself having problems in retaining the original format Although there are other ways to avoid this, using the TEXT function must be one of the more easier choices.
As an example, look at the picture below.
I have customers listed in column B:B and some amounts in column C:C. How did I change these amounts to Euro for Company ABC and US Dollars for Company XYZ in column D:D?
Let's look at the formats first. For Euro I have used [$EUR] #,##0.00, and for US Dollars I have used [$USD] #,##0.00. (Notice I have used square brackets and $ signs for EUR and USD for an Accounting format, whereas with the Format Cells dialog box, I could use either this format or just use inverted commas as in "EUR" #,##0.00 or "USD" #,##0.00. Either will do the job, although there may be some slight differences in spacing)
The TEXT formula is written as =TEXT(value, format_text), so for cell D3 I want to reference C3 which shows the amount or value, then I write the format I want the amount to change to, EUR or USD.
There are only 2 companies invloved in my example so a simple IF function is enough to decide between the EUR and USD formats, the company names in cell B3 being the deciding factor
=IF(B3="Company ABC",TEXT(C3,"[$EUR] #,##0.00"),TEXT(C3,"[$USD] #,##0.00"))
This formula will usually work, but if I have blank cells in column B, the above formula will automatically determine the format must be for US Dollars, so as a precaution I'll add an extra condition to be on the safe side.
=IF(B3="Company ABC",TEXT(C3,"[$EUR] #,##0.00"),IF(B3="Company XYZ",TEXT(C3,"[$USD] #,##0.00"),""))
The formula is now a nested IF formula which determines that if the customer is Company ABC to format in Euro, otherwise if the customer is Company XYZ to format in US Dollars, otherwise the two inverted comas shown as "" at he end of the formula will just leave a blank cell.
Next time I'll have a look at the "opposite" of the TEXT function...the VALUE function.
June 13, 2004
Mastermind
I present my very first Excel game (built mostly by me, but with some useful VBA provided by Colo and also Juan Pablo González)
Play and enjoy! If you have any feedback, please leave a comment or email me at the link provided below. Thanks.
Download it here!
I present my very first Excel game (built mostly by me, but with some useful VBA provided by Colo and also Juan Pablo González)
Play and enjoy! If you have any feedback, please leave a comment or email me at the link provided below. Thanks.
Download it here!
June 12, 2004
The DATE function, Part 2
It's a funny quirk of business. "Payment at 30 days" doesn't necessarily mean payment at 30 days, it really means exactly one month after. For example, if I add 30 days to today's date (06/12/04), the answer is 07/12/04 only because June has 30 days. Try adding 60 days and the result will be 08/11/04. What should really be calculated is today plus 2 months which is 08/12/04.
With this in mind, there is a very useful function called the EDATE function. It works like this, =EDATE(Date,Months) so if I want to add 2 months to today I can enter =EDATE(TODAY(),2). (Don't forget that =TODAY() gives today's date)
The problem is that the Analysis Toolpak must be installed for EDATE function to work. To check whether it is installed, select Tools, Add-Ins and the dialog box will appear like this.
And if you don't have the Analysis Toolpak? Here is a workaround that I call EDATEPLUS. It uses the DATE function to add or subtract the number of months you want, and if the days of the calculated date exceed the number of days that actually exist in that month, it will calculate to the end of the month instead, this being selected by the IF function. (For example, if today was 01/31/04, then one month later will be 02/29/04, not 03/01/04).
Hold on to your seats...
=IF(NOT(DAY(A1)=DAY(DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1)))),DATE(YEAR(A1),MONTH(A1)+B1+1,0),DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1)))
A1 is the original date and B1 is the number of months to add or subtract. Place the formula in any cell other than A1 or B1.
Basically it works like this. If the day of the calculated date (from the original date plus or minus the required months) does not equal the day of the original date, calculate to the end of the month instead. (Actually the end of month can be calculated by using the EOMONTH fuction also available with the Analysis Toolpak, or as =DATE(YEAR(A1),MONTH(A1)+B1+1,0) without the Analysis Toolpak)
Here's a Workbook example to have a look at. If you don't have the Analysis Toolpak installed, the EDATE and EOMONTH formulas will not work and you will get a #NAME? error.
There is also an EDATEPLUS Addin with installation instructions included on the right sidebar. Beware that it will only work on the computer(s) it is installed in. The workarounds above and in the Workbook example should work in any computer 100% of the time.
It's a funny quirk of business. "Payment at 30 days" doesn't necessarily mean payment at 30 days, it really means exactly one month after. For example, if I add 30 days to today's date (06/12/04), the answer is 07/12/04 only because June has 30 days. Try adding 60 days and the result will be 08/11/04. What should really be calculated is today plus 2 months which is 08/12/04.
With this in mind, there is a very useful function called the EDATE function. It works like this, =EDATE(Date,Months) so if I want to add 2 months to today I can enter =EDATE(TODAY(),2). (Don't forget that =TODAY() gives today's date)
The problem is that the Analysis Toolpak must be installed for EDATE function to work. To check whether it is installed, select Tools, Add-Ins and the dialog box will appear like this.
And if you don't have the Analysis Toolpak? Here is a workaround that I call EDATEPLUS. It uses the DATE function to add or subtract the number of months you want, and if the days of the calculated date exceed the number of days that actually exist in that month, it will calculate to the end of the month instead, this being selected by the IF function. (For example, if today was 01/31/04, then one month later will be 02/29/04, not 03/01/04).
Hold on to your seats...
=IF(NOT(DAY(A1)=DAY(DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1)))),DATE(YEAR(A1),MONTH(A1)+B1+1,0),DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1)))
A1 is the original date and B1 is the number of months to add or subtract. Place the formula in any cell other than A1 or B1.
Basically it works like this. If the day of the calculated date (from the original date plus or minus the required months) does not equal the day of the original date, calculate to the end of the month instead. (Actually the end of month can be calculated by using the EOMONTH fuction also available with the Analysis Toolpak, or as =DATE(YEAR(A1),MONTH(A1)+B1+1,0) without the Analysis Toolpak)
Here's a Workbook example to have a look at. If you don't have the Analysis Toolpak installed, the EDATE and EOMONTH formulas will not work and you will get a #NAME? error.
There is also an EDATEPLUS Addin with installation instructions included on the right sidebar. Beware that it will only work on the computer(s) it is installed in. The workarounds above and in the Workbook example should work in any computer 100% of the time.
June 10, 2004
The DATE function, Part 1
The DATE function is definitely one of my favorites. It works like this, =DATE(Year,Month,Day). So =DATE(2004,6,10) will give me June 10, 2004.
Here's a couple of useful tricks.
First of the Month
=TODAY will give today's date, so =DATE(YEAR(TODAY()),MONTH(TODAY()),1) will give you the first of the current month. Add or subtract to MONTH(TODAY()) for different months, as in =DATE(YEAR(TODAY()),MONTH(TODAY())+1,1) will give you the first day of next month while =DATE(YEAR(TODAY()),MONTH(TODAY())-1,1) will give you the first of last month.
End of the Month
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0) will give you the last day of the current month. Add or subtract months as above to go forward or backward in time as you like. Who said time travel was impossible?
A slightly more complex formula next time...
The DATE function is definitely one of my favorites. It works like this, =DATE(Year,Month,Day). So =DATE(2004,6,10) will give me June 10, 2004.
Here's a couple of useful tricks.
First of the Month
=TODAY will give today's date, so =DATE(YEAR(TODAY()),MONTH(TODAY()),1) will give you the first of the current month. Add or subtract to MONTH(TODAY()) for different months, as in =DATE(YEAR(TODAY()),MONTH(TODAY())+1,1) will give you the first day of next month while =DATE(YEAR(TODAY()),MONTH(TODAY())-1,1) will give you the first of last month.
End of the Month
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0) will give you the last day of the current month. Add or subtract months as above to go forward or backward in time as you like. Who said time travel was impossible?
A slightly more complex formula next time...
June 07, 2004
Hiding Stuff, Part 3
Hiding Sheets
To hide sheets, you can go to Format, Sheet and then select Hide or Unhide to hide or unhide any sheets you choose.
What's the advantage of hiding sheets? Well, if you are not using all of them, you might want to save some space where the Sheet Tabs are shown (too many tabs means not all will be visible and this will make harder to navigate around your Workbook)
You may also use a sheet for miscellaneous data such as pricing tables for LOOKUP or other reference formulas that are only referred to or updated recently. You might hide this sheet as a form of protection (you could protect the sheet and hide it to make it even safer. It might seem paranoid but just how important is your data?)
Or if you are really concerned about people snooping around you might consider making a sheet very hidden.
Making Sheets Very Hidden
By making your sheets very hidden, you are merely preventing it from being noticed from casual users. It won't appear under sheets to unhide using the Format, Sheets method. But I should say that anyone who knows about VBA (even low life sludge like myself) knows how to find and unhide Very Hidden sheets. That said, it should still be a reasonable measure of protection for around the office and such, but think very hard before you email a Workbook with sensitive information to people you don't want to see it in the first place!
Okay, assuming you still want to know, let's go to work. Open the Visual Basic Editor. There are a few ways to do this. One quick way is to use the shortcut keys Alt + F11. Another way is to right click any Sheet Tab and select View Code.
In the Project Explorer (top left) you will see a list of files. Let's say your Workbook is Book 1, navigate to the sheet you want. In this case I have chosen Sheet 3.
In the Properties Window (bottom left) select Visible. (It should appear in both the Alphabetic and Categorized tabs)
Click on the right and a drop down list should appear. Select Very Hidden and you're done.
So now you know. I just hope I haven't broken some little known taboo in the Excel community. If I don't post within the next few days, do me a favor and call Missing Persons!
Hiding Sheets
To hide sheets, you can go to Format, Sheet and then select Hide or Unhide to hide or unhide any sheets you choose.
What's the advantage of hiding sheets? Well, if you are not using all of them, you might want to save some space where the Sheet Tabs are shown (too many tabs means not all will be visible and this will make harder to navigate around your Workbook)
You may also use a sheet for miscellaneous data such as pricing tables for LOOKUP or other reference formulas that are only referred to or updated recently. You might hide this sheet as a form of protection (you could protect the sheet and hide it to make it even safer. It might seem paranoid but just how important is your data?)
Or if you are really concerned about people snooping around you might consider making a sheet very hidden.
Making Sheets Very Hidden
By making your sheets very hidden, you are merely preventing it from being noticed from casual users. It won't appear under sheets to unhide using the Format, Sheets method. But I should say that anyone who knows about VBA (even low life sludge like myself) knows how to find and unhide Very Hidden sheets. That said, it should still be a reasonable measure of protection for around the office and such, but think very hard before you email a Workbook with sensitive information to people you don't want to see it in the first place!
Okay, assuming you still want to know, let's go to work. Open the Visual Basic Editor. There are a few ways to do this. One quick way is to use the shortcut keys Alt + F11. Another way is to right click any Sheet Tab and select View Code.
In the Project Explorer (top left) you will see a list of files. Let's say your Workbook is Book 1, navigate to the sheet you want. In this case I have chosen Sheet 3.
In the Properties Window (bottom left) select Visible. (It should appear in both the Alphabetic and Categorized tabs)
Click on the right and a drop down list should appear. Select Very Hidden and you're done.
So now you know. I just hope I haven't broken some little known taboo in the Excel community. If I don't post within the next few days, do me a favor and call Missing Persons!
