1. 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

2. 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

3. 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.

Originally Posted by Maudibe
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. 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

Posting Permissions

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