Download
1 / 6
60 likes | 82 Views
Institute of professional accountant offers a certificate course in advanced excel where you can learn advanced Excel techniques like what-if analysis, pivot table, VLookup, HLookup, and more. Visit the website for more information
E N D
Introduction to What-If Analysis • Say you are taking a class in school, and I'm not doing very well so far. Luckily, if you get a high enough grade on the final exam, there's a chance you may be able to pass the class. The question is: What grade do you need to get on the exam to raise your average to at least a 70? • We can find out using a feature called What-If Analysis, which allows you to fill in missing information based on the data you already have. Right now, all know are the grades you got on the first four assignments: 58, 70, 72, and 60. Before we do our analysis, we need to figure out my current average by adding formula or function in this cell. In this case, each assignment is weighted equally, so we can calculate the number we need using the AVERAGE function.
What if analysis example • To get the most out of What-If Analysis, you do need to be fairly comfortable with formulas and functions-luckily, this one is pretty simple. • We'll just type an equal's sign, and then AVERAGE. Next, we need to select all the cells-even the empty cell for the final exam grade. If we don't, it won't calculate the average for all five assignments. Looks like we're done, so I'm just going to press Enter... and now we can see my current average. Unfortunately, it's not high enough, but we knew that was going to happen. Now we can move onto the next step: using a feature called goal to seek to figure out what we need on the final exam.
Goal seek • Goal Seek is a type of What-If Analysis that basically, lets you work backward in order to find a missing value. It starts with the result you want, then it calculates whatever input is needed to get you there. To begin, select the cell that contains your formula or function; in our case, B7. Then go to the Data tab... and click the What-If Analysis command. There are actually several different types of What-If Analysis, including Scenario Manager and Data Table. But in this case, we're going to use Goal Seek. A dialog box will appear where you can set the parameters for the information you need.
Complete solution • In our example, we want to set cell B7 (that's our selected cell) to a value of 70... by changing the cell that contains our missing value. And that's going to be B6. When you're done, click OK... and Goal Seek will take a minute to see if it can find a solution. Based on the message here, it looks like we were successful. So, we're going to click OK and now we can see the result we were looking for in the final exam cell. So, I guess you need to make a 90 on the exam in order to pass the class. It's going to be tough, but I think you can do it. At least now you know thanks to the work we did with What-If Analysis. It might take some practice to get the hang of the feature on your own, but it's a great way to figure out unknown values in your worksheet.
certificate course in advanced excel • That's it for what-if analysis hope you will understand the complete topic. If you are looking foracertificate course in advanced excel then contact the institute of professional accountant and learn complete advanced excel. In case of any query call us at +91-9213855555.
