Compute the boundaries formula
- Thread starter Eloise T
- Start date
I’m traveling so can only describe what is wrong
The S is actually an Integral sign
So it means to sum from 0 to 1 the formula after it with respect to small steps in x
The dX bit means in small increments of x
I would setup a table of values from say 0.05 to 0.95 in steps of 0.1
Then apply your formula to each value multiply it by 0.1 (the dX)
Then add up the results
Then do it again from day 0.005 to 0.995 using dX = 0.01
The more steps you use the more accurate the result
The S is actually an Integral sign
So it means to sum from 0 to 1 the formula after it with respect to small steps in x
The dX bit means in small increments of x
I would setup a table of values from say 0.05 to 0.95 in steps of 0.1
Then apply your formula to each value multiply it by 0.1 (the dX)
Then add up the results
Then do it again from day 0.005 to 0.995 using dX = 0.01
The more steps you use the more accurate the result
Peter Bartholomew
Well-Known Member
@Hui It turns out I have done precisely what you suggested as the more accurate solution. The way I have done it is not totally fair on Eloise. The entire calculation is done using named formulas, as is the plot of the integrand.
If you are travelling you may not have noticed but the denominator goes to zero at x = 1 so the dx/2 offset is critical to avoid #DIV/0!
@Eloise T
Don't suffer too much. If you need me to, I will explain.
If you are travelling you may not have noticed but the denominator goes to zero at x = 1 so the dx/2 offset is critical to avoid #DIV/0!
@Eloise T
Don't suffer too much. If you need me to, I will explain.
Peter Bartholomew
Well-Known Member
I have come to the conclusion that it might be better to allow the user to input the number of steps, N, rather than step length dx.
Peter Bartholomew
Well-Known Member
The integrand might be easier to calculate if the factor of √(1-x) were taken out of the denominator to give the integral in the form
Peter Bartholomew
Well-Known Member
Hi @Eloise , I probably owe you an apology for landing that spreadsheet on you; I took the problem up as a challenge! If it is any consolation this is about as obscure as it gets (unless, of course, someone better informed knows otherwise!)
Just by way of apology, I have included a further version with Season's greetings! Other than a bauble or two, the difference is a bit of VBA that loads any new formula from the cell G9 into the named formula F and evaluates it. The intention is that the user formula should reference the array 'x' which interpolates the range of integration from 'a' to 'b'. The function 'F' is used both as a chart series and is summed to give the estimate of the definite integral.
If you go into the Formulas ribbon tab and open Name Manager you will see that 'k' is an array of row numbers associated with a column range with 'N' cells. Both the chart and the sum force Excel to evaluate that array in memory and carry the calculation forward until it generates the desired result.
The shock for most people is to realise that you do not have to use worksheet cells at all to perform calculations in Excel. I fact, the calculation engine performs better on named arrays than it does on cell formulas because there the calculation is always an array calculation.
p.s. To enter a new formula, you can just type it as a string provided you are confident you will get it right. If not, type it with the '=' sign so that you get IntelliSense help building the formula and then allow the macro replace the formula by the string and set the formula property of the Name 'F'. The integration step length is changed by changing N and the upper and lower bounds of integration can be changed.
Just by way of apology, I have included a further version with Season's greetings! Other than a bauble or two, the difference is a bit of VBA that loads any new formula from the cell G9 into the named formula F and evaluates it. The intention is that the user formula should reference the array 'x' which interpolates the range of integration from 'a' to 'b'. The function 'F' is used both as a chart series and is summed to give the estimate of the definite integral.
If you go into the Formulas ribbon tab and open Name Manager you will see that 'k' is an array of row numbers associated with a column range with 'N' cells. Both the chart and the sum force Excel to evaluate that array in memory and carry the calculation forward until it generates the desired result.
The shock for most people is to realise that you do not have to use worksheet cells at all to perform calculations in Excel. I fact, the calculation engine performs better on named arrays than it does on cell formulas because there the calculation is always an array calculation.
p.s. To enter a new formula, you can just type it as a string provided you are confident you will get it right. If not, type it with the '=' sign so that you get IntelliSense help building the formula and then allow the macro replace the formula by the string and set the formula property of the Name 'F'. The integration step length is changed by changing N and the upper and lower bounds of integration can be changed.