{"id":2146,"date":"2021-03-29T19:46:04","date_gmt":"2021-03-29T17:46:04","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=2146"},"modified":"2024-02-06T16:05:34","modified_gmt":"2024-02-06T15:05:34","slug":"use-excel-goal-seek-to-find-the-formula-result-you-want","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/use-excel-goal-seek-to-find-the-formula-result-you-want\/","title":{"rendered":"Use Excel Goal Seek To Find The Formula Result You Want"},"content":{"rendered":"<p>If you have a <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\" title=\"formula\" data-wpil-keyword-link=\"linked\">formula<\/a> and want to show a specific result, but you do not know what input values to change within the formula, then <strong>Excel Goal Seek Formula<\/strong> is the one for you.<\/p>\n<p><strong>Excel Goal seek<\/strong> formula is an excellent way to minimize the efforts required in testing out a hit and trial method of finding answers. In a matter of seconds, it can <strong>find out the exact answer that would have taken a long time to find out manually<\/strong>.<\/p>\n<p>It can easily be <strong>used for back calculations<\/strong> as well.<\/p>\n<p><strong>For example<\/strong> &#8211; You can set your net profit\u2019s required amount to 0 in the Excel goal seek formula to find out the number of sales you have to generate to reach the breakeven point.<\/p>\n<p>Similarly, any required amount to reach a certain goal can be calculated easily using the goal seek excel online.<\/p>\n<p>&nbsp;<\/p>\n<p>The Goal Seel Formula comprises of <strong>three arguments<\/strong>, namely :<\/p>\n<ul>\n<li><strong>Set cell<\/strong> \u2013 This is the cell that has the amount which we need to find out.<\/li>\n<li><strong>To value<\/strong> \u2013 This is the value that you need the set cell amount to change to.<\/li>\n<li><strong>By changing cell<\/strong> \u2013 This is the cell that will be changed to test out multiple possibilities and arrive at the exact amount.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>Imagine you are <strong>calculating the payment terms on a loan<\/strong>.<\/p>\n<p>Your<strong> PMT formula<\/strong> gives you an amount of $1,450 but you can only afford to repay $1,000. You can <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/use-excel-goal-seek-to-meet-your-profit-goal\/\" target=\"_blank\" rel=\"noopener\">use Excel Goal Seek Formula<\/a> to find out what <em>Principal<\/em> you can borrow based on your $1,000 budget.<\/p>\n<p>&nbsp;<\/p>\n<p>Watch it on <a href=\"https:\/\/youtu.be\/_wjxkBxdG-A\" target=\"_blank\" rel=\"nofollow noopener\"><strong>YouTube<\/strong><\/a> and give it a thumbs-up!<br \/>\n<iframe src=\"https:\/\/www.youtube.com\/embed\/_wjxkBxdG-A?rel=0 \" width=\"800\" height=\"400\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><span data-mce-type=\"bookmark\" style=\"display: inline-block; width: 0px; overflow: hidden; line-height: 0;\" class=\"mce_SELRES_start\">\ufeff<\/span><\/iframe><\/p>\n<p><a href=\"https:\/\/www.youtube.com\/c\/MyExcelOnlinecomtutorials?sub_confirmation=1\" target=\"_blank\" rel=\"noopener noreferrer nofollow\"><img loading=\"lazy\" decoding=\"async\" class=\"alignmiddle wp-image-111468 size-full\" style=\"vertical-align: middle;\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/04\/1.yt_.png\" alt=\"\" title=\"\"><\/a><\/p>\n<h4>Follow the<strong>\u00a0step-by-step tutorial<\/strong> on How to Use Excel Goal Seek Formula and <strong>download this Excel workbook<\/strong>\u00a0to practice along:<\/h4>\n<p><span style=\"text-decoration: underline; color: #0000ff;\"><a class=\"IRPP_minimalist icon-download\" style=\"color: #0000ff; text-decoration: underline;\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/Goal-Seek.xlsx\" target=\"_blank\"><span class=\"ctaText px-3 text-capitalize\"><strong>download excel workbook<\/strong><\/span><span class=\"postTitle px-3\">Goal-Seek.xlsx<\/span><\/a><\/span><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1:<\/strong><\/span> Enter the 3 input variables that you will need to use for your PMT formula i.e.<strong> Interest Rate of 3.50%, Term of 240 months &amp; Principal of $250,000<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/loan-calculator1.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Use Excel Goal Seek To Find The Formula Result You Want\"  class=\"alignnone size-full wp-image-2159\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/loan-calculator1.png\" alt=\"Use Excel Goal Seek To Find The Formula Result You Want\" width=\"350\" height=\"177\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/loan-calculator1.png 350w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/loan-calculator1-300x152.png 300w\" sizes=\"(max-width: 350px) 100vw, 350px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2:<\/strong><\/span> Enter the PMT function in cell C8\u00a0<strong>=PMT(Interest Rate\/12, Term, Principal)<\/strong> which will give you a monthly payment amount of <span style=\"color: #ff0000;\">-$1,450<span style=\"color: #000000;\">.<\/span><\/span><\/p>\n<p>Here using the PMT function, we have successfully linked the changing cell (principal cell) with the set cell (payment cell).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Use Excel Goal Seek To Find The Formula Result You Want\"  class=\"alignnone size-full wp-image-19455\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/goal-seek-01.jpg\" alt=\"Use Excel Goal Seek To Find The Formula Result You Want\" width=\"421\" height=\"149\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/goal-seek-01.jpg 421w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/goal-seek-01-300x106.jpg 300w\" sizes=\"(max-width: 421px) 100vw, 421px\" \/><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 3:<\/strong><\/span> Select the cell C8 and go to <em><strong>Data &gt; What If Analysis &gt; Goal Seek<\/strong><\/em><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Use Excel Goal Seek To Find The Formula Result You Want\"  class=\"alignnone size-full wp-image-19456\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/goal-seek-02.jpg\" alt=\"Use Excel Goal Seek To Find The Formula Result You Want\" width=\"1267\" height=\"362\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/goal-seek-02.jpg 1267w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/goal-seek-02-300x86.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/goal-seek-02-1024x293.jpg 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/goal-seek-02-768x219.jpg 768w\" sizes=\"(max-width: 1267px) 100vw, 1267px\" \/><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 4:<\/strong><\/span> <strong>SET CELL:<\/strong> Enter the reference for the cell that contains the formula that you want to resolve. In our\u00a0example, this reference is cell C8<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/goal-seek-set-cell.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Use Excel Goal Seek To Find The Formula Result You Want\"  class=\"alignnone size-full wp-image-2153\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/goal-seek-set-cell.png\" alt=\"Use Excel Goal Seek To Find The Formula Result You Want\" width=\"212\" height=\"141\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 5:<\/strong> <\/span><strong>TO VALUE<\/strong>: Type the formula result that you want. \u00a0In our example, we want the payment to be -$1,000 (Note that this number is negative because it represents a payment)<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/goal-seek-to-value.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Use Excel Goal Seek To Find The Formula Result You Want\"  class=\"alignnone size-full wp-image-2154\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/goal-seek-to-value.png\" alt=\"Use Excel Goal Seek To Find The Formula Result You Want\" width=\"212\" height=\"141\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 6:<\/strong><\/span> <strong>BY CHANGING CELL:<\/strong> Enter the reference for the cell that contains the input value that you want to adjust i.e. One of our 3 variables (Interest Rate, Principal &amp; Term).<\/p>\n<p>In our example, this reference is cell C7 for the Principal.<\/p>\n<p>It is important that this changing cell is linked directly or indirectly to the set cell. Otherwise, there will not be any possible way for excel to perform calculations if there is no link between the changing and the set cells.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/goal-seek-by-cganging-value.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Use Excel Goal Seek To Find The Formula Result You Want\"  class=\"alignnone size-full wp-image-2155\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/goal-seek-by-cganging-value.png\" alt=\"Use Excel Goal Seek To Find The Formula Result You Want\" width=\"212\" height=\"141\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 7:<\/strong><\/span> Press OK and Excel Goal Seek Formula will run and produce a result. \u00a0<strong>Press OK to keep the results or Cancel to discard<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/goal-seek-status.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Use Excel Goal Seek To Find The Formula Result You Want\"  class=\"alignnone wp-image-2156 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/goal-seek-status.png\" alt=\"Use Excel Goal Seek To Find The Formula Result You Want\" width=\"259\" height=\"144\" \/><\/a><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/loan-calculator-end-result1.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Use Excel Goal Seek To Find The Formula Result You Want\"  class=\"alignnone size-full wp-image-2160\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/loan-calculator-end-result1.png\" alt=\"Use Excel Goal Seek To Find The Formula Result You Want\" width=\"356\" height=\"187\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/loan-calculator-end-result1.png 356w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/loan-calculator-end-result1-300x158.png 300w\" sizes=\"(max-width: 356px) 100vw, 356px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>Similarly, you can use this method to know how to use goal seek to find interest rate as well!<\/p>\n<p>&nbsp;<\/p>\n<p>It may happen that <strong>Excel is not able to find an exact value via the goal seek<\/strong> function. It will get you the closest value and inform you that the goal-seeking may not have a solution.<\/p>\n<p>In such cases, you can try the<strong> following methods<\/strong> to clarify this issue:<\/p>\n<p><span style=\"color: #ff0000;\"><strong>Method 1: <\/strong><\/span><strong>Double-check goal seek parameters<\/strong><\/p>\n<p>While using Excel goal seek formula, it is essential that the required cell and the changing cells are either directly or indirectly dependent on each other.<\/p>\n<p>There can be multiple layers of <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\" title=\"formulas\" data-wpil-keyword-link=\"linked\">formulas<\/a> that eventually link both the cells, but a link should be present for goal seek to work. Make sure that such a relationship exists between the two cells.<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>Method 2: <\/strong><\/span><strong>Circular references<\/strong><\/p>\n<p>It should be noted that the changing cell and the required cell are not interdependent, i.e., there are no circular references present.<\/p>\n<p>For Excel Goal Seek Formula to work properly, the involved formulas should not be co-dependent on each other.<\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000;\">Method 3:<\/span> Adjusting the iteration settings<\/strong><\/p>\n<p>There\u2019s a setting in Excel where you can adjust the number of possible solutions Excel will calculate, including changing its accuracy of the calculation.<\/p>\n<p>To change them:<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1:<\/strong><\/span> Click <strong>File<\/strong> from the tab list.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/Screenshot-328.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Use Excel Goal Seek To Find The Formula Result You Want\"  class=\"alignnone size-full wp-image-23166\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/Screenshot-328.png\" alt=\"Use Excel Goal Seek To Find The Formula Result You Want\" width=\"1101\" height=\"125\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/Screenshot-328.png 1101w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/Screenshot-328-300x34.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/Screenshot-328-1024x116.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/Screenshot-328-768x87.png 768w\" sizes=\"(max-width: 1101px) 100vw, 1101px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2:<\/strong><\/span> Select <strong>Options<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/Screenshot-329-1.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Use Excel Goal Seek To Find The Formula Result You Want\"  class=\"alignnone size-full wp-image-23168\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/Screenshot-329-1.png\" alt=\"Use Excel Goal Seek To Find The Formula Result You Want\" width=\"935\" height=\"723\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/Screenshot-329-1.png 935w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/Screenshot-329-1-300x232.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/Screenshot-329-1-768x594.png 768w\" sizes=\"(max-width: 935px) 100vw, 935px\" \/><\/a><\/p>\n<p><span style=\"color: #339966;\"><strong><span style=\"color: #ff0000;\">STEP 3:<\/span> <\/strong><\/span>Click on <strong>Formulas<\/strong> in the left vertical bar<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/Screenshot-326.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Use Excel Goal Seek To Find The Formula Result You Want\"  class=\"alignnone size-full wp-image-23169\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/Screenshot-326.png\" alt=\"Use Excel Goal Seek To Find The Formula Result You Want\" width=\"823\" height=\"677\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/Screenshot-326.png 823w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/Screenshot-326-300x247.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/12\/Screenshot-326-768x632.png 768w\" sizes=\"(max-width: 823px) 100vw, 823px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>Here, change these settings:<\/p>\n<ul>\n<li><strong>Maximum Iterations<\/strong> (it denotes the number of possible solutions; the higher the number the more iterations). Increase this number if you want Excel to test out more possible solutions to the goal-seeking that you require.<\/li>\n<li><strong>Maximum Change<\/strong> (it denotes the accuracy of the results; the lower the number the higher the accuracy). Decrease this number if your formula requires more accuracy. For example, if you are testing a formula with an input cell equal to 0 but Excel Goal Seek Formula stops at 0.001, setting Maximum Change to 0.0001 should fix the issue.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>Make sure to download our FREE PDF on the<strong><a href=\"https:\/\/app.monstercampaigns.com\/c\/czseo0viyrmspcx5ndve\/\" target=\"_blank\" rel=\"noopener noreferrer nofollow\">\u00a0333 Excel keyboard Shortcuts here<\/a>:<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>If you have a formula and want to show a specific result, but you do not know what input values to change within the formula, then Excel Goal Seek Formula is the one for you. Excel Goal seek formula is an excellent way to minimize the efforts required in testing out a hit and trial [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":17206,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"Use Excel Goal Seek To Find The Formula Result You Want","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[6,41],"tags":[88,114],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/2146"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=2146"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/2146\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17206"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=2146"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=2146"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=2146"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}