Concepts in Mathematics

By David Alderoty © 2015

 

Chapter 8) Illustrating and Writing Mathematics with Microsoft Excel, SpreadsheetConverter, and Mathcad

Over 2,600 Words

 

To contact the author left click for a

website communication form, or use:

David@TechForText.com

 

To go to the previous chapter

left click on one of the following links:

 

For HTML

www.TechForText.com/Ma/Chapter-7

 

For PDF

www.TechForText.com/Ma/Chapter-7/PDF.pdf

 

If you want to go to the table of contents

of this CHAPTER left click on these words

 

 

To Access Additional Information with Hyperlinks

 

After I complete a writing task, I select a number of websites from other authors, and link to them, to provide additional information and alternative perspectives for the reader.  The links are the blue underlined words, and they can be seen throughout this e‑book.  The in‑line links, such as the link on these words, are primarily to support the material I wrote, or to provide additional details.  The links presented at the end of some of the paragraphs, subsections, and sections are primarily for websites with additional information, or alternative points of view, or to support the material I wrote.  The websites contain articles, videos, and other useful material.

     The brown text that look like these fonts, represent quotes in this book.  You can access the original source, by clicking on a link presented just before a quote.

     If a link fails, use the blue underlined words as a search phrase, with www.Google.com.  If the failed link is for a video use www.google.com/videohpThe search will usually bring up the original website, or one or more good alternatives.

 

 

Microsoft Excel, SpreadsheetConverter, and Explaining and Demonstrating Mathematical Concepts

 

Note for this Section

 

This section deals with mathematical concepts, Microsoft Excel, and SpreadsheetConverter.  The focus is mathematics that relate to the date and time functionality of Microsoft Excel.  However, this chapter does not provide detailed instruction on how to use Microsoft Excel, or SpreadsheetConverter.  If you want instruction on how to use the above software, see the web-based videos and articles at the end of this section.

 

 

Microsoft Excel, and SpreadsheetConverter

 

Most people are familiar with Microsoft Excel as a spreadsheet for addition, subtraction, and statistical calculations.  However, Excel has a much greater functionality, and it can be used to create sophisticated calculation devices.  These devices generally do not look like spreadsheets, and they can carry out complex sequences of mathematical calculation simultaneously.  These calculation devices are useful for explaining and/or demonstrating mathematical concepts.

     In the following subsections, I present a complex mathematical concept, and demonstrate and explain it, with JavaScript software that I created with Microsoft Excel and SpreadsheetConverter.

     When an Excel device is converted to HTML-JavaScript, with SpreadsheetConverter, it functions directly in a web browser, without Excel.

 

 

A Demonstration Device, in the Form of a Software-Based Calendar, to Illustrate a Mathematical Concept, Involving Repetitive Cycles

 

I created a software-based device, in the form of a special calendar, to demonstrate a mathematical concept that can be difficult to understand.  The idea here is if the reader does not understand the explanation, they can at least see that the mathematical concept functions in the form of software.  With this technique, it is necessary to display the related formulas directly on the software, along with explanations, which the reader may or may not understand in its entirety.

     The mathematical concept that I explain with the software is useful for creating formulas that calculate dates based on specific days of the week.  This is actually a general mathematical concept, but I am applying it to calculation devices created with Microsoft Excel.  See the following formulas I devised which function Excel.

 

Calculates dates that fall on Saturdays

=7*ROUNDDOWN((days/7),0)

 

Calculates dates that fall on Sundays

=7*ROUNDDOWN((days/7),0)+1

 

Calculates dates that fall on Mondays

=7*ROUNDDOWN((days/7),0)+2

 

Calculates dates that fall on Tuesdays

=7*ROUNDDOWN((days/7),0)+3

 

Calculates dates that fall on Wednesdays

=7*ROUNDDOWN((days/7),0)+4

 

Calculates dates that fall on Thursday =7*ROUNDDOWN((days/7),0)+5

 

Calculates dates that fall on Fridays =7*ROUNDDOWN((days/7),0)+6

 

 

The Above Formulas, and Related Mathematical Concept

 

The first formula listed above, =7*ROUNDDOWN((days/7),0) calculates dates with Saturday, when used in Microsoft Excel.  With this formula, Excel converts a date, to a number of days from a predefined zero point.  The predefined zero point in Microsoft Excel is on a Saturday.  Then the days are divided by seven, to convert them to weeks.  The resulting number is rounded down to eliminate decimals.  These decimals represent fractions of a week.  Then the result is multiplied by seven, to convert it back from weeks to days.  The days are converted back into a date, with Microsoft Excel format code.  The result is always a Saturday, because, the predefined zero point in Microsoft Excel is on a Saturday, and the formula presents a date that is a precise number of weeks from the zero point.  (This is actually common sense.  For example, if today is Thursday, 10 weeks ago, it was also Thursday.)

     The other formulas listed above, simply add a number to the above result.  For example, =7*ROUNDDOWN((days/7),0)+1, always calculates dates that fall on a Sunday.

     To fully understand the above concept examine the software linked to this page, and read the following sections on Microsoft Excel’s date and time functionality.

 

HTML-JavaScript software

www.TechForText.com/Ma/C

 

Download link for Excel version www.TechForText.com/Ma/CR.xlsx

 

You can download the JavaScript version, and two Excel versions in a zip folder, by clicking on the link below.  Note: You must remove the JavaScript version, with the cut and paste function, from the zip folder, before using it.  The JavaScript version is in a separate folder with filename: ClickOnIconWith-index.htm.  One of the Excel versions has unlocked cells, for those who want to study the formulas that comprise the software.  The filename is UnlockedToStudyFormulas.xlsx.  Click on the following link to download the zip folder: www.TechForText.com/Ma/CR.zip

 

 

The Original Utility of the Above Formulas

 

I originally devised the above concept, and related formulas, when I encountered a problem while creating calendars, with Microsoft Excel.  Specifically, conventional calendars, display Sundays on the extreme left.  However, the calendars I initially created, would display the days, based on the start date that the user entered.  For example, if the user entered a start date of May 1, Friday, 2015, Fridays would be displayed on the extreme left, instead of Sundays. 

     To solve the above problem, I configured the calendars to electronically divert the start date entered by the user, into the following formula: =7*ROUNDDOWN((days/7),0)+1.  This formula would calculate a date that was a Sunday, which was usually a few days earlier than the start date the user entered.  (If the user entered a start date that was on a Sunday, that date would automatically be used by the software.)  The formula was placed in the extreme left.  The remaining days on the calendar were calculated in proper sequence from the first Sunday, by adding an appropriate number.  As a result, all seven days (from Sunday to Saturday) would be automatically placed on the calendar in conventional style, by the software.

 

 

Repetitive Cycles, for Dates, and Time, and Microsoft Excel

 

Repetitive cycles, such as the seven days in the week, and the 24 hours in a day, have an endpoint, and a starting point.  However, in many situations, especially with software, the endpoint and starting point of the cycle can be difficult to determine.  For example, most software and programming languages, calculate time and dates from a fixed point in time, which might be over 100 years in the past.  For example, Microsoft Excel uses Sunday, January 1, 1900, at 12 AM, as the starting point for their date and time cycle.  The above is day one, which implies that time zero (or day zero) is Saturday, December 31, 1899.  The formulas described above are based on this zero point.  The proof for this concept is presented in the following subsection.

 

 

Determining DAY ONE, and DAY ZERO, with Microsoft Excel

 

If 1 is inserted into a cell, with the following format code:

h:mm AM/PM, dddd, mmmm d, yyyy, Microsoft Excel displays the following time and date:

12:00 AM, Sunday, January 1, 1900

     When 0 (zero) is inserted into a cell, with the format code presented above Excel displays:

12:00 AM, Saturday, January 0, 1900

Because there are no zeros on the calendar, from a mathematical perspective this can be defined as Saturday, December 31, 1899.  The formulas that were previously presented, are based on the concept that day zero, or time zero, is defined by Microsoft Excel as a Saturday.

 

 

The Units of Time Used by Microsoft Excel’s Internal Mechanism

 

The timing mechanism in Excel uses days, and fractions of the day, to calculate time.  For example, today is Tuesday, May 19th 2015, but the internal mechanism of Microsoft Excel represents this says: 42,143.  This is the number of days from the current date to time zero, which is Saturday, December 31, 1899.  

Similarly, the time is 1:29:53 PM, but Excel’s internal mechanism represents this as 42,143.562422685200, which is days from time zero.  The decimal actually represents the time of day, and the 42,143, represents the date.

 

 

Microsoft Excel’s Functions for the Current Time and Date

 

Microsoft Excel has two functions (or formulas) for date and time, which are =TODAY() and =NOW().  The first formula =TODAY() can be used for obtaining the current date.  The, number for the date presented in the previous subsection, 42,143, was obtained with this function, and format code to convert the date to a number. 

     The =NOW() function can be used to obtain the current time and/or date.  The number for the time, presented in the previous subsection, 42,143.562422685200, was obtained with this formula, and related format code.

     The numbers, produced by =TODAY() and =NOW() can be mathematically manipulated.  For example, if you add one day to the first formula, as such: =TODAY()+1 you obtain a formula for tomorrow.  Similarly, a formula for yesterday can be created as follows: =TODAY()-1.  You can create a formula for a different time zone by adding or subtracting 1/24 of a day to =NOW().  For example, =NOW()+(3/24) will display a time that is three hours greater than the your local time.

     It is easy to make an error with the =NOW() function.  For example, =NOW()+3, would not change the time, but it would change the date.  This is because Microsoft Excel, only uses days and fractions of a day as units, for all date and time computations.  Thus, one hour must be represented as 1/24.

 

 

Excel’s Computer Code to Display Date and Time, In Conventional Formats

 

As explained above, Microsoft Excel calculates dates and time, in days, and fractions of a day, such as 42,143.562422685200.  Excel, and most programming languages use special code to convert date and time into units that humans can understand.  Microsoft Excel, has many code sequences that are used for this purpose, such as the following examples: (I am using following number of days from time zero all of the examples: 42,143.562422685200)

 

[$-F800]dddd, mmmm dd, yyyy

(Displays: Tuesday, May 19, 2015)

 

[$-409]mmmm d, yyyy;@

(Displays: May 19, 2015)

 

m/d/yyyy

(Displays: 5/19/2015)

 

[$-F400]h:mm:ss AM/PM

(Displays: 1:29:53 PM)

 

[$-409]h:mm AM/PM;@

(Display-example: 1:29 PM)

 

h:mm;@

(Display-example: 13:29)

 

I devised the following code sequences

 

h:mm:ss AM/PM, dddd, mmmm d, yyyy

(Displays date and time

1:29:53 PM, Tuesday, May 19, 2015)

 

h:mm:ss.000 AM/PM

(Displays time to 1000th of a second

1:29:53.320 PM)

 

 

Displaying Time and Dates in Terms of Days In Microsoft Excel, with Format Code

 

Any date or time that is displayed in Microsoft Excel can be displayed in terms of days, with any of the format codes listed below.  (I am using the following time and date for all of the examples: 9:49:24 AM, Wednesday, May 20, 2015)

 

#,##0.000000000000

(Displays 42,144.409302777800)

 

#,##0

(Displays 42144)

 

0.000

(Displays 42144.409)

 

0

(Displays 42144)

 

I devised the following code

#,##0.00000000000 “Days”

(Displays 42,144.40930277780 Days)

 

 

For Supporting Information, Alternative Perspectives, and Additional Information, from Other Authors, on the Material Discussed in this Section See the following Websites

 

1) Google Videos: (www.google.com/videohp) Microsoft Excel and see also Microsoft Excel date and time functions.

     2) YouTube Videos: (www.youtube.com) Microsoft Excel and see also Microsoft Excel date and time functions, and for SpreadsheetConverter see SpreadsheetConverter.

     3) MASHPEDIA Videos (www.mashpedia.com) Microsoft Excel and see also Microsoft Excel date and time functions,  and for SpreadsheetConverter see SpreadsheetConverterNOTE: Mashpedia has a large number of videos on each webpage.  To go from one webpage to another on Mashpedia, scroll to the BOTTOM of the webpage, and click on: NEXT >> 

     4) Microsoft Excel, Create a custom number format5) Number Formats in Microsoft Excel6) Microsoft Excel, Custom Number Format7) Microsoft Excel, Date & Time Functions8) Dates And Times In Excel9) Understanding Text & Numeric Formats | Microsoft Excel10) Custom Cell Formatting in Excel – Few Tips & Tricks11) How to control and understand settings in the Format Cells dialog box in Excel12) SpreadsheetConverter: Learn how to build live web pages from Excel spreadsheets13) SpreadsheetConverter Downloads14) SpreadsheetConverter: It's yours for free, for 30 days15) Homepage: www.SpreadsheetConverter.com

 

 

Mathcad, versus Microsoft Excel, and Other Mathematics Software

 

A Note for this Section

 

In this section, I briefly discuss Mathcad and some of its advantages, over other types of math software.  The material presented in this section is based on Mathcad 15.  If you want information on newer versions of Mathcad, or instructions on how to use it, see the web-based videos and articles at the end of the section.

 

 

What is Mathcad?

 

Mathcad is mathematics software primarily created for engineers, but it is very useful for studying and practicing almost any type of mathematics.  The main advantage of Mathcad over Microsoft Excel, and other mathematics software is Mathcad uses conventional math notation.  To use Mathcad, you do not have to translate mathematical expressions into a special programming language.  As a result of the above, documents created with Mathcad can be understood by anyone with a mathematical background.

 

 

Mathcad, versus Microsoft Excel, and Other Mathematics Software

 

To perform complex mathematics in Microsoft Excel, and many scientific and engineering mathematics programs, the user must learn how to translate mathematical expressions into a format that is unconventional.  For example, the following integral was solved with Mathcad, Microsoft Word’s Mathematics add-in, and by Microsoft Excel.

 

Solved with Mathcad

 

Solved with Microsoft mathematics add-in

 

     To solve the above problem with Microsoft Excel, you must translate the mathematical expression into the following format:

 

=(( 12)^2-(4)^2))/2

This format often results in errors, because it involves translating conventional mathematical expressions to an unconventional format.  It is easier to solve the problem presented above manually, then to use Microsoft Excel, such as:

 

Manual solution

 

     However, =(( 12)^2-(4)^2))/2 will of course provide the same calculated result as  when inserted in a cell in an Excel worksheet.  Nevertheless, the calculations will be quite confusing to most people, because of the unusual format required by Microsoft Excel, and many other mathematics software packages.

 

 

Using Excel, and Many Other Mathematics Software Packages, is More Time-Consuming than using Mathcad

 

With Microsoft Excel, it is necessary to clearly label, and sometimes explain, each mathematical calculation.  For example, if =(( 12)^2-(4)^2))/2 is inserted in an Excel worksheet, 64 will be displayed, without any notation indicating the nature of the calculation.  The most practical solution in this case, is to create graphic of , and place it next to the cell with the formula =((12)^2-(4)^2))/2.

     Carrying out complex mathematics with Microsoft Excel, and many other software packages, can be quite time-consuming because of the difficulties explained above.  However, with Mathcad, these difficulties are eliminated, and most complex calculations can be completed in a matter of seconds.

 

 

Mathcad vs. Microsoft’s Mathematics Add-in for Word

 

Microsoft’s Mathematics Add-in for Word, is just as efficient as Mathcad, because it also uses standard mathematical notation.  However, Mathcad can carry out complex calculations that cannot be done with Microsoft’s Mathematics Add-in, such as the following examples:

 

(Note, Mathcad 15 uses an arrow,

instead of an equal sign for

Symbolic calculations.)

 

 

 

 

 

 

 

 

 

 

     Another advantage of Mathcad is it can perform a sequence of calculations simultaneously, based on a single input.  See the following example:

 

 

The above, is essentially a calculation device, and it carries out nine calculations simultaneously, when a value is entered for Q:=.  A similar a device, can be created with Microsoft Excel, which may take up to one hour.  The Mathcad device presented above took about five minutes to create.  However, calculation devices created with Microsoft Excel, can be designed for high level of functionality and attractiveness.  The Excel devices can be converted to HTML–JavaScript, and used on the web as an online calculator.  This cannot be done with Mathcad. 

 

 

For Supporting Information, Alternative Perspectives, and Additional Information, from Other Authors, on the Material Discussed in this Section See the following Websites

 

1) Google Videos: (www.google.com/videohp) Mathcad, and see also Mathcad tutorial.

     2) YouTube Videos: (www.youtube.com) Mathcad, and see also Mathcad tutorial.

     3) MASHPEDIA Videos (www.mashpedia.com) Mathcad, and see also Mathcad tutorialNOTE: Mashpedia has a large number of videos on each webpage.  To go from one webpage to another on Mashpedia, scroll to the BOTTOM of the webpage, and click on: NEXT >> 

     4) All PTC Mathcad Tutorials5) Mathcad Tutorial by Prof. Bern Kohler, The Ohio State University6) An Introduction to Mathcad © by Sidney Young,  7) PTC Mathcad Express – Free Engineering Math Software8) Mathcad, From Wikipedia, the free encyclopedia9) PTC Mathcad community10) Videos: Mathcad Academic Channel11) MathCAD: Fundamental Instructions.,  12) Advantages of Mathcad for analysis

 

 

To go to the first page of this

chapter left click on these words

 

HYPERLINK TABLE OF CONTENTS

Below is the hyperlink table of contents of this chapter.  If you left click on a section, or subsection, it will appear on your computer screen.  Note the chapter heading, the yellow highlighted sections, and the blue subheadings are all active links.

 

Chapter 8) Illustrating and Writing Mathematics with Microsoft Excel, SpreadsheetConverter, and Mathcad. 1

To Access Additional Information with Hyperlinks  1

Microsoft Excel, SpreadsheetConverter, and Explaining and Demonstrating Mathematical Concepts  2

Note for this Section. 2

Microsoft Excel, and SpreadsheetConverter 2

A Demonstration Device, in the Form of a Software-Based Calendar, to Illustrate a Mathematical Concept, Involving Repetitive Cycles. 3

The Above Formulas, and Related Mathematical Concept 4

The Original Utility of the Above Formulas  6

Repetitive Cycles, for Dates, and Time, and Microsoft Excel 7

Determining DAY ONE, and DAY ZERO, with Microsoft Excel 7

The Units of Time Used by Microsoft Excel’s Internal Mechanism.. 8

Microsoft Excel’s Functions for the Current Time and Date. 8

Excel’s Computer Code to Display Date and Time, In Conventional Formats. 9

Displaying Time and Dates in Terms of Days In Microsoft Excel, with Format Code. 11

For Supporting Information, Alternative Perspectives, and Additional Information, from Other Authors, on the Material Discussed in this Section See the following Websites. 11

Mathcad, versus Microsoft Excel, and Other Mathematics Software. 12

A Note for this Section. 12

What is Mathcad?. 13

Mathcad, versus Microsoft Excel, and Other Mathematics Software. 13

Using Excel, and Many Other Mathematics Software Packages, is More Time-Consuming than using Mathcad  15

Mathcad vs. Microsoft’s Mathematics Add-in for Word  15

For Supporting Information, Alternative Perspectives, and Additional Information, from Other Authors, on the Material Discussed in this Section See the following Websites. 18

 

To go to the first page of this

chapter left click on these words

 

If you want to see a list of all

the chapters in this e-book go to

www.TechForText.com/Ma