VBA to Solver

[panyagak]

hi

I need to solve this formula using
Solver and vba.

Pi = Ni^k (power k)

Applied to this example:
1.51 3.69 5.30 (in columns D2, E2,
F2 respectively)

Where
Ni represents (1/1.51), (1/3.69),(1/
5.30), individually .

There are 2 competitors, n.

N = Sum of the 3 Ni(s)

k is derived as:
Log n / Log(n/N).

k is the SUBJECT
OF ITERATION to ENSURE
SUMMATION OF THE DERIVED 3
Pi=1.00.

Maximum iterations=100.

Am new to using Solver and
integrating vba to it to a large Range
of cells, say D2:F850

help out

regards

[Alf]

You better do as the yellow banner says i.e. upload a file if you wish to get help.

Lookin at your problem you have defined Pi, Ni, N and k but not n. You are using the "Log" function and I assume you mean the function based on 10 as excel has a LOG function where one specify the number and the base. Then there is the "LOG10" function where excel assumes your base is 10.

Running Solver with vba you will need to set a reference to Solver in Visual Basic (VB -> Tools -> References and mark box named Solver)

For the loop you could use something like

Please Login or Register  to view this content.

where "”$??$$ & i," is your target cell that you need to specify a column, as well as change the MaxMin to what you wish your target value to be Min or Max or a value of. And then you probably has some constraints that should be added.

Alf

[Alf]

You better do as the yellow banner says i.e. upload a file if you wish to get help.

Lookin at your problem you have defined Pi, Ni, N and k but not n. You are using the "Log" function and I assume you mean the function based on 10 as excel has a LOG function where one specify the number and the base. Then there is the "LOG10" function where excel assumes your base is 10.

Running Solver with vba you will need to set a reference to Solver in Visual Basic (VB -> Tools -> References and mark box named Solver)

For the loop you could use something like

Please Login or Register  to view this content.

where "”$??$$ & i," is your target cell that you need to specify a column, as well as change the MaxMin to what you wish your target value to be Min or Max or a value of. And then you probably has some constraints that should be added.

Alf

[panyagak]

Alf

(n) is given
There are 2 competitors, n

Target value = 1.00

The iteration part is the problem in a 1.51 3.69 5.30 example.

Where 1/1.51+1/3.69+1/5.30 >1.00 (1.12)

I need 1/a+1/b+1/c=1.00 and k iteration determines that

thanks
(attachment failed)

[Alf]

attachment failed

Well forum do suffer the occasional hiccups but just to be sure. You did use the "Go Advanced" button and then click on the blue line "Manage Attachment" in order to select and upload a file?

Alf

[panyagak]

(in attachment copied)

To remove over-round from bookmakers prices P we have adjusted price = 1/x = P^(1/k) or Adjusted price = P^k' where k'=log(n)/log(nR) where n is the number of outcomes and R is the return to punter = (1- O). O is Overround

However since this only gives the required R for equally likely outcomes, we need to use iteration to produce probabilities that sum to 1. This is easily performed in a spreadsheet.

Consider the prices for a tennis match where published prices are $1.22 and $4.33.

This gives implied probabilities of 0.820 (1/1.22) and 0.231 (1/4.33) for an over-round of 0.051 and return to punter R of 0.952 and so k = 0.929.

The power method initially gives probabilities of .807 and 0.206, but these still sum to more than one.

Using iteration to adjust k to correct this, we obtain k = 0.904 and probabilities of 0.802 and 0.198 HOW????. (Iteration)

[Alf]

My math know-how is not that profound so I'm not sure how to set this up. Let's hope that shg or MrShorty sees this thread as they really knows the math I think you need to solve this problem.

Alf

[MrShorty]

I saw this, but I'm having trouble understanding exactly what your question is. Here's what I understand about the project:

1) We have a list of values (2 or 3 or more, up to n). These values fit into an equation that looks something like P=Sum[for i=1 to n](Pi) where Pi=Xi^-k. P should be exactly 1 by solving for k. Combining it all together, we have something like 1=Sum[for i=1 to n](Xi^-k). I may have misunderstood some detail(s), but the problem seems to look something like that.
2) Of course, except for n=1, this equation cannot be solved for k, so you need some kind of numeric root finding algorithm to solve this. Most Excel users will use the build in Solver/Goal seek utilities for the root finding algorithm part of a problem like this. For a single or rare problem, simply calling Solver manually should be easy enough to solve for k.
3) In your case, you say that you have 800+ individual problems to solve. One possible approach would be to call Solver from VBA within a loop. Alf's post in 2 and 3 contains a simple For..Next loop that calls Solver, so it seems that Alf's code should have been readily adapted to the problem.

So at this point I am not sure exactly what the question is. Are you able to build a sequence of cells in the spreadsheet to calculate P at some guess for k? Are you able to call Solver manually to solve for one incidence of k? Are you able to adapt Alf's loop code to solve multiple incidences of k?