Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Aug 2016
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multicells goal seek

    Hi.. I am trying to figure out how to do multiple cells goal seek.. I have few hundreds products that I need to adjust prices based on certain value result from it.. Attached sample excel file.. I am using Mac Excel 2016.. I know there is a plugin for MS windows excel but no option for Mac excel that I am aware of.

    Say, if I want to adjust profit (column F) to $5.00, I would like the goal seek to determine and change the sale price (Column B) that would give me $5 profit.. Column C & E is fixed value for that row.. Column D is 15% of Sales Price (column B).

    I have never written VB and I have seen others using VB to do this task but I have yet figured out how to do that.

    Any assistance would be appreciated.

    Thanks,
    Richard
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Frog,

    You can do this without goal seeking using a couple of extra columns and formulas. In your test spreadsheet, I added a column to enter the goal seek profit and a second to return the goal seek sale price.

    The Goal seek column has no formula but the Goal seek sales price has the formula on cell C2 =IF(H2="","",SUM(D2:F2,H2)) then copy down. The user simply enters the goal seek profit in column H and the adjusted Goal seek sales price appears in column C. Attached is the spreadsheet in Excel 2010 if you have a windows machine to open it. The sheet is locked with no password to protect the formulas

    HTH,
    Maud

    goalseek1.png
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Aug 2016
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks! However, that does not update the remaining cells after update. I notice you converted commission column to static, which give wrong data.. it is dependable on 15% of sale price.

    Quote Originally Posted by Maudibe View Post
    Frog,

    You can do this without goal seeking using a couple of extra columns and formulas. In your test spreadsheet, I added a column to enter the goal seek profit and a second to return the goal seek sale price.

    The Goal seek column has no formula but the Goal seek sales price has the formula on cell C2 =IF(H2="","",SUM(D2:F2,H2)) then copy down. The user simply enters the goal seek profit in column H and the adjusted Goal seek sales price appears in column C. Attached is the spreadsheet in Excel 2010 if you have a windows machine to open it. The sheet is locked with no password to protect the formulas

    HTH,
    Maud

    goalseek1.png

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Yes, you are correct. My error!

    In C2, enter the formula =IF(I2="","",(I2+D2+G2)/0.85) then copy down.

    In F2, enter the formula =IF(C2="","",C2*0.15) then copy down.

    Enter in your new goal seek profit (col I) and the goal seek price should be calculated (col C). There is a sum check in col M to verify the calculations is correct.

    goalseek3.png
    Attached Files Attached Files
    Last edited by Maudibe; 2016-08-14 at 17:08. Reason: Added columns to clarify calculations

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •