excel solver like Public Function

[xelhelp]

Hi,

I'm Trying to write a public function that is like the solver add-in in excel. The reason I cant use solver is because the solver add-in doesn't automatically recalculate as the values in the cells are changing. My spreadsheet is dynamic and the values are constantly changing.

I have three values. These are in cells A1, A2, and A3. I call these value1, value2 and value3 respectively in the code below. Changing the value in A3 changes A1 but not A2. I would like a function that will give me the value of A3 for which A1-A2=0

here is the code i have tried to write but it obviously isn't working. I'd appreciate any help on this. There is probably some simple loop type function that would do the trick. Please help.

Please Login or Register  to view this content.

[shg]

The loop in your function doesn't do anything -- nothing changes, so it will loop forever.

The reason I cant use solver is because the solver add-in doesn't automatically recalculate as the values in the cells are changing.

I don't understand that.

[MrShorty]

There are many different algorithms for finding roots of equations. How familiar are you with numerical methods? Which numerical method would you use? What kind of function is in A2?


Newton-Raphson method is pretty easy to program when there's only one variable changing, assuming the function can be readily differentiated.
A bisection type algorithm is another that I have used that isn't too difficult to program, but you have to be able to give the algorithm two starting points, one on each side of the root.

[xelhelp]

Hi Shg,

to use the solver function one has to manually click on the solver button in the excel ribbon and recalculate. Plus on any given spreadsheet you can only use solver for a single set of cells. That is why i would like to have a function that i can use throughout the spreadsheet.

[shg]

to use the solver function one has to manually click on the solver button in the excel ribbon and recalculate.

You can automate solver. There are many examples in this forum, and one from a few minutes ago at http://www.mrexcel.com/forum/showthread.php?t=638727. You can't automate solver in a UDF (a function called from the worksheet), but you could via a macro triggered by a change event.

Plus on any given spreadsheet you can only use solver for a single set of cells.

Solver remembers the last model used on each sheet.

EDIT: And you can store and reuse as many models on a sheet as you wish.

[xelhelp]

let say the range of values for value3 could be .01 - 1

[xelhelp]

how would this work with the bisection method? could you give an example code?

[MrShorty]

quick and untested pseudocode, for 2^-10 precision

Please Login or Register  to view this content.

[xelhelp]

how would this work for an unknown function F(value3) that produces value1?

[MrShorty]

Off the top of my head, I don't think it can. Any root finding algorithm that I can think of needs to be able to calculate F. If you code it right, you can make F as generic as you need to, but I think you are going to need to put something in the code so it can calculate F.

[xelhelp]

what do u mean when u say "make F as generic as you need to"...if the function is actually some complicated formula but when i code it i enter F(x)= 2*x .... it wouldn't work would it?

[MrShorty]

what do u mean when u say "make F as generic as you need to".

That means that you code F so that you can pass necessary parameter to it. For example, if you are working with polynomials (F(x)=A+Bx+Cx^2+Dx^3+...), you can pass the parameters A,B,C,D,... to the function rather than hard coding those values into the code.

if the function is actually some complicated formula but when i code it i enter F(x)= 2*x .... it wouldn't work would it?

I don't see why not. Of course you can't code it in algebraic syntax, you have to use VBA syntax. I got the impression from the 1st post that you had already figured out how to code the function into a spreadsheet cell. Call me optimistic, but I figure any function that you can code into a spreadsheet cell can be adapted into one or more lines of VBA code.

[xelhelp]

can the address, match and index functions from an excel spreadsheet be used in a VBA code? im trying to enter into vba code Application.Worksheet.Function.Address(2, 3) but it doesn't seem to work...is it because some excel functions cannot be used in VBA? How do i know which ones can and which ones cannot?

thanks

[MrShorty]

You can access worksheet function through the Worksheetfunction property (Application.worksheetfunction.match(arguments). I thought there was a list of worksheet functions available to VBA in the VBA help files. Try putting something like worksheet functions available to VBA in the VBA search engine and see if it comes up.

The match function is commonly called from VBA. The others, not so much, probably because the .cells(i,j) property/method usually works just as well without needing the worksheet function version.

[xelhelp]

I have a function that has 3 variables ( f(x,y,z) = 2X + y^2 + 1/z ) that i would like to compare to a set of values that i will call "observed values f(obs)"... i would like to find the values for x, y, and Z that will minimize the sum of squares....

please see the attachment for the equation

minimize.JPG

how do i do this optimization problem?
just to be clear...f(obs) are just values listed in Column A...like 1.5, 1.6, 1.8, 1.9, 2.5, 3.6, 5.4, 8, 9.1 ...etc

[shg]

You're missing something fundamental.

You have one set of observations, and want to derive three hidden variables, x, y, and z, given the function f = 2x + y^2 + 1/z.

So assume y=z=1; then x = (obs-2)/2

Or assume x=z=1; then y = sqrt(obs-3)

[xelhelp]

lets say we had only two observed values...2.5 and 3.5

the question is ....for what values of x,y,and z would we minimize.... [2.5 - (2X + y^2 + 1/z )]^2 + [(3.5 - (2X + y^2 + 1/z )]^2

please let me know if this makes sense

[shg]

The result must be f=3, right?

So pick any values you like for two of the variables (z<>0), and solve for the remaining one.

[xelhelp]

Attachment 159330

please have a look at the attached file with example...

i can do this with solver but i need to do this in realtime..which is why im trying to have vba code for this...

if u use solver and use the following settings....target cell C6, equal to "min", and by changing cells $E$3:$G$3....u get an answer

[shg]

"Invalid attachment"

[xelhelp]

as long as the initial value of z is not zero this works in solver....however i need to automate this so i don't have to do this manually using solver because the observed values will change in real time in my actual sheet

[xelhelp]

how do i attach a file to this thread?

[xelhelp]

exampleoptimize.xlsx

attached spreadsheet

[xelhelp]

http://www.excelforum.com/excel-prog...84#post2809584

[xelhelp]

i have posted the attachment on a new thread...the link is above , thats the only way it was allowing me to attach a spreadsheet

[xelhelp]

http://www.excelforum.com/excel-prog...unction-2.html

the attachment if for the thread at the above link...

[shg]

There is nothing to automate until you understand that two of the variables are redundant.

Change E3 to 0.5 set F3 and G3 to 1. Same result.


Change E3 and F3 to 0, and set z =1/3. Same result.

[abousetta]

why are you putting it in a new thread. You need to upload the example in the original thread.

[xelhelp]

How do u attach a file to an existing thread? I only see the attachment option when I start a new thread

[zbor]

I have moved your attachement now...
TO upload to existing file click Edit post -> Go Advanced and push paper clip button

[abousetta]

Thanks zbor