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, 2022 in Others by Kithuzzz
• 38,010 points
675 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, 2022 by narikkadan
• 63,700 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, 2022 in Others by narikkadan
• 63,700 points
851 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, 2022 in Others by narikkadan
• 63,700 points
338 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, 2022 in Others by narikkadan
• 63,700 points
1,934 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, 2022 in Others by narikkadan
• 63,700 points
1,135 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, 2022 in Others by narikkadan
• 63,700 points
1,120 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, 2022 in Others by narikkadan
• 63,700 points
723 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, 2022 in Others by narikkadan
• 63,700 points
1,610 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, 2022 in Others by narikkadan
• 63,700 points
1,430 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, 2022 in Others by narikkadan
• 63,700 points
4,185 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, 2022 in Others by narikkadan
• 63,700 points
606 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