Shantanu's Blog
Database Consultant
February 28, 2005
A quick calculation
To perform a quick calculation in the Formula Bar:
1. In the Formula Bar, type an equals (=) sign.
2. Type the numbers to multiply, for example, 9*5.
3. Press F9 to calculate and convert the formula to a value.
4. Press Enter to move down to the next cell. The value displayed in the cell is 45.
February 26, 2005
Text to column
There are a number of tasks those can be accomplished using "Text to column" option found under Data menu.
To separate first name and last names:
1. Select Column A or the range of cells containing the list of names.
2. From the Data menu, select Text to Columns.
3. In Step 1 of 3, select Delimited.
4. In Step 2 of 3, select the Space checkbox.
5. In Step 3 of 3, in the Destination box, select cell B1, and then click Finish.
To convert date values to text
1. Select Column A or the range of cells containing the list of dates.
2. From the Data menu, select Text to Columns.
3. In Step 1 of 3, select Delimited.
4. In Step 2 of 3, click on Next>
5. In Step 3 of 3, in the "Column data format", choose "Text". and then click Finish.
You can also use the same method to convert values from a text file to excel. If you have exported data from Oracle in text format, try the method mentioned above to import it in excel.
February 25, 2005
Netcraft: Power Outage Knocks Wikipedia Offline: "The free online encyclopedia Wikipedia has been knocked offline by a power outage in its data center. While the servers hosting the site were down only a short time, much of the site's content remained offline as Wikimedia staff worked on properly restoring data from MySQL databases."
Type a name to know if the name is popular. For e.g. If you type JOHNN you will get 2 options, johnny and johnnie click on any one of the name to how it has progressed over the decades. It's a fun tool but a module for business graphs to represent complex data.
http://babynamewizard.com/namevoyager/lnv0105.html
Hotmail outage peeves some e-mail users | CNET News.com: "The outage has caused intermittent access to Hotmail accounts for some consumers over the last couple of weeks and, more alarmingly, appears to have erased new and saved messages from in-boxes, according to several people who contacted CNET News.com about the problem."
February 20, 2005
To create a watermark using Word 2002 and later,
1. From the Format menu, select Background and then Printed Watermark…
2. In the Printed Watermark dialog, select the radio button for Text watermark.
3. In the Text field, either type your value or select from the predefined list.
4. Select any other options and click the OK button.
February 17, 2005
More excel tips:
So you know what will the formula =sum(a1:a5) in excel will return. There are a few other functions like min and max or avg which you must have tried. But did you know what does =RANK(A1,$A$1:$A$5) do? It will display the ranking of the number in cell A1 within the range A1 to A5
If you want to print the row and column headings, go to File - Page setup... - Sheet tab and check Row and column headings. It will print 1,2,3 rows and A, B, C columns. But if you want to print only the row numbers without column headings, type the following formula in the column A
=ROW()
It will print the current row number in the cell.
Google Toolbar 3 beta: What's new in the new version of google toolbar?
SpellCheck: Check your spelling whenever you type in web forms
WordTranslator: Translate English words into other languages
AutoLink (US only): Turn street addresses into links to online maps
Requires Internet Explorer 5.5+
February 14, 2005
Quickly Typing Dates into Cells
Typing a large amount of data into cells can be tiring, especially if it includes a series of dates. Type the day (serial number only) into cell A1 and add the following formula to insert the month and year into cell B1:
=DATE (YEAR(TODAY()), MONTH(TODAY()), A1)
Type a full number in the cell and change it to a date by using the following formula:
=DATEVALUE(LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,2))
For example, type 122203, the result is 12/22/03.
Limiting scroll area in Excel
To set the Scroll Area range, click the Properties icon from the Control Toolbox toolbar. In the Scroll Area text box, type the scroll area range. (To cancel the Scroll Area restricted range, clear the Scroll Area text box.) Note: You cannot add two Scroll Areas to the Scroll Area text box.
February 12, 2005
Amazon.com: Amazon Prime
* Free Two-Day Shipping on over a million in-stock items
* Overnight Shipping for only $3.99 per item—order as late as 6:30 PM
Newsburst : Help | CNET News.com: "Newsburst is a personalized RSS reader tool that tracks virtually any type of information on the Web: news, blogs, shopping lists, weather, search results, alerts, auctions and more."
Learn Linux - Shell Commands: "Using Linux - The Command Prompt
Linux Commands
When you enter a command in Linux, you type a command at a prompt and then press enter. Commands can be more than one word--some require switches (which modify the command's behavior ) and/or file names (which tell the command what data to act on)."
Images: Firefox gets Yahoo toolbar | CNET News.com
Yahoo said Wednesday, 9 Feb. 05 that it has released a test version of its toolbar for use with the Firefox browser.
February 11, 2005
These are two great functions in excel.
Combinations
The syntax is =COMBIN(Number, Number_chosen). The number is how many things you have. Eg 3 sandwich spreads. The number chosen is how many you want to combine. For example how many different combinations of 2 sandwich spreads can you make when you have 4 spreads available. The formula is =COMBIN(4,2) and the answer is 6 different combinations of two sandwich spreads.
A permutation is any set or subset of objects or events where internal order is significant. Permutations are different from combinations, for which the internal order is not significant.
So the number total number of permutations you can make from 4 objects using 2 objects.
from this formuls =PERMUT(4,2) and the answer is 24.
AutomateExcel.com - Excel VBA: Remove Blank Rows: "The obvious way to remove blank rows from a data set is to simply sort the data. This moves the blank rows to the bottom of the data and 'removes' them. But what if you want the blank rows removed, however you don't want the data sorted? VBA."
Count Used Cells With COUNTA() Function:
Count Used Cells With COUNTA() Function
You can use the COUNTA() function in Excel to count non blank cells. In other words, use COUNTA() to get the total used cells in a Range.
Here's an example formula using COUNTA(), the following returns the count of non blank cells in the Range A1:A10
=COUNTA(A1:A10)
Also note: Empty cells with a space character in them are considered non blank or used cells and create undesired results, possibly caused by the spacebar delete.
To count the blank cells in a range use the COUNTBLANK() function.
When you look at the income statement, you can see the numbers but it's hard to tell what area is favorable or unfavorable. You see that net revenue and NOI are positive this year vs. last year, but when you get to the line item detail it's harder to see the differences.
1. Write a formula:
To help your reader quickly see the differences, combine font and conditional formatting to create a symbol.
The formula cell F3 is:
=IF(D3 [ E3,"q","p")
2. Apply color:
To color the letters, apply conditional formatting. In this case we want letters associated with unfavorable variances to be colored red and letters associated with favorable variances to be colored green.
3. Apply arrows:
Finally, the Wingdings 3 font is applied to the cells with formulas in column F.
The colored arrows make it much easier to quickly see what areas are favorable or unfavorable. There are literally hundreds of formatting symbols that you can use via the different font families that Excel provides.
Documentation tip
How can I document my worksheet showing row and column heading?
To document an Excel workbook you would like a picture in a Word file showing an area of a worksheet with the column and row headings as in the figure.
To achieve this:
1) Use Page SetUp/ Sheet to specify print with row and column headings,
2) select the range required,
3) Hold down SHIFT and select the Edit command from the menu bar; this now contains Copy Picture,
4) In the Copy Picture dialog box, use the As Printed option,
5) you now have an image ready to paste into another application.
There are times when you might want to add a prompt to a workbook that triggers a question or action before the workbook is saved. When added to the ThisWorkbook code module, this macro will prompt the user with a "Did you fill in cell A2" question when the user tries to save the file. If he answers "Yes", then the macro will exit and the save event continues. If he answers "No", the save event is canceled.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Did you fill in cell A2?"
Style = vbYesNo
Title = "Save Data Prompt"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
Exit Sub
Else
Cancel = True
End If
End Sub
February 10, 2005
Wikipedia and you
My fellow WikiPedians of the world: ask not what the WikiPedia will do for you, but what together we can do for the knowledge of man.
February 09, 2005
To anyone who is working on maps, check this out:
http://maps.google.com
Zoom somewhere and *drag* the map around. Search for anything and find it on the map (spelling corrections is working as usual...)
Only the USA for now, and only IE and Mozilla/Firefox.
February 05, 2005
Though acronym is one of the basic HTML tag supported since IE 2.0, it does not has any inherent effect on text display other than show the text in title attribute as yellow tool tip. You can spice up the concept by adding a different cursor shape. In the head section of you html page or in the external stylesheet, add the following code.
abbr, acronym {
border-bottom: 1px solid #AAA;
cursor: help;
Now use the acronym tag anywhere on the page. for e.g.
Don't post any
PNG images on here.
Now the acronym will not only have the yellow tool tip, but also the help shaped curser instead of regular arrow.
February 04, 2005
PHP: Alternative syntax for control structures - Manual: Alternative syntax for control structures
PHP offers an alternative syntax for some of its control structures; namely, if, while, for, foreach, and switch. In each case, the basic form of the alternate syntax is to change the opening brace to a colon (:) and the closing brace to endif;, endwhile;, endfor;, endforeach;, or endswitch;, respectively.
?php if ($a == 5): ?
A is equal to 5
?php endif; ?
In the above example, the HTML block 'A is equal to 5' is nested within an if statement written in the alternative syntax. The HTML block would be displayed only if $a is equal to 5.
February 03, 2005
Sometimes a name can go against you and I think this may be the case with RSS. One common translation of the abbreviation is "really simple syndication". As one friend asked me, "If it's so simple, how come I don't get it and I'm not using it?" The question has merit as the simplicity may reside more with the publisher. Fortunately, MY Yahoo! offers a simple way of adding RSS to your pages. Check out the 4 minute flash tutorial.
http://www.timeatlas.com/tutorials/MYYahooRSS.htm
PowerPoint - Kiosk Setup
Setting up an information booth or kiosk is very simple. Any PowerPoint slide show can be made to run automatically and to loop continuously if desired.
1. Set timings for all slides, this can be done from the Slide Sorter View by selecting Slide Transition from the Slide Show menu.
Alternatively, click the Rehearse Timings icon from the Slide Sorter View to run the slide show, click with the mouse to advance through the slides at the desired pace. At the end of the show PowerPoint will give a summary of the time taken for the entire show and register these timings for each slide.
To get rid of these Rehearsed Timings, Select "Set Up Show" from Slide Show Menu and check "Manually" inside the Advance Slides box.
2. Select Set Up Show from the Slide Show menu.
3. Select Browsed At A Kiosk, in the Set Up Show window under Show Type and under Advance Slides select Using Timings, If Present.
Note that it may be a good idea to also unplug the keyboard from the computer, as the [Esc] key will stop the show.
http://www.masterviews.com/
Wouldn't it be nice if for every site we visit, we would automatically see a display of how many pages the domain at large contains in Google? This could be made into a nice Firefox extension. E.g. you visit "www.example.com/subpage/1.html", then Google should be queried for "site:www.example.com", and the resulting page count should be displayed in the Firefox status bar at the bottom.
http://blog.outer-court.com
A shortcut to search for all messages having a specific label is to type the Label prefixed with the "label:" query word in any simple search field at the top of any Gmail page:
label:work
If you want to view all messages that have selected multiple Labels, for example messages having both 'work' and 'client', enter the following into the simple search field at the top of any Gmail page:
label:work label:client
To see all messages with either 'work' or 'home', you can enter:
label:work OR label:client
Note: the specific label names are NOT case sensitive, but the "OR" operator is case sensitive.
This type of searching goes way beyond this by letting you search using more complex criteria. For example, building on our example above, say you want to search for messages containing attachments from your family sent before May 21, 2004? You would simply enter the following advanced search criteria:
label:family has:attachment before:2004/5/21
Yes, this could actually be done in the Search Options pane, but in addition to the available search criteria fields, query words not only let you search using criteria not included in the Search Options pane, (like "cc:" and "bcc:") but you can do "compound" searches otherwise not available in the Search Options pane. For example:
label:doctors label:statements has:attachment before:2004/5/21 in:anywhere
Here are some of the keywords other than lable: mentioned above.
from: to: subject: cc: bcc: label: has:attachment filename: after: before:
You can also use the "in" keyword.
in:anywhere in:inbox in:trash in:spam in:starred in:unread in:read
And the comparison is done using OR, - (minus sign to exclude a word), "" (Double inverted comma) and () (Brackets)
http://g04.com/misc/GmailTipsComplete.html
February 02, 2005
According to blog search-engine and measurement firm Technorati, 23,000 new weblogs are created every day—or about one every three seconds. Each blog adds to an inescapable trend fueled by the Internet: the democratization of power and opinion. Blogs are just the latest tool that makes it harder for corporations and other institutions to control and dictate their message. An amateur media is springing up, and the smart are adapting. Says Richard Edelman, CEO of Edelman Public Relations: "Now you've got to pitch the bloggers too. You can't just pitch to conventional media."
Of course, it's difficult to take the phenomenon seriously when most blogs involve kids talking about their dates, people posting pictures of their cats, or lefties raging about the right (and vice versa). But whatever the topic, the discussion of business isn't usually too far behind: from bad experiences with a product to good customer service somewhere else. Suddenly everyone's a publisher and everyone's a critic.
says Marissa Mayer, who oversees the search site and all of Google's consumer web products. 'Our legal department loves the blogs, because it basically is a written-down, backed-up, permanent time-stamped version of the scientist's notebook. When you want to file a patent, you can now show in blogs where this idea happened.'
fortune.com
encyclopedia, not an experiment
Wikipedia is not primarily an experiment in Internet democracy. It's a project to write an encyclopedia. Wikipedia *is* a grand social experiment of course. But not _primarily_.
Studying for the MySQL Certification exam? This article, the first of two parts, covers roughly 10 percent of the material that will appear on the exam, including general database and table properties, storage engines and table types, and more. It is excerpted from chapter four of the book MySQL Certification Guide written by Paul Dubois
www.devshed.com
A pop-up note contains information and can be made to appear when a visitor to your website moves the mouse over the appropriate area. Alejandro Gervasio explains how to create several different kinds of pop-up notes using CSS and JavaScript.
http://www.devarticles.com/
February 01, 2005
Removing Icons Performing Two Different Tasks
Some of the icons on the Standard and Formatting toolbars perform two different (but related) tasks. To toggle between the two tasks, press Shift while clicking the icon. The icons that perform different tasks are:
In the Standard toolbar:
*Print Print Preview
*Sort Ascending Sort Descending
*Open Save
*In the Formatting toolbar:
*Increase Indent Decrease Indent
*Underline Double Underline
*Center Merge and Center
*Increase Decimal Decrease Decimal
Example: The Open icon changed to Save when pressing the Shift key.
To remove the double-task icons:
Drag the icon off the toolbar while holding the Alt key.
Archives
June 2001
July 2001
January 2003
May 2003
September 2003
October 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
July 2008
August 2008
September 2008
October 2008
November 2008
December 2008
January 2009
February 2009
March 2009
April 2009
May 2009
June 2009
July 2009
August 2009
September 2009
October 2009
November 2009
December 2009
January 2010
February 2010
March 2010
April 2010
May 2010
June 2010
July 2010
August 2010
September 2010
October 2010
November 2010
December 2010
January 2011
February 2011
March 2011
April 2011
May 2011
June 2011
July 2011
August 2011
September 2011
October 2011
November 2011
December 2011
January 2012
February 2012
March 2012
April 2012
May 2012
June 2012
July 2012
August 2012
October 2012
November 2012
December 2012
January 2013
February 2013
March 2013
April 2013
May 2013
June 2013
July 2013
September 2013
October 2013
January 2014
March 2014
April 2014
May 2014
July 2014
August 2014
September 2014
October 2014
November 2014
December 2014
January 2015
February 2015
March 2015
April 2015
May 2015
June 2015
July 2015
August 2015
September 2015
January 2016
February 2016
March 2016
April 2016
May 2016
June 2016
July 2016
August 2016
September 2016
October 2016
November 2016
December 2016
January 2017
February 2017
April 2017
May 2017
June 2017
July 2017
August 2017
September 2017
October 2017
November 2017
December 2017
February 2018
March 2018
April 2018
May 2018
June 2018
July 2018
August 2018
September 2018
October 2018
November 2018
December 2018
January 2019
February 2019
March 2019
April 2019
May 2019
July 2019
August 2019
September 2019
October 2019
November 2019
December 2019
January 2020
February 2020
March 2020
April 2020
May 2020
July 2020
August 2020
September 2020
October 2020
December 2020
January 2021
April 2021
May 2021
July 2021
September 2021
March 2022
October 2022
November 2022
March 2023
April 2023
July 2023
September 2023
October 2023
November 2023
April 2024
May 2024
June 2024
August 2024
September 2024
October 2024