Plotting Mandelbrot

There are many sites discussing the Mandelbrot Set, but not many examples using VBA to plot the set in Excel, so here is my effort:

The spreadsheet (including open source VBA code) may be downloaded from:

Mandelbrot chart.zip

Note that to keep the download file a reasonable size the file has been saved with a low resolution image.  350 x 350 resolution gives a good compromise between image quality and speed of processing and plotting.

Required input is:

  • Centre point coordinates
  • Plot width (= height)
  • Plot resolution
  • Number of iterations

The results are plotted as a scatter chart  with 7 data ranges, plotting markers only.

The plot below was generated with 28 iterations, and a resolution of 350×350.  Calculation time was 0.33 seconds:

The second example was taken from:

How To Quickly Compute The Mandelbrot Set In Python

Higher resolution (1000×1000) with 2048 iterations resulted in much longer calculation time, but times are 2-3 times faster than the plain Python code given in the link:

For faster plotting compiled code is required.  A good site providing fast high resolution plots for user selected location, scale and number of iterations is:

Mandelbrot Set

The site text says the plots will not work in Microsoft browsers, but they worked with no problem for me in Internet Explorer.  The image below was generated in the spreadsheet using parameters from the link above.

Posted in Charts, Charts, Coordinate Geometry, Drawing, Excel, Maths, Newton, VBA | Tagged , , , | 3 Comments

Using conditional formatting with array formulas

A recent Quora question asked “How do I highlight the number closest to an initial number in a row in Excel?“.  The answer by Bill Jelen provides a good example of using conditional formatting with an array formula:

In my data, I have the Goal or Initial Number in column A. Then, there are 12 results in B:M of each row. Select B3:M16. Home, Conditional Formatting, New Rule, Use a Formula To Determine Which Cells To Format. The formula will be =ABS($A3-B3)=MIN(ABS($A3-$B3:$M3)). Click the Format button and choose your desired fill color or font color.

Note the single dollar sign before the $A, $A, $B, and $M are crucial to make this work. As Conditional Formatting evaluates each cell, those dollar signs make sure that the logic is always pointing at A for the Initial cell and the range of B:M for the other values which may or may not be closer.

Note: This was created using Office 365 Insider which has access to Dynamic Arrays.

When used for conditional formatting, this formula also works with non-Insider versions of Office 365, as can be seen in my example below:

Posted in Arrays, Excel | Tagged , , , | Leave a comment

64 bit Excel and the Strand7 API

Back in May 2017 I posted a sample spreadsheet using the Strand7 API to allow Excel to read and write Strand7 node data and results.  A comment noted at the time that the spreadsheet was not working with 64 bit Excel.  Having now installed 64 bit Excel myself I can confirm that:

  • The API for the current Strand7 version (R2.4.6) does not work with 64 bit Excel.
  • The beta version of the next release (R3) is available for users with a current licence, and this has a full version of the API that supports 64 bit Excel.
  • The R3 beta version is currently updated on a monthly basis, and has to be reinstalled each month.
  • The API sample spreadsheet currently only supports the 32 bit API.  A 64 bit version will be uploaded in the near future.

 

 

Posted in Excel, Finite Element Analysis, Frame Analysis, Link to dll, Newton, Strand7 | Tagged , | Leave a comment

64 bit Excel and PtrSafe

Installing my Office 365 subscription on a new computer I discovered it had decided (without asking) to upgrade itself to the 64 bit version, which resulted in some of my spreadsheets not working on the new system.  The main problem is that where VBA code calls an external dll or xll file with a Declare statement, the Declare must be followed by PtrSafe for 64 bit Excel.

A comprehensive article dealing with this problem can be found at: Declaring API functions in 64 bit Office.

The following article deals with one simple example, how to deal with calls to the microtimer function, which is the main offender amongst my spreadsheets.

Revised code from the RC Design Functions spreadsheet is shown below:

#If VBA7 Then
  Private Declare PtrSafe Function getFrequency Lib "kernel32" _
  Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
  Private Declare PtrSafe Function getTickCount Lib "kernel32" _
  Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
#Else
  Private Declare Function getFrequency Lib "kernel32" _
  Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
  Private Declare Function getTickCount Lib "kernel32" _
  Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
#End If

Note that:

  • “PtrSafe” must be inserted for 64 bit Excel, and will work with recent 32 bit versions (those using VBA7), but will raise an error with earlier versions of VBA.
  • With 64 bit Excel the VBA editor will show the lines without PtrSafe in red, and may raise an error during editing, but the code will run without problems with all VBA versions.

The updated version of the spreadsheet can be downloaded from: RC Design Functions8.zip

I will be progressively updating my spreadsheets as I use them, but please let me know if you are using 64 bit Excel and find a spreadsheet that has problems.

 

Posted in Excel, VBA | Tagged , , , , | Leave a comment

Concrete 2019

The Concrete Institute of Australia’s biennial conference is this year being held in Sydney from 8-11 September, and early-bird registration has now been extended to 15th July, so register now and save!

For more details of the conference see:

Concrete in Practice – Progress through knowledge

and for a lightning tour of Sydney and surrounds see:

Posted in Concrete, Newton | Tagged , , , | 2 Comments

Installing Adobe Reader non-DC version.

The option to install a non-DC version of Adobe Reader used to be hidden away under a menu on the DC installation screen.  It seems that Adobe would really, really like you to use the DC version, because the other options have been removed.

Non DC versions are still available though from this link. The link takes you first to a forum page, then directly on to the download page, but may take some time before the download box appears.

The link was provided at response No. 14 (thegeneticbytemare) at Where can I get Acrobat Reader without DC .  That response also includes links to the latest upgrade and other language versions, but note that you have to install the full version before you can install an upgrade.

Posted in Computing - general | Tagged , , | Leave a comment

Solving the Lagrangian Point equation for the Moon

This post was prompted by a recent question at Fitting high order polynomials, asking for Excel methods to solve the equations for the radius of the Moon’s Lagrangian Point 1. All the methods described in this post have been added to the ItSolve Functions2 spreadsheet, which can be downloaded from:

ItSolve.zip

The question asked for a solution to two equations:

  • Simplified: r = R*(Mm/(3*Me))^(1/3)
  • Detailed:   Find r so that (Me/(R+r)^2)+(Mm/r^2)=(Me/R^2)+r*(Me+Mm)/R^3

The simplified equation may be easily solved by entering as a spreadsheet formula:
=O7*(O6/(3*O5))^(1/3)
The spreadsheet also includes a User Defined Function (UDF) called Eval, that will evaluate a formula entered as a text string, as shown below:

The Eval UDF returns the same result as the spreadsheet formula:

The simplest way to solve the more complex formula is to use the built in Goal Seek function (on the Data Tab), under What-if Analysis.  To use Goal Seek first enter a guessed value for the radius, r, then enter a cell formula to evaluate the function:
(Me/(R+r)^2)+(Mm/r^2)-((Me/R^2)+r*(Me+Mm)/R^3)
as shown below:

Then call the Goal Seek function (Data – What-if analysis – Goal Seek):

Goal Seek will adjust the value in the selected changing cell (O15) so that the “set cell” (O18) evaluates to the selected value (0):

The Goal Seek function is built-in, and is the most convenient for a one-off solution, but it is slow and cumbersome to use for a large range of data.  The UDFs QuadBrent and QuadBrentT are much quicker and more convenient to use on tabular data.  The input for the QuadbrentT function is shown below:

Entering the function returns exactly the same result:

Finally the function may be rearranged as a quintic polynomial, that can be solved with the RPolyJT function:

RPolyJT returns all 5 solutions of the quintic polynomial as an array function, but in this case there is only one real solution, which is the first value in the results array.

More details of the UDFs described above can be found at:

Posted in Excel, Maths, Newton, UDFs, VBA | Tagged , , , , , , | 3 Comments