Formula , Function and Macros
Formula : Formula is a relationship among variables. It helps an easy and quick accomplishment of numeric calculations for spreadsheet analysis. When a formula is entered in a cell, it represents a specified relationship with other specific cells in the worksheet.
Creation of formulas :
a) Creating Formulas Manually :To create a formula manually, you have to do the following:
1. Select the cell in which you want to enter the formula.
2. Type the equal to (=) sign first.
3. Now array the required cells as per their relationship.
4. Press enter key.
The resulting value would be shown in the specified cell :
b) Creating Formulas by Pointing :
In this method you don't need to type cell references for creating formula. All you have to do is pointing at the specific cell
1. Select the cell in which you want the formula.
2. Type the equal to (=) sign to start the formula.
3. Click on the cells with the reference that is required in the formula.
4. Optionally enter an arithmetic operator, such as , +, -, *, /, ^, 0r %, or a comparison operator such as , <, >, <=, >=, <>, = or a text operator like the ampersand (&).
5. Click on the next cell that you want to include in the formula.
6. Click on the check box in the formula entry.


Formula Bar

Entering Date and Time Formula
You can create formulas to calculate values by using date and time. For excel to recognize date and time that you enter in the formula you need to specify it in the correct format. You need to enclose the entry in double quotation marks. Excel would then give the required result, to find out difference of two dates, the format is := "26/4/98". Excel would return the value 244 days. An example to calculate hours difference is =Hour ("4:30:00-3:30:00"). This would display result 1.

Converting Formulas to Values
Most of the time we create a formula. You would only want to view the result and not the formula. Which is normally displayed on the formula bar. In such a situation, you can convert the formula to its actual value. To convert a single formula to a value, the steps are:
1. Select the cell that contains the formula.
2. Double click on the cell or press the F2 function key.
3. Press the F9 function key.
Excel will show the result not the formula.

Converting Range of Formulas to values
To convert a range of formulas to values, the steps are:
1. Select the range that contains the formulas you want to convert.
2. Select the copy option from the edit menu, or click on the right mouse button and select the copy option from the shortcut menu.
3. Select the paste special option from the edit menu. A dialog box will show.
4. Select the values option from the dialog box.
5. Click on OK button.

Inserting Range Names in Formulas
You can insert a range name into the formula, the steps are:
1. Enter the values in the required range of cells.
2. Give a name to the range of cells by using the define option of the name submenu in the insert menu.
3. Type the (=) equal to symbol in a cell and create a formula that can use the range name.
4. Select the name option from the inser menu.
5. Select the paste option from the name submenu.
6. In the paste name drop-down list, select the name you want to insert.
7. Click on the OK button.
8. Enter the rest of the formula and press enter.
9. Example:
To sum the marks of 25 students Enter the marks of the 25 students in 25 contiguous cells .give a name of the range of cells namely marks. Enter the formula

Entering Functions
Excel functions help in performing simple to complex arithmetic calculations. There are 200 built-in function or predefined formulas that enable you to create formulas for a wide range of application including business, scientific and engineering applications.
We can entered function by following ways.
# Typical Functions manually
To enter a function in the active cell, type the equal (=) sign, followed by an option parentheses. You can then specify the cell range you want the function to use and complete the function with closed parentheses; for example, =SUM (A2:A8).
#Using AutoSum
You can use the autosum button for quick calculations that involve addition of numbers. This button is located on the standard toolbar. To use this feature of Excel, follow these steps:
1. Select a cell below the range you want to sum.
2. Click on the AutoSum button .
Excel will automatically inserts the SUM function and selects the cells in the column above the selected cell.
3. You can also highlight the cell range that you want to sum and then click on the AutoSum button.
Excel will automatically calculates the total and displays it.

Macros
Macros automate frequently performed task by changing task into a key strokes that are assigned a control key. Whenever the specified control key is used. The entire operation is performed automatically. You can have macros for formatting worksheet, report generation and so on. This feature is time saving, flexible and very powerful.
Creating and Storing Macros
You can create a macro in a number of ways. To create a macro, you should have your program in the macro record mode. This can be done by the following the steps:
1. Select the macro option from the tools menu.
2. Select the record new macro option from the macro submenu.

Macro record is a tool that translates your actions in Visual Basic Application macro. It works like a tape recorder-when you tern it on. It records everything you do. Later you can run the macro and all the previously performed action are repeated.
You can name your macro in the record macro dialog box. The name that you specify should not contain spaces or other punctuation marks, but it can contain underscores.


Record macro dialog box.

You can assign a shortcut key for the macro that you want to record.
You can store macro in text box.The option available are:
# Personal Macro Workbook : which has the name PERSONAL.xls. This workbook opens and is hidden automatically everytime you start excel. # This workbook-which stores the macro in the active workbook.
# The new workbook-which stores/records the macro in a new workbook that is created.
** You can click on the OK button and the macro recording process begins.
** You can then perform the actions that you want to record.
Once you have completed all the necessary actions for storing/recording macros you can click on the stop button.
You can also select the stop recording option from macro submenu within the tools menu option.

Running Macro You can run macro in different ways. You can select the run option from the dialog box or use the shortcut key that you have assigned to your macro. Given below is an example to create a macro.

You can record a macro that can create the given worksheet, by following the steps:
1. Select the macro option from the tools menu.
2. Select the record new macro option from the macro submenu.
The record macro dialog box get invoked.
3 Give a name to the macro that you want to record and assign m as a shortcut key.
4 Click on OK button.
5 In the excel worksheet, type the labels products, rate , qty, and value.
6. Type in the rest of the data shown in the sample worksheet.
7. Click on the stop button to stop the recording process.
8. Clear the data that you had copied on your worksheet.
9. Run the macro by pressing Ctrl+m.