Solve formula with another way than solver/goal seek

Just quickly looking through the formula, I don't see a ready way to algebraically rearrange that function to solve for J4. Excel does have the built in NORMINV which can find the inverse of the probability density function, but your function is using cumulative distribution function, each instance of the NORMDIST function uses slightly different arguments, so I'm not sure it would be very useful.

It looks to me like, one way or another, you will be limited to numerical methods to solve this function. The easiest way in Excel that I know of to access these numerical methods is through the Goal Seek/Solver utilities, so you might already have found the easiest solution. What is your desired purpose here? To automate the process of calling Solver/Goal Seek so you don't have to do it manually? A Macro/VBA procedure could be written (maybe even tied to the calculate event) to automate the process of calling Solver/Goal Seek.

If you have the programming expertise, you could even write a UDF using the Newton Raphson (or other method if you prefer) method to solve this equation in VBA. A UDF might be an attractive option because it can be entered into the spreadsheet like any other function, and it will "fit" into the calculation dependency tree and only calculate when needed.

Hi,

Thanks a lot for your answer.
My goal is to automate the process of calling Solver/Goal Seek so I don't have to do it manually.
I never heard of the UDF method, but it sounds really good if it is possible to insert into the spreadsheet as a function.

Can you might help me with UDF?

I expect I and the others here can help you. Your chance of success is going to depend on your familiarity with VBA and the Newton Raphson method. I don't know your experience level, so I'm not sure where to tell you to start. Have you coded UDF's in VBA for Excel before? Have you written code in any language using the Newton Raphson method?

One note: A cost - benefit analysis might be useful at this point. How many times do you have to solve this equation? Coding this UDF is going to require a significant investment in time and effort, especially if you are an inexperienced programmer and/or unfamiliar with the Newton-Raphson method. If you only need to solve this equation 20 or 30 times, it might be a better use of time/effort to simply do it manually, even if it is tedious. I sometimes find myself spending more effort trying to avoid doing something "the hard way" than I would have spent just doing it "the hard way."

Hi,

Thanks a lot for your answer.

I want to solve this equation a lot of times.
Unfortunately I have not so much programming experience. And UDF and Newton Rapson I am also not familiar with.

Thank you for your help and your explanation.

For someone who is unfamiliar with coding UDF's, I suppose the first place to start is a few basics.

1) Enter the VB editor. In my older version, alt-f11 will do this. If that shortcut doesn't work in the newer version, ask excel help how to bring up the visual basic editor
2) insert a new module into your workbook/VBA project.
3) create your function procedure in this module. Here is a simple function procedure that returns 5 times the argument passed to the function:

Please Login or Register  to view this content.

4) In the spreadsheet, enter =mytest(A1) into B1, and you should get a result.

Just about any numerical algorithm will require you to calculate f(x) at some given x. Once you can use a basic UDF like this, work on writing a UDF that will calculate the formula in column K in your spreadsheet.

While you are at that, you will need to become familiar with the Newton Raphson method. If you already know enough calculus, Wikipedia has a pretty good article (with more information that is probably necessary) describing the NR method. Once you have code to calculate f and you understand how the NR method works, then you can proceed to write the code to solve the equation.