VideoPhoto

30 July, 2023

Charts Based On Parametric Equations: Sinusoidal (Oscilloscope-type) Curves

Curves that can be used in physics for analysing pairs of simple harmonic motions are generally known as Lissajous Curves. They describe the superposition of two oscillations taking place at the right angle to each other,  at different frequencies. Their main application can be found in oscilloscopes. These instruments allow observation of two superimposed sine waves coming from varying signal voltages of sinusoidal nature. Depending on the frequency and amplitude of each wave, and the phase between them, we can see interesting patterns emerging.

Because the superimposed two perpendicular oscillations can be described with parametric equations for x and y in the Cartesian coordinate system, we can quite easily demonstrate the resulting shapes graphically using Excel charts. Let's look first at some of "oscilloscopic" patterns generated with Excel.

29 July, 2023

Shapes Based On Equations: Cardioids

Cardioid curve is formed by tracing a point selected on the circumference of a circle, rolling onto another circle of the same radius. Here are two examples of cardioid graphics, one is just a single cardioid curve and the other is presentation of 4 cardioids positioned within the same coordinates.

These kind of graphics can be generated using the VBA macro listed below the two charts.

28 July, 2023

Shapes Based On Parametric Equations: Epitrochoids

Epitrochoids are curves generated by a point selected on a circle of smaller radius rolling around the outside of a fixed circle of larger radius, and that selected point can be chosen at some distance from the center of the smaller circle. These are then 3-parametric curves. They can be helpful in creating decorative designs and other art applications, and can serve also as pastime and recreation for children. Since 1965 there is available a toy (geometric drawing device) known as spirograph, combining mathematics and art. It allows producing physically numerous curve designs based exactly on epitrochoid and hypotrochoid parametric equations.

However, nowadays the epitrochoids (as well as hypotrochoids - see another post at https://draft.blogger.com/blog/post/edit/3323809043368251287/9189797010242374274?hl=en-GB) can be generated quite easily in Excel by using VBA macro like the one provided at the end of this post.

Here are some examples of charts showing epitrochoid curves generated with the macro. Some of them present combined double curves.

27 July, 2023

Shapes Based On Parametric Equations: Hypotrochoids

Hypotrochoids are curves generated by a point selected on a circle of smaller radius rolling around the inside of a fixed circle of larger radius, and that selected point can be chosen at some distance from the center of the smaller circle. These are then 3-parametric curves.

You don't need any special programs, like e.g. Mathematica, to create hypotrochoids. They can be generated in Excel quite easily by using VBA macro like the one provided at the end of this post.

Here are some examples of charts with generated hypocycloid curves, both single and combined double curves:


Shapes Based On Parametric Equations: Hypocycloids

Hypocycloid is a parametric curve traced by a fixed point on a circle that rolls on the interior of another larger circle. 

The curve - due to its parametric functionality - finds many applications, both theoretical and practical. They include mechanical and construction engineering, e.g. design of gears, cams, valves, robotics (motion planning), and machine/structures design in general. Contribute also to aesthetically pleasing art designs and animations.

The hypocycloid curves can be generated quite easily in Excel with VBA macro,  presented at the end of this post. Here are some examples of charts with generated hypocycloid curves, both single and combined double curves:


25 July, 2023

Shapes Based On Parametric Equations: Epicycloids

Epicycloid is a parametric geometric curve obtained by tracing the path of a chosen point on the circumference of a circle (outside of it). Variety of epicycloid curves find applications in mechanical and construction engineering, e.g. construction of gears, cams, valves, pendulum clocks, robotic actuators, and machine/structures design in general. Contribute also to making designs of arts and animations.

In this context, it is useful to know how to generate epicycloid curves. In this post I'm presenting some of the curves along with the Excel VBA macro for creating this kind of curves. You can try to use it for your own creations. Just copy it to one of the modules in your workbook sheets and experiment with different settings and parameters.

Here are some examples of charts with epicycloid curves, single and double plots. 

23 July, 2023

Curves Based On Equations: Lemniscate of Bernoulli

The Lemniscate curve is based on specific algebraic polynomial equation. It has a shape similar to the numeral 8 and to the infinity symbol (called also a ribbon). You can create it with Excel VBA code and use it as one of the elements of graphic design.

Here are two examples of the curve created with the macro code listed below them.

22 July, 2023

Excel Comments: Setting Font Format

Default font format in Excel Comments may look like this:

If you want to change some of the font settings (i.e. the Windows font settings), you can either try to adjust font format in "Display settings" of Windows or use VBA code listed below. The second option is better, because it doesn't change font settings in all other Windows applications. Remember that you must make your Comments visible. If necessary, right-click in the cell and click on Show/Hide Comments option.

This is the event driven macro. To use it, you need to:

  • open the Visual Basic for Applications (VBA) editor in Excel (use the shortcut ALT+F11 on your keyboard)
  • click on the View in the top menu and choose Project Explorer option
  • in your VBAProject double-click on Sheet1 and paste the macro code in the window
  • close the VBA editor and return to your Excel worksheet

16 July, 2023

Excel Shapes: Positioning, Formatting and Layout In Worksheets

Excel Shapes are frequently used to enhance visual appeal of worksheets as well as to convey information. It's relatively easy to work with the Shapes. You can insert, resize, rotate, format, and position them within the worksheet, according to your needs.

This post is about special case of positioning and manipulating of Shapes. Namely, I'll show the way of inserting and moving them so that they are located/centred precisely at the crosspoint of 4 surrounding it cells. Furthermore, in this case I'll use a worksheet with squared cells. If you need to know how to make cells square please refer to one of my previous posts (Make cells SQUARE) .

Here are just two examples showing what I'm talking about:

 
These are the formatted Shapes called "Donut" and "Gear with nine teeth". They are placed exactly at the point where the four cells meet together. It's not that easy to do that by hand, so I've created the following Excel macro, to handle this task swiftly:

14 July, 2023

Prime Numbers: Their Importance In Computational Security and Other Applications

Prime numbers primarily constitute the basis of cryptography (hidden writing) and cybersecurity, i.e. secure transmission and storage of digital data. They are used in mathematical algorithms that enable efficient encryption (converting into code) and decryption (converting to plain text) of data.

And everything starts with generating and selection of just two prime numbers. However, the generated primes must be really very high numbers for the purpose. The higher they are the better becomes security/privacy of communication between parties.

Beside that, the whole concept of cryptography relies also on the complexity of applied algorithms (ciphers). And these are still based on prime numbers and are evolving all the time.

Cryptocurrencies, such as Bitcoin, are also created with with cryptographic algorithms and rely on prime numbers for ensuring secure transactions and protecting private information.

Primes are used as well in mechanical design, machinery and engineering structures. Choosing primes for e.g. the number of gear tooth, blades, or similar structural elements, helps to ensure reduction of the wear of materials, reduction or better distribution of vibrations, eliminating unwanted resonances.

In machine design - uniform gear wear, reduced noise or desired gear ratios can be achieved by choosing the tooth counts to be co-primes (relatively prime), i.e. 7 & 12, 13 & 21, or 7 & 9 & 12. This is an example of gear with co-primed  numbers of cogs: 7 & 12.

 

In electrical engineering - prime numbers are used in the design of power distribution system to improve the efficiency of the system.

There are also dozens of important uses for prime numbers in many sciences. So, if you are really interested and want to be better educated in the subject of primes and related to them algorithms it's worth to familiarize yourself with one of the most stimulating recent publications by Gary William Croft: "The Prime Spiral Sieve". It can be found at https://www.primesdemystified.com .


13 July, 2023

Prime Numbers: Presentation of Distribution of Primes with Waterfall Charts

One way of effective visual presentation of prime numbers is using Excel charts. And the Waterfall type of chart seems to be quite useful. It shows a running total as values are added to your data set; in this case - as consecutive primes are added. The running totals can be shown on the vertical axis as well as on the chart itself (for each subsequent prime value).

Here are some examples of this kind of visualization of running totals of primes. You'll need to enlarge the charts or use great size screen area in order to see the charts in detail.

12 July, 2023

Prime Numbers: How to Find the Highest Prime Number Below Given Limit

If you need to find/generate the highest prime number not exceeding some given limit, like e.g. 100,000, using the following Excel VBA macro is the appropriate and easy to use tool; obviously, within the Excel program specifications.

Just copy and paste the code into one of the modules of your workbook. Some explanatory notes are included within the macro.

Sub CalcTopPrimeNumberBelowGivenLimit()
'Max prime <1,000,000: 999,983
'Max prime <10,000,000: 9,999,991
'Max prime <100,000,000: 99,999,989
'Max prime <1,000,000,000: 999,999,937
'For max. integer handled by my computer (=1,068,699,999) the highest prime is: 1,068,699,979
    Dim n As Long
    Dim primes() As Boolean
    Dim i As Long, j As Long
    Dim tPrime As Long
    'Set the maximum limit for prime number generation
    n = InputBox("Enter your max. limit for the prime generation:", "Set the max. limit", 199999999)
    'Initialize the array to assume all numbers are prime
    ReDim primes(2 To n)
    For i = 2 To n
        primes(i) = True
    Next i
    'Apply the Sieve of Eratosthenes algorithm (it finds all primes up to a given limit)
    For i = 2 To Sqr(n)
        If primes(i) Then
            For j = i ^ 2 To n Step i
                primes(j) = False
            Next j
        End If
    Next i
    'Find the highest prime number
    For i = n To 2 Step -1
        If primes(i) Then
            tPrime = i
            Exit For
        End If
    Next i
    'Display the result
    MsgBox "The highest prime number below " & n & " is " & tPrime
End Sub 

 

11 July, 2023

Prime Numbers: How to Check If a Number is Prime Number

Prime number is defined as a whole number > 1 (integer) that cannot be divided by any whole number other than itself and 1.

Sometimes you may need to know if some specific positive whole number is a prime number or not.

At first look we can tell that given number is NOT a prime, if it is >5 and its last digit is equal 0,2,4,5,6 or 8. All primes >5 end in 1,3,7 or 9.

While its easy to identify primes within low range of integers (let's say, up to 100 or 500), it's becoming harder as we want to deal with higher range of numbers. Here are the lowest 81 prime numbers:

03 July, 2023

Looking for and Formatting Cells with Different Contents In One Pass

To find and select (and optionally, to format) cells that contain specific information or meet specific conditions we would usually use Go To command. That's simple: on the Home tab click Find & Select > Go To (or use shortcut: CTRL+G). When we click Special in the displayed Go To Special dialog box, we get many options to choose from; can look for Comments, Constants, Formulas, Blanks, Objects, Last cell, Conditional formats, etc.

However, we may need to use a completely different option. What if we'd like to find simultaneously (in one pass) for e.g. two or more different numbers or strings? There is no such option in the dialog box, so we have to find out a different approach.

Here's an example; I'm using the Excel macro listed below to find and fill with different colors all cells (in the specified range) containing four specific values. I need only replace the default values "a,b,3,4" with my own; here I've used "lost,123,mat,cat":

Sub HighlightSpecificCells()
'Applies different fill colors to cells containing different specified values
'This example fills cells containing four different strings/values

Dim rng As Range
Dim cell As Range
Dim inp As String
Dim vaArray() As String
Set rng = Range("A1:Z100")  'Change the range to the desired one
inp = InputBox("Enter values, separated by comma, you want to find in the specified range", _
    "Find and format cells that store specified values", "a,b,3,4")
vaArray = Split(inp, ",")   'Split the string using comma as the delimiter
'Loop through cells of the range
For Each cell In rng
    If cell.Value = vaArray(0) Then cell.Interior.ColorIndex = 4
    If cell.Value = vaArray(1) Then cell.Interior.ColorIndex = 6
    If cell.Value = vaArray(2) Then cell.Interior.ColorIndex = 24
    If cell.Value = vaArray(3) Then cell.Interior.ColorIndex = 44
Next cell
End Sub

This way, in a single pass you find your all specified values and fill the cells with the specified different color for each value.

This is just an example. You can modify the macro to meet your specific number of values, choice of colors, and whatever else you need.