TechCafe: Excel

<div dir="ltr" style="text-align: left;" trbidi="on"><br /><b style="mso-bidi-font-weight: normal;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt;">Question:- How to use goal seek. Give an example</span></b><b style="mso-bidi-font-weight: normal;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">?</span></b><br /><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Answer:-</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 7.0pt; margin-left: 22.3pt; mso-list: l0 level1 lfo1; mso-margin-top-alt: auto; tab-stops: list 22.5pt; text-indent: -.25in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">1.<span style="font: 7.0pt "Times New Roman";">      </span></span></span><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Click on the <i>Data</i> tab.</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 7.0pt; margin-left: 22.3pt; mso-list: l0 level1 lfo1; mso-margin-top-alt: auto; tab-stops: list 22.5pt; text-indent: -.25in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">2.<span style="font: 7.0pt "Times New Roman";">      </span></span></span><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Choose <b>What-If Analysis</b> from the ribbon to open the drop down list.</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 7.0pt; margin-left: 22.3pt; mso-list: l0 level1 lfo1; mso-margin-top-alt: auto; tab-stops: list 22.5pt; text-indent: -.25in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">3.<span style="font: 7.0pt "Times New Roman";">      </span></span></span><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Click on <i>Goal Seek... </i>in the list to bring up the Goal Seek dialog box.</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 7.0pt; margin-left: 22.3pt; mso-list: l0 level1 lfo1; mso-margin-top-alt: auto; tab-stops: list 22.5pt; text-indent: -.25in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">4.<span style="font: 7.0pt "Times New Roman";">      </span></span></span><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Now fill the dialog box according to your need.</span></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><b><span style="font-family: "Times New Roman","serif"; font-size: 13.5pt; mso-fareast-font-family: "Times New Roman";">Example Of Goal Seek:</span></b><br /></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 12.0pt; mso-margin-top-alt: auto;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Enter the following data into the cells indicated: </span></div><table align="left" border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; margin-left: 6.75pt; margin-right: 6.75pt; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-table-anchor-horizontal: page; mso-table-anchor-vertical: paragraph; mso-table-left: 103.4pt; mso-table-lspace: 9.0pt; mso-table-rspace: 9.0pt; mso-table-top: 6.0pt; mso-yfti-tbllook: 1184;"> <tbody><tr style="height: 15.15pt; mso-yfti-firstrow: yes; mso-yfti-irow: 0;">  <td style="border: solid windowtext 1.0pt; height: 15.15pt; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 120.9pt;" valign="top" width="161"><div class="MsoNormal" style="line-height: normal; mso-element-anchor-horizontal: page; mso-element-anchor-vertical: paragraph; mso-element-frame-hspace: 9.0pt; mso-element-left: 103.45pt; mso-element-top: 6.0pt; mso-element-wrap: around; mso-element: frame; mso-height-rule: exactly; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Cell Name</span></div></td>  <td style="border-left: none; border: solid windowtext 1.0pt; height: 15.15pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 120.9pt;" valign="top" width="161"><div class="MsoNormal" style="line-height: normal; mso-element-anchor-horizontal: page; mso-element-anchor-vertical: paragraph; mso-element-frame-hspace: 9.0pt; mso-element-left: 103.45pt; mso-element-top: 6.0pt; mso-element-wrap: around; mso-element: frame; mso-height-rule: exactly; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Data</span></div></td> </tr><tr style="height: 15.15pt; mso-yfti-irow: 1;">  <td style="border-top: none; border: solid windowtext 1.0pt; height: 15.15pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 120.9pt;" valign="top" width="161"><div class="MsoNormal" style="line-height: normal; mso-element-anchor-horizontal: page; mso-element-anchor-vertical: paragraph; mso-element-frame-hspace: 9.0pt; mso-element-left: 103.45pt; mso-element-top: 6.0pt; mso-element-wrap: around; mso-element: frame; mso-height-rule: exactly; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">A1</span></div></td>  <td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 15.15pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 120.9pt;" valign="top" width="161"><div class="MsoNormal" style="line-height: normal; mso-element-anchor-horizontal: page; mso-element-anchor-vertical: paragraph; mso-element-frame-hspace: 9.0pt; mso-element-left: 103.45pt; mso-element-top: 6.0pt; mso-element-wrap: around; mso-element: frame; mso-height-rule: exactly; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">20</span></div></td> </tr><tr style="height: 14.25pt; mso-yfti-irow: 2;">  <td style="border-top: none; border: solid windowtext 1.0pt; height: 14.25pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 120.9pt;" valign="top" width="161"><div class="MsoNormal" style="line-height: normal; mso-element-anchor-horizontal: page; mso-element-anchor-vertical: paragraph; mso-element-frame-hspace: 9.0pt; mso-element-left: 103.45pt; mso-element-top: 6.0pt; mso-element-wrap: around; mso-element: frame; mso-height-rule: exactly; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">A2</span></div></td>  <td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 14.25pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 120.9pt;" valign="top" width="161"><div class="MsoNormal" style="line-height: normal; mso-element-anchor-horizontal: page; mso-element-anchor-vertical: paragraph; mso-element-frame-hspace: 9.0pt; mso-element-left: 103.45pt; mso-element-top: 6.0pt; mso-element-wrap: around; mso-element: frame; mso-height-rule: exactly; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">30</span></div></td> </tr><tr style="height: 16.0pt; mso-yfti-irow: 3; mso-yfti-lastrow: yes;">  <td style="border-top: none; border: solid windowtext 1.0pt; height: 16.0pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 120.9pt;" valign="top" width="161"><div class="MsoNormal" style="line-height: normal; mso-element-anchor-horizontal: page; mso-element-anchor-vertical: paragraph; mso-element-frame-hspace: 9.0pt; mso-element-left: 103.45pt; mso-element-top: 6.0pt; mso-element-wrap: around; mso-element: frame; mso-height-rule: exactly; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">A3(Formula)</span></div></td>  <td style="border-bottom: solid windowtext 1.0pt; border-left: none; border-right: solid windowtext 1.0pt; border-top: none; height: 16.0pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 120.9pt;" valign="top" width="161"><div class="MsoNormal" style="line-height: normal; mso-element-anchor-horizontal: page; mso-element-anchor-vertical: paragraph; mso-element-frame-hspace: 9.0pt; mso-element-left: 103.45pt; mso-element-top: 6.0pt; mso-element-wrap: around; mso-element: frame; mso-height-rule: exactly; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">=sum(A1,A2)</span></div></td> </tr></tbody></table><div class="MsoNormal" style="line-height: normal; margin-left: .5in; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><br /></div><div class="MsoNormal" style="line-height: normal; margin-left: .5in; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><br /></div><div class="MsoNormal" style="line-height: normal; margin-left: .5in; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><br /></div><div class="MsoNormal" style="line-height: normal; margin-left: .5in; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><br /></div><div class="MsoNormal" style="line-height: normal; margin-left: .5in; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><br /><br /><i style="mso-bidi-font-style: normal;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">The value 50 should appear in cell A3. Because of Addition Formula</span></i></div><div class="MsoNormal" style="line-height: normal; margin-left: .5in; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><br /></div><div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><b><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";">Altering the Addition using Goal Seek</span></b><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman";"> </span></div><div class="MsoListParagraphCxSpFirst" style="line-height: 130%; margin-bottom: 12.0pt; margin-left: 22.3pt; margin-right: 0in; margin-top: 12.0pt; mso-add-space: auto; mso-list: l1 level1 lfo2; tab-stops: list 22.5pt; text-indent: -.25in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 130%; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">1.<span style="font: 7.0pt "Times New Roman";">      </span></span></span><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 130%; mso-fareast-font-family: "Times New Roman";">Click on the <i>Data</i> tab.</span></div><div class="MsoListParagraphCxSpMiddle" style="line-height: 130%; margin-bottom: 12.0pt; margin-left: 22.3pt; margin-right: 0in; margin-top: 12.0pt; mso-add-space: auto; mso-list: l1 level1 lfo2; tab-stops: list 22.5pt; text-indent: -.25in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 130%; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">2.<span style="font: 7.0pt "Times New Roman";">      </span></span></span><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 130%; mso-fareast-font-family: "Times New Roman";">Choose <b>What-If Analysis</b> from the ribbon to open the drop down list.</span></div><div class="MsoListParagraphCxSpMiddle" style="line-height: 130%; margin-bottom: 12.0pt; margin-left: 22.3pt; margin-right: 0in; margin-top: 12.0pt; mso-add-space: auto; mso-list: l1 level1 lfo2; tab-stops: list 22.5pt; text-indent: -.25in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 130%; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">3.<span style="font: 7.0pt "Times New Roman";">      </span></span></span><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 130%; mso-fareast-font-family: "Times New Roman";">Click on <i>Goal Seek... </i>in the list to bring up the Goal Seek dialog box.</span></div><div class="MsoListParagraphCxSpMiddle" style="line-height: 130%; margin-bottom: 12.0pt; margin-left: 22.3pt; margin-right: 0in; margin-top: 12.0pt; mso-add-space: auto; mso-list: l1 level1 lfo2; tab-stops: list 22.5pt; text-indent: -.25in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 130%; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">4.<span style="font: 7.0pt "Times New Roman";">      </span></span></span><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 130%; mso-fareast-font-family: "Times New Roman";">In the dialog box, click on the <b>Set cell:</b> line.</span><br /><a name='more'></a></div><div class="MsoListParagraphCxSpMiddle" style="line-height: 130%; margin-bottom: 12.0pt; margin-left: 22.3pt; margin-right: 0in; margin-top: 12.0pt; mso-add-space: auto; mso-list: l1 level1 lfo2; tab-stops: list 22.5pt; text-indent: -.25in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 130%; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">5.<span style="font: 7.0pt "Times New Roman";">      </span></span></span><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 130%; mso-fareast-font-family: "Times New Roman";">Click on cell <b style="mso-bidi-font-weight: normal;">A3</b> in the spreadsheet since we are interested in altering the Addition.</span></div><div class="MsoListParagraphCxSpMiddle" style="line-height: 130%; margin-bottom: 12.0pt; margin-left: 22.3pt; margin-right: 0in; margin-top: 12.0pt; mso-add-space: auto; mso-list: l1 level1 lfo2; tab-stops: list 22.5pt; text-indent: -.25in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 130%; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">6.<span style="font: 7.0pt "Times New Roman";">      </span></span></span><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 130%; mso-fareast-font-family: "Times New Roman";">In the dialog box, click on the <b>T0 value:</b> line.</span></div><div class="MsoListParagraphCxSpMiddle" style="line-height: 130%; margin-bottom: 12.0pt; margin-left: 22.3pt; margin-right: 0in; margin-top: 12.0pt; mso-add-space: auto; mso-list: l1 level1 lfo2; tab-stops: list 22.5pt; text-indent: -.25in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 130%; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">7.<span style="font: 7.0pt "Times New Roman";">      </span></span></span><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 130%; mso-fareast-font-family: "Times New Roman";">Type <b style="mso-bidi-font-weight: normal;">100</b> since we want to increase the addition value.</span></div><div class="MsoListParagraphCxSpMiddle" style="line-height: 130%; margin-bottom: 12.0pt; margin-left: 22.3pt; margin-right: 0in; margin-top: 12.0pt; mso-add-space: auto; mso-list: l1 level1 lfo2; tab-stops: list 22.5pt; text-indent: -.25in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 130%; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">8.<span style="font: 7.0pt "Times New Roman";">      </span></span></span><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 130%; mso-fareast-font-family: "Times New Roman";">In the dialog box, click on the <b>By changing cell:</b> line.</span></div><div class="MsoListParagraphCxSpMiddle" style="line-height: 130%; margin-bottom: 12.0pt; margin-left: 22.3pt; margin-right: 0in; margin-top: 12.0pt; mso-add-space: auto; mso-list: l1 level1 lfo2; tab-stops: list 22.5pt; text-indent: -.25in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 130%; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">9.<span style="font: 7.0pt "Times New Roman";">      </span></span></span><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 130%; mso-fareast-font-family: "Times New Roman";">Click on cell A1 or A2 whichever value you want to change in the spreadsheet and click <b style="mso-bidi-font-weight: normal;">Ok</b></span></div><div class="MsoListParagraphCxSpMiddle" style="line-height: 130%; margin-bottom: 12.0pt; margin-left: 22.3pt; margin-right: 0in; margin-top: 12.0pt; mso-add-space: auto; mso-list: l1 level1 lfo2; tab-stops: list 22.5pt; text-indent: -.25in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 130%; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">10.<span style="font: 7.0pt "Times New Roman";">  </span></span></span><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 130%; mso-fareast-font-family: "Times New Roman";">At this point, Goal Seek should begin searching for a solution. If it finds one, the <i>Goal Seek</i> dialog box will inform you that a solution has been found.</span></div><div class="MsoListParagraphCxSpMiddle" style="line-height: 130%; margin-bottom: 12.0pt; margin-left: 22.3pt; margin-right: 0in; margin-top: 12.0pt; mso-add-space: auto; mso-list: l1 level1 lfo2; tab-stops: list 22.5pt; text-indent: -.25in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 130%; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">11.<span style="font: 7.0pt "Times New Roman";">  </span></span></span><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 130%; mso-fareast-font-family: "Times New Roman";">Now see the cell which you have inserted in <b>By changing cell (A1 or A2) </b><span style="mso-bidi-font-weight: bold;">the value will increased to 70 for result 100.</span></span></div><div class="MsoListParagraphCxSpMiddle" style="line-height: 130%; margin-bottom: 12.0pt; margin-left: 22.3pt; margin-right: 0in; margin-top: 12.0pt; mso-add-space: auto; mso-list: l1 level1 lfo2; tab-stops: list 22.5pt; text-indent: -.25in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 130%; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">12.<span style="font: 7.0pt "Times New Roman";">  </span></span></span><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 130%; mso-fareast-font-family: "Times New Roman";">To accept this solution, click OK in the <i>Goal Seek</i> dialog box and Goal Seek will alter the data in cell (A1 or A2 whichever you selected in</span> <span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 130%; mso-fareast-font-family: "Times New Roman";">By changing cell).</span></div><div class="MsoListParagraphCxSpLast" style="line-height: 130%; margin-bottom: 12.0pt; margin-left: 22.3pt; margin-right: 0in; margin-top: 12.0pt; mso-add-space: auto; mso-list: l1 level1 lfo2; tab-stops: list 22.5pt; text-indent: -.25in;"><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 130%; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">13.<span style="font: 7.0pt "Times New Roman";">  </span></span></span><span style="font-family: "Times New Roman","serif"; font-size: 12.0pt; line-height: 130%; mso-fareast-font-family: "Times New Roman";">To find a different solution, click <b>Cancel</b>in the <i>Goal Seek</i> dialog box. And do these steps again.</span></div></div>

Question:- How to use goal seek. Give an example ? Answer:- 1. Click on the Data tab. 2. Choose What-If Analysis from the ribb...

TechCafe

What they says

Wikipedia

Popular Posts

Trending Stories

Followers

About Me

Our Sponsors

Blog Archive

How to use goal seek With an example