Formula/VBA code needed - something like Solver/Goal seek

[Borndippy]

I have a spreadsheet with 3 columns. One has forecast value, one has actual value and the third column shows the difference between the two.

I want to show the individual rows that have the largest differences so as to account for at least 90% of the total.

I added a 4th column that caluclates the difference as a percentage of the total difference and applies the following if statement

If the difference is less than a value input into another cell (say A10) then return 0%

I have a filter that excludes all the 0% results (this runs via a macro, as soon as I view the sheet it applies the correct filter).

I have a total adding up the results as a value in cell C70
I then manually tweak the value in cell A10 until I get an answer of 90% or higher in cell C70.

I can't get goal seek to work with this (although I've never managed to get goal seek to work so that's not very telling!). I need to repeat this exercise on 20 individual sheets in very tight time constraints (less than 10 minutes). Is there a better way of doing it than manually changing cell A10 until I get the answer I want?

Apologies for the long post, any assistance welcome. I am definitely an Excel user, not an expert.

 

[jc.021286]

I've done similar work with stock options.

Your goal is to get the sum of C1:C69 which is represented in Cell C70 = 90% by only changing the value in cell A10?

Using Goal Seek, (make sure it is installed) in excel 2007-2010 go to the Data Ribbon
Data > What-If-Analysis > Goal Seek...

Set Cell: C70
To Value: 0.9
By Changing: A10

Run and it is done in 2-10 seconds depending on the iterations required.

If you install the VBA component.

You can use

Public Sub Goal_Seek_My_Value()
    Range("C70").GoalSeek Goal:=0.9, ChangingCell:=Range("A10")
End sub

Cycle through the pages and you ought to be done.

If your cell c70 is calculated based on other macros, then you will have to pretend to do a goal seek through raw coding and loops + itterrations.

Regards,
jc