On UrbanPro

View My Profile       Proud to be a Teacher       Write a Review  

Wednesday, April 20, 2016

TRIM Function in Excel

TRIM function Removes all the unnecessary leading and trailing spaces between words in a string (except single space between the words).

TRIM is useful when cleaning up text that has come from other applications or environments.


Syntax: =TRIM (text)

Parameter list:

    text - The text from which to remove extra space(s).

Example:


 

Tuesday, April 19, 2016

REPLACE Function in Excel

REPLACE Function is used when to replace text based on its location. or If you know the position of the text to be replaced, use the REPLACE function.

Syntex: =REPLACE (old_text, start_num, num_chars, new_text)

Parameter:

        old_text        - The text to replace.
        start_num     - The starting location in the text to search.
        num_chars    - The number of characters to replace.
        new_text      - The text to replace old_text with.


Example: 

 

Monday, April 18, 2016

Substitute Function in Excel

Use the SUBSTITUTE function when you want to replace text based on its content, not position and it is  also use to Replace All or Part of a Text String With Another Text String - Function Description and Examples are mentioned below:

Syntex: =SUBSTITUTE (text, old_text, new_text, [instance])

SUBSTITUTE function finds and replaces old_text with new_text in a text string. Instance limits SUBSTITUTE replacement to one particular instance of old_text.

SUBSTITUTE function is case-sensitive and does not support wildcards.

Parameter:

    text     - The text to change.
    old_text     - The text to replace.
    new_text     - The text to replace with.
    instance     - [optional] The instance of old_text to replace with new_text. Optional; if not supplied, all instances of old_text are replaced with new_text.


Examples are as follows:

1. Replacing "2010" to "2013" in below example:



2. Below example is self defined and has its all aspects i.e. indicate which occurrence you want to substitute, Case sensitive, what happens, if instance is not defined.




Use SUBSTITUTE to replace text based on content. Use REPLACE when to replace text based on its location.

Friday, April 15, 2016

Show a Processing Window While Macro Runs

Step-by-step process to show a processing window While Macro Runs in excel:
  1. Start a new workbook in Microsoft Excel 97, 2003, 2007, 2010, 2013, 2016. (or a new document in Microsoft Word 97, 2003, 2007, 2010, 2013, 2016.).
  2. Press ALT+F11 to activate the Visual Basic Editor.
  3. On the Insert menu, click Module to insert a module into the project.
  4. Press F4 to display the Properties window.
  5. Change the Name property of the module to Processing_Code.
  6. In the Processing_Code module window, type the following:
    
      Public Processing_Message As String
      Public Macro_to_Process As String
    
      Sub StartProcessing (msg As String, code As String)
    
         Processing_Message = msg    'Set the message that is displayed
                                         'in the dialog box
    
         Macro_to_Process = code     'Set the macro that is run after the
                                         'dialog box is active
    
         Processing_Dialog.Show      'Show the Dialog box
    
      End Sub
          
  7. On the Insert menu, click UserForm to add a UserForm to the project.
  8. Press F4 to display the Properties Window. Change the UserForm properties to the following settings.
           Property           Setting
           -------------------------------------
    
           Name               Processing_Dialog
           Caption            Please Wait...
           StartUpPosition    2-CenterScreen
     
  9. Add one Label control to the UserForm. Change the Name property of the label to lblMessage.
  10. Select the UserForm. On the View menu, click Code. In the code window, in the Procedure list, select the Initialize event, and then type the following in the Code window:
    
           Private Sub UserForm_Initialize()
    
              lblMessage.Caption = Processing_Message  'Change the Label
                                                       'Caption
    
           End Sub
          
  11. In the Code window, click Activate event in the Procedure list, and type the following:
    
           Private Sub UserForm_Activate()
    
              Me.Repaint                         'Refresh the UserForm
              Application.Run Macro_to_Process   'Run the macro
              Unload Me                          'Unload the UserForm
    
           End Sub
          
  12. On the Insert menu, click Module. Type the following code in the Code window:
    
       Sub MyMacro()
    
          For x = 1 to 5000
              Application.StatusBar = x   '5000 Iterations Changing
                                             'StatusBar
          Next
    
          Application.StatusBar = False  'Reset the StatusBar
    
       End Sub
    
       Sub Main()
    
          'Call the StartProcessing procedure to show the Processing_Dialog
          'with the label "Processing, Please Wait..." and execute
          'MyMacro.
    
          StartProcessing "Processing, Please Wait...", "MyMacro"
    
       End Sub
          
  13. Press ALT+Q to leave the Visual Basic Editor and return to Microsoft Excel (or Microsoft Word).
  14. On the Tools menu, point to Macro, and click Macros. Select the Main macro and click Run. The Processing_Dialog dialog box appears.
While the dialog box is shown, the status bar text in the application increments from 1 to 5000--the changing status bar text is the indication that your macro is running while the dialog box is on the screen.

How to Use the Processing Dialog Box in Other Workbooks

 

To use this "Processing, Please Wait" dialog box with macros in other workbooks or documents, do the following:

  1. Activate the Visual Basic Editor. On the View menu, click Project Explorer.
  2. Select the project that you created in the previous steps.
  3. Right-click the "Processing_Dialog" UserForm and click Export File. Save the UserForm as "Processing_Dialog.frm."
  4. Right-click the "Processing_Code" Module and click Export File. Save the module as "Processing_Code.bas."
  5. Return to Microsoft Excel and open the workbook in which you would like to use the "Processing, Please Wait" dialog box.
  6. Activate the Visual Basic Editor.
  7. On the File menu, click Import File, select the "Processing_Dialog.frm" file, and click Open.
  8. On the File menu, click Import File. Select the "Processing_Dialog.bas" file, and click Open.
  9. On the Insert menu, click Module, and type the following code in the Code window:
    
          Sub Main()
             StartProcessing "<message text>", "<macro name>"
          End Sub
          
    Where the <message text> argument is the text string that you want to display in the Processing_Dialog dialog box and <macro name> is the macro that you would like to run after the dialog box appears. To show the dialog box and run the macro indicated by "<macro name>", run the macro Main.

Thursday, April 14, 2016

Format Numbers in Indian Currency Format

How to format numbers and amounts in Indian currency format. Indian numbers are grouped differently than standard English numbers.

 
English Grouping              123,456,789.01

Indian Grouping                12,34,56,789.01

Quick and easy fix to show numbers in Indian format:

A custom number format of:

[>=10000000]##\,##\,##\,##0;[>=100000] ##\,##\,##0;##,##0
or

[>=10000000]"Rs. "##\,##\,##\,##0;[>=100000]"Rs. " ##\,##\,##0;"Rs. "##,##0
Will solve the problem.

To Insert  above format:

  • Press Ctrl+1 or Right click on the cell and click on "Format Cells..." Option, a dialog box will popped up as shown below:
Microsoft Excel Cell Formatting Dialog box - numbers tab for custom cell formatting

  • Select "custom" under category field.
  • copy and paste the above mentioned format under Type field
  • Click OK and you are done.

A permanent solution to Indian Number Formats:

However there is better and more permanent solution using the Systems Regional Settings

  1. Goto the Control panel and select the “Region and Language” button
  2. Select “Additional Settings”
  3. Set digit groupings to the Indian grouping like this:
    Indian Currency Formatting, Number Formatting - Settings in Control Panel
  4. Do the same on the Currency Tab and Apply
Now in Excel the Default  “,” and “$” Style will show the way you wanted.

Wednesday, April 13, 2016

Sum Value in a Date Range

Sum Values with-in a Date Range

While you’re waiting for the Office 365 Launch to start, you could calculate how many units of your top product have been sold.
If you’re using Excel 2007 and later, use the SUMIFS function , and for earlier versions, use the SUMIF function .
In this example, a Start date and an End date are entered on the worksheet. Dates are in column A, and units sold are in column B.



Use SUMIFS to Calculate Total for a Date Range

The SUMIFS function to calculate a total based on multiple criteria. We'll use a SUMIFS formula to total all the units where the sales date is:
  • on or after the Start date
    • AND
  • on or before the End date.
Here is the formula that is entered in cell D5:
=SUMIFS( $B$2:$B$9 , $A$2:$A$9 , ">=" & $D$2 , $A$2:$A$9 , "<=" & $E$2 )
  • Range $B$2:$B$9 has the numbers that we want to sum.
  • Range $A$2:$A$9 contains the sales dates .
  • The first criterion, ">=" & $D$2 , is the range with the value for criteria 1 (the Start date), and the operator to use with that value (greater than or equal to)
  • The second criterion, "<=" & $E$2 , is the range with the value for criteria 2 (the End date), and the operator to use with that value (less than or equal to) 

Verify the Total

In this example, the result for the selected date range is a total of 494 units sold. To verify, you can select cells B3:B6, and look at the total shown in Excel's Status Bar.



To get the total units for a different date range, change the Start date in cell D2, and/or the End date in cell E2.

Top 10 Free Web Hosting sites

Top 10 Free Web Hosting sites
  1. Biz.nf (PHP, MySQL, WordPress, Joomla, Free .co.nf domain, No ads)
  2. Free Hosting EU (Blog / Site builder, No ads, Free .eu.pn domain)
  3. Wix.com (Easy Website builder: html5, Flash, mobile, blogs, etc.)
  4. WebSiteBuilder (1000′s templates, Google/mobile friendly, .website)
  5. Biz.ly (Website & Blog builder, Photo album, Free .biz.ly domain)
  6. AwardSpace.net (PHP, MySQL, Email Sending, No Ads, Free subdomain)
  7. Jimdo.com (Visual website builder, add videos, photos, shopping cart)
  8. ByetHost.com (PHP, MySQL, PHPbb, SMF, Wiki, Free subdomain)
  9. Webs.com (Easy site builder, blog, forms, polls, Free subdomain)
  10. Weebly.com (Themes, Drag & Drop Creator, Easy Blog, eCommerce)

Tuesday, April 12, 2016

MS-Excel Support Services

Get Support Service in MS-Excel.

Services are as follows:
  • Database Management
  • Inventory Management
  • Basic Accounting Tool
  • Customized Automation Tools
  • Report Automation
  • Excel Trouble Shooting
  • Excel Training

 – Contact Vishesh Golya on 098289-88829 or visheshgolya@gmail.com
for more details call or whatsapp on 09828988829.calling time 11am to 11pm. whatsapp anytime.

Data Recovery Services

Monday, April 11, 2016

Data Recovery Services

Data Recovery Services:

We understand the importance of your data and need for a quick & secure recovery. Data Recovery tools are equipped to recover and repair inaccessible files, databases, photos and videos. We recover your Data with Safety & secure your information and preventing data leakage. 

Recovery Services are:
  • Hard Disk Data Recovery
  • Database Recovery
  • Laptop / Desktop Data Recovery
  • Pen drives / Memory Cards Data Recovery.

- Contact Vishesh Golya on 098289-88829 or visheshgolya@gmail.com
For more details call or whatsapp on 09828988829.calling time 11am to 11pm.
whatsapp anytime.

MS Excel Support Services
 

Saturday, April 9, 2016

What Are Flowcharts?

A flowchart is an easy to understand diagram of any process that uses simple graphics to represent a beginning, an end, and the different stages that follow a logical order. Each step in the process is represented by a unique symbol with a brief label of the process step. The flowchart symbols are linked together with arrows showing the direction of the process flow. As you can see from the diagram below, each symbol is standardized for the specific process.


Shape Name Description
Flowchart Line.svg Flow Line
An arrow coming from one symbol and ending at another symbol represents that control passes to the symbol the arrow points to. The line for the arrow can be solid or dashed. The meaning of the arrow with dashed line may differ from one flowchart to another and can be defined in the legend.
Flowchart Connector.svg On-Page Connector
Generally represented with a circle, showing where multiple control flows converge in a single exit flow. It will have more than one arrow coming into it, but only one going out. In simple cases, one may simply have an arrow point to another arrow instead. These are useful to represent an iterative process (what in Computer Science is called a loop). A loop may, for example, consist of a connector where control first enters, processing steps, a conditional with one arrow exiting the loop, and one going back to the connector. For additional clarity, wherever two lines accidentally cross in the drawing, one of them may be drawn with a small semicircle over the other, showing that no connection is intended.
Flowchart Annotation.svg Annotation
Annotations represent comments or remarks about the flowchart. Like comments found in high-level programming languages, they have no effect on the interpretation or behavior of the flowchart. Sometimes, the shape consists of a box with dashed (or dotted) lines.
Flowchart Terminal.svg Terminal
Represented as circles, ovals, stadiums or rounded (fillet) rectangles. They usually contain the word "Start" or "End", or another phrase signaling the start or end of a process, such as "submit inquiry" or "receive product".
Flowchart Decision.svg Decision
Represented as a diamond (rhombus) showing where a decision is necessary, commonly a Yes/No question or True/False test. The conditional symbol is peculiar in that it has two arrows coming out of it, usually from the bottom point and right point, one corresponding to Yes or True, and one corresponding to No or False. (The arrows should always be labeled.) More than two arrows can be used, but this is normally a clear indicator that a complex decision is being taken, in which case it may need to be broken-down further or replaced with the "predefined process" symbol. Decision can also help in the filtering of data.
Flowchart IO.svg Input/Output
Represented as a parallelogram. Involves receiving data and displaying processed data. Can only move from input to output and not vice versa. Examples: Get X from the user; display X.
Flowchart Predefined Process.svg Predefined Process
Represented as rectangles with double-struck vertical edges; these are used to show complex processing steps which may be detailed in a separate flowchart. Example: PROCESS-FILES. One subroutine may have multiple distinct entry points or exit flows (see coroutine). If so, these are shown as labeled 'wells' in the rectangle, and control arrows connect to these 'wells'.
Flowchart Process.svg Process
Represented as rectangles. This shape is used to show that something is performed. Examples: "Add 1 to X", "replace identified part", "save changes", etc....
Flowchart Preparation.svg Preparation
Represented as a hexagon. May also be called initialization. Shows operations which have no effect other than preparing a value for a subsequent conditional or decision step. Alternatively, this shape is used to replace the Decision Shape in the case of conditional looping.
Off page connector.png Off-Page Connector
Represented as a home plate-shaped pentagon. Similar to the on-page connector except allows for placing a connector that connects to another page.


Popular Posts