Goal Seeking and Data validation Ms Excel 2007
Goal Seeking and Data validation Ms Excel 2007: Goal seeking is a technique to find values for one or more cells that make the result of a formula equal to specified value. And the following figure shows result of PMT formula in B7 cell.
In this figure, the maximum monthly payment that can afford in 1200. The amount is written in B7 and the cell becomes the staring point. The bank is offering an interest rate of 8.25% over 15 years. The total mortgage is show in B3 it can be calculate from monthly payment, years paid and interest rate and the figure shows that the user can afford a mortgage of $123.693.
A student current has a grade of 82 in a class. He has asked the professor to calculate what they would need to receive on the final exam of the term in order to get an 85. So Using goal seek would be valuable so that Excel can automatically recalculated all formulas involved.
- Click Tools > Goal seek.
- Enter reference for the cell that contains the formula to resolve in Set cell box.
- Type the desired result in To Value box.
- Enter reference for the cell that contains the value to be adjusted in By changing cell box. This cell cannot contain a formula and only one cell must be referenced.
- Click OK OR click cancel to leave the sheet unchanged.
The process of comparing data to a set of rules or values to find out if the data is correct is called data validation. Data validation is a tool to control the kind of information that is enter in worksheet. You can apply rules about the range of values. For example the marks can be from 0 to 100 etc. it can be apply on the type of data that are allow for one cell or a range of cells. For example, the Roll No must be a numeric value etc. specifying data validation for individual cells allows you to restrict the type and value of information users enter into worksheet.
Data validation can:
- Make a list of the entries that restricts the values allowed in a cell.
- Create a message explaining the kind of data allowed in a cell.
- Create messages that appear if incorrect data is enter.
- And check for incorrect entries by suing Auditing toolbar.
- Set a range of numeric vales that can be enter in a cell.
- Determine if an entry is valid base on calculation in another cell.
Make a list of Entries Allowed in the cell:
You can make a list of entries for a cell in a worksheet. A restriction can be apply on the cell to accept entries only form the list by using the data validation feature. and he following procedure is use to create a list and restrict values in a cell to these entries.
- Select cell A1.
- Click Validation on Data menu.
- Click List in Allow drop down list.
- The ignore blank and In cell Drop down check boxes are selects by default. Do not change them.
- Type a, b, c, in source box. You can also enter a named range or cell reference if it contains a list of values. Both must be preceded by an equal sign.
- Click OK. Cell A1 now has a drop down list next to it and you can use this list to select the value to enter in the cell.
- Click the list and click any item it contains. This value will be enter in the cell.