Tuesday, September 30, 2008
To remove empty rows between data
Find how many times a particular character repeats in a cell.
Friday, September 26, 2008
Find and replace ?
Find and replace is very common thing we can expect from any application. However, certain special character can't be replaced like question mark '?'. But this can be very well tricked by using Replace function.
1. When you want use find & replace to remove ? from the string you will notice that all the next old text will be replaced by new text.
Wednesday, September 24, 2008
Import data from website to MS Excel
Many times we don't want to go to website again and again to check if content is updated. Alternative is to import such data to MS Excel. This post will help you in understanding how to import data to MS Excel. Please carefully read the instruction below.
To import and refresh information from a Web site:
1. Open MS Excel XP/ 2003, and click on the Data menu, click Import External Data, and then New Web Query.
2. In the Address box of the New Web Query dialog box, type or paste the address of the Internet site which you want to see on your excel sheet
For example, open the site www.bloomberg.com, which includes a table of various currency exchange rates. The address of the page containing the table of currency exchange rates is http://www.bloomberg.com/markets/index.html.
3. In the dialog box, notice the Web page. Click the small arrow in the upper left-hand corner of the table to select only the data table.
4. Click Import.
5. To save the query, click Properties in the Import Data dialog box.
6. In the External Data Range Properties dialog box, enter the query name in the Name box, select the Save Query definition checkbox, and then click OK.
7. In the Import Data dialog box, click OK to import.
To refresh the Internet data
2. From the Data menu, select Refresh Data.
OR
Display the External Data toolbar and click the Refresh Data icon. To display the External Data toolbar, select one of the toolbars, right-click and select External Data, and click OK.
To automatically refresh the Internet data:
1. On the External Data toolbar, click the Data Range Properties icon.
2. Select the Refresh every option, and set the number of minutes between each refresh action.
3. Select the Refresh data on file open checkbox to automatically refresh the data when the file is opened.
Do let me know your comments and experience about using information provided in this post via comments. Also, subscribe via email to receive latest updates, add-ins and ebooks on MS Excel.
D
Read more on this article...Monday, September 22, 2008
Check which CASE is used using Exact function
The formula is not very useful in practical MS Excel application. However, can be use to improve your knowledge about EXACT function.
Exact function basically compares the text from two cells for all aspects like content and case both. For Example, If cell A1 contains S and B1 contains s then the formula Exact (A1, B1) will return false.
Upper functions converts any case into Upper case.
Lower function converts any case into Lower case.
Proper function converts any case into Proper case (First letter of each word capital).
Formula:
=IF(EXACT(C9,UPPER(C9)),"UPPER CASE",IF(EXACT(C9,LOWER(C9)),"LOWER",(IF(EXACT(C9,PROPER(C9)),"PROPER","INCORRECT CASE"))))
In this trick, IF Exact(C9, Upper(Exact(C9)) returns true only if C9 is in upper case and same goes other cases.
This formula may not be very useful in practical application. However, very useful in understanding Exact function.
Thank you for spending your valuable time in reading above post and if you like to read more such tricks subscribe to blog via email.
We assure you knowledge, not SPAM!
Read more on this article...How to Remove Apostrophe
Suppose if column A contains an imported list of numbers. All these number will have an invisible apostrophe due to which MS Excel interprets such numbers as text. These numbers can’t be formatted as currency or they cannot be using in calculation as SUM and AVERAGE. From office XP onward you can recognize them by a triangular green color mark at the left side top of the cell.
We will have to convert them into values/numbers, so we can use them in formulas or format them as currency.
Please have a look at the steps below
Office XP or Above:
1. Select the enter column from which you wish to remove apostrophe. On the highlighted cell, you will see an exclamation mark .
2. Click on the exclamation mark and select Convert to Number from menu.
Office 2000:
You will have to use the VALUE function as shown in the following
Formula:
=VALUE(A1)
Subscribe here to receive more such tips. Your comments motivate us to post more.
We assure you knowledge, not SPAM!
Read more on this article...Saturday, September 20, 2008
Calculate Six Sigma in Excel
Sigma is calculated using error and number of opportunities. At times, people need different tool for calculating Sigma. At times, people even search in Google. I wish if you reach here and understand Sigma calculation once an and all in MS Excel.
- The Six Sigma mainly consist defects/error and Total number of opportunities.
- Please check the formula in the image below. For reference use, it's mentioned below.
Formula:
=IF(A2=0,6,IF(A2=B2,0,IF(B2=0, "DENOMINATOR CANNOT BE ZERO",ROUND((NORMSINV(1-A2/B2)+1.5),2))))
If you like this post, spend two minutes in leaving comments. To receive update over email do subscribe to us.
We assure you knowledge, not SPAM!
Read more on this article...Separate Text based on SPACE
Also, do let me know your comments about this post and subscribe to us to receive latest updates via email
We assure you knowledge, not SPAM!
Read more on this article...Friday, September 19, 2008
User Define function to Convert Number to Text
This function is something I wrote myself on request of one of my community member from my MS Excel community on Orkut. Through this post I am also attempting to teach blog readers how to move code to Visual Basic Editor for use.
The Function will convert 122 into One Hundred and Twenty Two
1. Press ALT + F11 to start Vb Editor
2. Insert Module
3. Copy paste the entire code into module
4. Check the function in the list of User Define Function.
e.g = N2T(a2)
Where a2 cell contain numeric value
'The below program is written to convert the number into text
'The logic in solely developed by Sarfaraz Ahmed and is not referred from any internet on internet or any book
'Hence, user in this forum can freely use the function.
'User are requested not to remove the comments
Dim fplace(0 To 20) As String
Dim splace(0 To 10) As String
Dim PlaceName(1 To 5) As String
Private Sub LoadValue()
fplace(0) = ""
fplace(1) = "One"
fplace(2) = "Two"
fplace(3) = "Three"
fplace(4) = "Four"
fplace(5) = "Five"
fplace(6) = "Six"
fplace(7) = "Seven"
fplace(8) = "Eight"
fplace(9) = "Nine"
fplace(10) = "Ten"
fplace(11) = "Eleven"
fplace(12) = "Twelve"
fplace(13) = "Thirteen"
fplace(14) = "Fourteen"
fplace(15) = "Fifteen"
fplace(16) = "Sixteen"
fplace(17) = "Seventeen"
fplace(18) = "Eighteen"
fplace(19) = "Nineteen"
fplace(20) = "Twenty"
splace(0) = ""
splace(1) = "Ten"
splace(2) = "Twenty"
splace(3) = "Thirty"
splace(4) = "Forty"
splace(5) = "Fifty"
splace(6) = "Sixty"
splace(7) = "Seventy"
splace(8) = "Eight"
splace(9) = "Ninety"
splace(10) = "Hundredth"
PlaceName(1) = "Hundred"
PlaceName(2) = "Thousand"
PlaceName(3) = "Lakh"
PlaceName(4) = "Crore"
End Sub
Function N2T(DbVal As Double) As String
LoadValue
Dim LFTPART As String
Dim RGTPART As String
Dim RGTLEN As Integer
Dim LFTLEN As Integer
If Len(Trim(DbVal)) < 3 Then
N2T = D2T(DbVal)
ElseIf Len(Trim(DbVal)) = 3 Then
N2T = D2T(Left(DbVal, 1)) & " " & CheckZero(Left(DbVal, 1), "Hundred") & " " & CheckZero(Right(DbVal, 2), "And") & " " & D2T(Right(DbVal, 2))
ElseIf Len(Trim(DbVal)) = 4 Then
N2T = D2T(Left(DbVal, 1)) & " " & CheckZero(Left(DbVal, 1), "Thousand") & " " & D2T(Mid(DbVal, 2, 1)) & " " & CheckZero(Mid(DbVal, 2, 1), "Hundred") & " " & CheckZero(Right(DbVal, 2), "And") & " " & D2T(Right(DbVal, 2))
ElseIf Len(Trim(DbVal)) = 4 Then
N2T = D2T(Left(DbVal, 1)) & " " & CheckZero(Left(DbVal, 1), "Thousand") & " " & D2T(Mid(DbVal, 2, 1)) & " " & CheckZero(Mid(DbVal, 2, 1), "Hundred") & " " & CheckZero(Right(DbVal, 2), "And") & " " & D2T(Right(DbVal, 2))
ElseIf Len(Trim(DbVal)) = 5 Then
N2T = D2T(Left(DbVal, 2)) & " " & CheckZero(Left(DbVal, 1), "Thousand") & " " & D2T(Mid(DbVal, 3, 1)) & " " & CheckZero(Mid(DbVal, 3, 1), "Hundred") & " " & CheckZero(Right(DbVal, 2), "And") & " " & D2T(Right(DbVal, 2))
ElseIf Len(Trim(DbVal)) = 6 Then
N2T = D2T(Left(DbVal, 1)) & " " & CheckZero(Left(DbVal, 1), "Lakh") & " " & D2T(Mid(DbVal, 2, 2)) & " " & CheckZero(Mid(DbVal, 2, 2), "Thousand") & " " & D2T(Mid(DbVal, 4, 1)) & " " & CheckZero(Mid(DbVal, 4, 1), "Hundred") & " " & CheckZero(Right(DbVal, 2), "And") & " " & D2T(Right(DbVal, 2))
ElseIf Len(Trim(DbVal)) = 7 Then
N2T = D2T(Left(DbVal, 2)) & " " & CheckZero(Left(DbVal, 2), "Lakh") & " " & D2T(Mid(DbVal, 3, 2)) & " " & CheckZero(Mid(DbVal, 3, 2), "Thousand") & " " & D2T(Mid(DbVal, 5, 1)) & " " & CheckZero(Mid(DbVal, 5, 1), "Hundred") & " " & CheckZero(Right(DbVal, 2), "And") & " " & D2T(Right(DbVal, 2))
ElseIf Len(Trim(DbVal)) = 8 Then
N2T = D2T(Left(DbVal, 1)) & " " & CheckZero(Left(DbVal, 1), "Crore") & " " & D2T(Mid(DbVal, 2, 2)) & " " & CheckZero(Mid(DbVal, 2, 2), "Lakh") & " " & D2T(Mid(DbVal, 4, 2)) & " " & CheckZero(Mid(DbVal, 4, 2), "Thousand") & " " & D2T(Mid(DbVal, 6, 1)) & " " & CheckZero(Mid(DbVal, 6, 1), "Hundred") & " " & CheckZero(Right(DbVal, 2), "And") & " " & D2T(Right(DbVal, 2))
ElseIf Len(Trim(DbVal)) = 9 Then
N2T = D2T(Left(DbVal, 2)) & " " & CheckZero(Left(DbVal, 2), "Crore") & " " & D2T(Mid(DbVal, 3, 2)) & " " & CheckZero(Mid(DbVal, 3, 2), "Lakh") & " " & D2T(Mid(DbVal, 5, 2)) & " " & CheckZero(Mid(DbVal, 5, 2), "Thousand") & " " & D2T(Mid(DbVal, 7, 1)) & " " & CheckZero(Mid(DbVal, 7, 1), "Hundred") & " " & CheckZero(Right(DbVal, 2), "And") & " " & D2T(Right(DbVal, 2))
Else
N2T = "Value is too big, function support upto 9 digits only"
End If
End Function
Private Function D2T(intVal As Double) As String
If intVal > 20 Then
D2T = splace(Left(intVal, 1)) & " " & fplace(Right(intVal, 1))
Else
D2T = fplace(intVal)
End If
End Function
Private Function CheckZero(intVal As Double, PlaceName As String) As String
If intVal = 0 Then
CheckZero = ""
Else
CheckZero = PlaceName
End If
End Function
Read more on this article...Thursday, September 18, 2008
Classification Using Lookup function
Many times I came across MS Excel sheet where people use IF Then to classify grades. IF then is really big and confusing in this kind of scenarios. I would suggest you to use LOOKUP function to
1 The scenario is to classify values into Fail, Second Class, First Class, Distinction and Merit.
2. Refer to formula in the image.
3. This is an easy alternative to IF formula.
Formula : =LOOKUP(A2,{0,35,45,60,75,90,100},{"Fail","Pass Class","Second Class","First Class","Distinction","Merit"})
Also, do let me know your comments about this post and subscribe to us to receive latest updates via email
We assure you knowledge, not SPAM!
Read more on this article...Monday, September 15, 2008
Filter by Color
Also, do let me know your comments about this post and subscribe to us to receive latest updates via email
We assure you knowledge, not SPAM!
Read more on this article...Using IF and AND to Classify
This is a very common scenario that I have came across. This is first thing I did when I opened MS Excel in 2000. In fact this query is raised by one girl in my MS Excel community on Orkut.
The IF formula below classify based on the value A1.
Above 70 - ,"CONGRATS KEEP IT UP",
70 to 60 - "Good Work"
and so on
Formula:
=IF(A1>70,"CONGRATS KEEP IT UP",IF(AND(A1>60,A1<=70),"GOOD WORK",IF(AND(A1>45, A1<=60),"MUST WORK HARD","BAD")))
Sunday, September 14, 2008
Check if Value is Present in the give RANGE
Creating a formula that will return TRUE if each number in column E appears at least once in the range A2:C6.
If any of the specified numbers are missing, the formula will return FALSE.
Solution: Use AND and COUNTIF functions in the following Array formula:{=AND(COUNTIF(A2:C6,E2:E10))}
Read more on this article...Calculate difference in Time in MS Excel
To calculate an employee's working hours. Especially when shift start one days and ends on the other day. The calculation becomes tricky as time end time becomes less than start time and time can't be negative.
Formula: =D1-C1+IF(C1>D1,1)
Take a look at image below to understand the formula.
Click on image to view Enlarge
To view formula on MS Excel sheet download the Timesheet example through link below.
Also, do let me know your comments about this post and subscribe to us to receive latest updates via email
We assure you knowledge, not SPAM!
Read more on this article...Friday, September 12, 2008
VBA, Simple Macro Example
The purpose of creating this page is to help you understand how to declare a integer variable in VBA, assign value to it and compare it using IF THEN and show the result using using message box. However, in near future we will move step by step to more complex conditions.
1) Dim xValue As Integer -> Creates the integer variable which can hold numeric values and in case if you are not sure about the user input, please use variant type.
2) xValue = Sheet1.Cells(3, 4) -> This part of code is written under 'Private Sub CommandButton1_Click()' hence will only execute when someone clicks on the command button 1 which is placed on sheet1.
3) If xValue >= 18 Then MsgBox "You are major", vbInformation + vbOKOnly, "IF Then Macros"Else MsgBox "You are Minor", vbInformation + vbOKOnly, "IF Then Macros"End If
The above code check if the xValue is greater or equal to 18 and display the necessary output using the msgbox.
The link below has MS Excel workbook with above macro for download.
MS Excel Simple macro download
Note: To view the code, press ALF +F11.
Also, do let me know your comments about this post and subscribe to us to receive latest updates via email
We assure you knowledge, not SPAM!
Read more on this article...Wednesday, September 10, 2008
Formula to find duplicate records
This is my first post of blog where I have just posted one image. Later while moving images from one place to another I update this with more details. The post is useful if you are using MS Excel 2003 or below as MS Excel 2007 has option to remove duplicates.
Here we will use countif to determine all unique as 1 and duplicate as 2 or more. Mentioned below is formula to be placed in first row. For details take a look at screenshot/image below.
Formula: =COUNTIF($A$2:A2,A2)
Click on image to view enlarge
Also, you can download file with find duplicate example.
Please do let me know your views and comments about this post and subscribe to us to receive latest updates via email
We assure you knowledge, not SPAM!
Read more on this article...Friday, September 5, 2008
Convert HH:MM:SS to Seconds without using Formula
Step 1: If A1 cell in sheet1 is containing value 00:12:04.Select A1 -> Press Ctrl + 1 -> Select Number tab-> Select Number from Category list-> Click on OK.
Step 2: Now type 86400 in any cell in Excel sheet. Copy that 86400 (86400 is number of seconds in a day) and use paste special (click value and multiply) over A1. It will return 724.Note: The above paste special can be done for multiple cell at one time. Hence,its an ideal way to save time.
USING formulaType: HOUR(A1)*3600+MINUTE(A1)*60+SECOND(A1)in A2 cell. It will return 724 in A2. Please try the first method with any AVAYA report in HH:MM:SS form. I am sure it will definitely save your lot of time. Read more on this article...