How can solve this Solver Excel like problem in R

0 votes

enter image description here

I have difficulty implementing the following optimization problem which I know the solution as presented in excel and now I am trying to implement it in R.

The data are :

Kurt=4
Skew =-0.2

as k and t respectively as shown in the picture.

Finding the lower and upper omega is simple in R:

For the lower omega:

w1 = c(-Kurt-6,0,3,2,1)
f = polyroot(w1)
w11=max(Re(f));w11
[1] 1.206575
w2 = c(-4-(Skew)^2 ,0,3,1)
g = polyroot(w2);g
w22=max(Re(g));w22
wl = max(w11,w22) ;wl
1] 1.206575

For the upper omega:

wu = (-1+(2*(Kurt+2))^(0.5))^(0.5)
wu
[1] 1.569746

For the upper and lower omega R agrees with Excel.

Now the problem is that the excel Solver finds the omega value to be 1.56425 but I don't know how to verify it in R.

I tried as well the optimize function for the upper and lower:

m = function(x){ (4+2*( x^2 - ((Kurt +6) /(x^2+2*x+3) )   ))^(0.5) }
om = function(x){(x-1-m(x) )*(x+2+(m(x) /2))^2 - Skew}
optimize(om,interval = c(wl,wu), maximum = TRUE)

but I don't know how to find the result 1.56425 between the upper and lower omega as described in the photo. It makes a Solver in Excel but I don't know to perform it in R.

Excuse me for the photo but stack overflow does not have latex in order to present it properly.

Any help? How can I do it in R?

The excel sheet is the below picture:

enter image description here

Nov 20 in Others by Kithuzzz
• 20,660 points
37 views

1 answer to this question.

0 votes

More details and explanations are required so that people can accurately follow your example. (For example, what is m and why does it depend on x? Instead of seeking a zero, why do you optimize in the final step?).

In any event, you may test Excel's answer by entering it into the equation and seeing if it holds.

Additionally, you can plot your function to determine why the optimization might not succeed:

Also, you can also plot your function in order to see why the optimization might fail:

plot(x = seq(wl, wu, length.out = 50),
     y = om(seq(wl, wu, length.out = 50)))
answered Nov 20 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

Excel VBA : HOW TO PRINT THE TEXT IN A CELL (like Wrap Text)

Use a LineFeed character to create a ...READ MORE

answered Oct 27 in Others by narikkadan
• 37,660 points
73 views
0 votes
1 answer

How can I copy from specific sheet in excel

You should use wb.Sheets("Name of sheet").Copy - ...READ MORE

answered Oct 29 in Others by narikkadan
• 37,660 points
21 views
0 votes
1 answer

How can I sort one set of data to match another set of data in Excel?

In addition, INDEX MATCH is a more ...READ MORE

answered Oct 29 in Others by narikkadan
• 37,660 points
43 views
0 votes
1 answer

Excel - How can I get the average of cells where the value in one column is X and the value in another column is Y?

Use AVERAGEIFS ... =AVERAGEIFS(C2:C13,A2:A13,"Yellow Typ ...READ MORE

answered Nov 11 in Others by narikkadan
• 37,660 points
36 views
0 votes
1 answer

Download multiple excel files linked through urls in R

Try something along the lines of: for (i ...READ MORE

answered Sep 23 in Others by narikkadan
• 37,660 points
87 views
0 votes
1 answer

Excel, How to split cells by comma delimiter into new cells

The Excel manual method: choose Text to Column ...READ MORE

answered Oct 3 in Others by narikkadan
• 37,660 points
67 views
0 votes
1 answer

Comparing two Excel files in R and returning different titles

Solution: library(tidyverse) dat <- read_xlsx("Book1.xlsx") dat2 <- read_xlsx("Book2.xlsx") book1_output <- anti_join(dat,dat2, ...READ MORE

answered Oct 14 in Others by narikkadan
• 37,660 points
71 views
0 votes
1 answer

Conversion of PDF file to Excel in R

I looked at the pdf, and it ...READ MORE

answered Oct 16 in Others by narikkadan
• 37,660 points
70 views
0 votes
1 answer

How can I find and replace text in Word using Excel VBA?

Try this code Option Explicit Const wdReplaceAll = 2 Sub ...READ MORE

answered Oct 15 in Others by narikkadan
• 37,660 points
100 views
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24 in Others by narikkadan
• 37,660 points
62 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP