The post Plot an Equation in Excel Using Python appeared first on My Online Training Hub.

]]>I know there are ways to do this that involve producing a set of data points, and then using those to create a chart, but I want to be able to just say, here's my equation, plot it.

I turned to Python to do this as it has the capability to plot equations quite easily. The issue was how do I get Python to run in Excel?

There are a number of packages that allow you to run Python in Excel: xlwings, PyXLL, and DataNitro are three options.

xlwings is free and open source.

DataNitro offers a 30 day free trial, but will keep working after this. If you purchase one of their membership options you get added benefits like ongoing support and updates, and the ability to write UDF's in Python.

PyXLL also offer a 30 day free trial, and then paid options after that.

For this, I chose to look at DataNitro, but will look at the others in the future.

Python is a powerful, easy to learn programming language - I've started helping my 11 year old son learn it.

It is used in myriad fields like web and internet development, databases access, scientific and numeric work, and software and game development. That is a very short list of examples of what it is capable of.

These days it is mentioned a lot in relation to data science and machine learning. Microsoft have thrown their support behind Python with a product called Machine Learning (ML) Server that supports both Python and R. ML Server is built in to SQL Server 2017.

Visual Studio also has great support for developing in Python.

But let's not get too far ahead of ourselves. Microsoft's adoption of Python (and R) is fantastic and I for one feel like a kid in a candy store.

Unfortunately the documentation for DataNitro is a bit out of date and I had a few issues installing and getting it to work.

When I tried to run some Python scripts it complained that the NumPy library was missing. NumPy is a software package for Python that includes mathematical and scientific functions.

I knew it wasn't missing as I had Python already installed on my PC with NumPy and other libraries included. However, reinstalling NumPy fixed the issue.

Once it was up and running I really liked the way the thing works. DataNitro installs as an add-in in Excel and will appear on your Ribbon.

To run a Python script you first need to import the script to Excel. From the Data Nitro area of the Ribbon, click on the import icon, locate your script and then click on Run to, uh, run it

Once you've imported the script you can click Run to run it again, no need to import it more than once.

What I really wanted to do was to have a Python script where I could just write out my equation, and have Python plot it.

Here's the Python code, you can see I've written y = 3*x**2 + 4*x + 2 for my equation.

In Python if you want to raise a number/variable to a power e.g. x^{2}, you write it as x**2.

Here's the graph this produces, which is inserted as an image in the worksheet called polynomial.

I can click on the Editor icon in the Ribbon which allows me to edit the Python code. Changing and saving the code allows me to run that changed code right away in Excel.

If I change my equation to 2*x**3 - 3*x**2 + 4*x + 2, I get this

To plot a different equation it's as simple as changing one line of code and re-running the Python script.

So if I wanted to plot Sin(x) my equation is simply y = np.sin(x) and I get this plot

You'll notice that the peaks and troughs of the curve are a little jagged. This is because I've only specified 50 points along the length of the curve between the values -10 and 10.

x = np.linspace(-10, 10, 50)

If I increase the number of points (to 150) then the curve will look smoother

x = np.linspace(-10, 10, 150)

which gives me this

Let's say we want to plot a sine wave that decays over time. We'll use this Python code

The code that describes the decaying sine wave is s = np.sin(np.pi*t)*np.exp(-t*0.05) which means sin(π*t) * e^{(-t*0.05)}

The curve we get is

We can write the values we're plotting to the Excel sheet with a couple of lines of code. To see the values for t we write Cell("P2").vertical = t and for s we write Cell("Q2").vertical = s.

This will list the values for t in column P starting at P2. The values for s will be in column Q starting at Q2.

The code looks like this

I've only scratched the surface of what you can do with Python in Excel.

A couple of things that I was looking at but didn't include in this post are, given a fixed set of points, interpolating a curve to fit that data. In this plot the data points are blue and the interpolated points are orange.

and finding the roots of polynomials.

The plots you see here haven't had any fancy formatting, labels etc applied to them. But this is all possible. If you've never used Python, or just used it a bit, I encourage you to explore what it's capable of.

Or if you are an old hand at Python, try linking it to Excel and see what you can do.

Either way, if there's anything you'd like me to look at, let me know.

All the Python code I have used has either come from the DataNitro examples, or from the matplotlib site.

I have modified them and you can download these modified files, and the workbook I used below.

Don't forget you'll need to install something like DataNitro to be able to run the Python code.

Enter your email address below to download the Python code and Excel workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

The post Plot an Equation in Excel Using Python appeared first on My Online Training Hub.

]]>The post Excel PivotTable Error Handling appeared first on My Online Training Hub.

]]>Thankfully Excel PivotTable error handling is easy to control via the PivotTable Options; right-click the PivotTable > PivotTable Options >

On the Layout & Format tab check the āFor error values showā:

Instead of the error you can show text, like āN/Aā, a number or leave it blank. I usually leave it blank, as you can see in the image below, because this is most likely to be suitable for all errors. Whereas if you put a value like 0 or 1, then that may not be correct in every scenario.

In the PivotTable below, column F; ā% Changeā is a āShow Values Asā calculation for ā% Difference Fromā¦ā, which is uses the following formula:

=(current month ā previous month) / previous month

When the āprevious monthā is zero we get the #DIV/0! error, and when the current month has no data we get a #NULL! error, as you can see below:

**#DIV/0! **errors are triggered by dividing by zero. Itās the most common PivotTable error and is typically found in calculated fields or calculated items, or a calculation from the Show Values As options.

**#NULL!, **errors are triggered when a formula references an item that is blank. In the example above, you can see the source data for October is an empty cell (B15). You can fix this by replacing blank cells with zeros...but then you might end up with #DIV/! errors

**Blanks** ā Notice Jan and Nov have blank results in the % Change column?

A blank is returned when the previous month is blank. And while this isnāt an error as such, some of you may prefer to see 100% or ā, or some other value in there. After all, if youāve gone from nothing to something (positive) then isnāt that an improvement?

Well yes, but that doesnāt mean you can represent the improvement in percentage terms.

From time to time someone will ask me how they can override these blanks and display 100% change when the prior period was zero.

The answer to this question is always, āIām not tellingā! Not because Iām being mean, but because it would be wrong to show the percentage change from zero as 100%, or even 0%.

I donāt just leave them hanging with āIām not tellingā, I told you Iām not mean. I go on to explain why, like so:

Letās say yesterday you had $0 and today you have $10. If you were to say that today you have 100% more $ than yesterday, then youād be saying that you still have $0 because $0 + (100% x $0) Ā = $0, when in reality you now have $10.

Whereas if yesterday you had $5 and today you have $10, then you can say you have 100% more $ today because $5 + (100% x $5) = $10.

In other words, you can only calculate the percentage change from āsomethingā, and zero is nothing. i.e. something is a number other than zero.

So, the accepted answer when calculating the change from zero is ānot meaningfulā or leave it blank.

And before you think you can sneak 100% in the āFor empty cells showā field in the PivotTable Options, Iām happy to say that wonāt work for the % Difference From empty fields.

Forward this tutorial to those you know who insist the percentage change from nothing to something is 100%?

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

If you liked this please click the buttons below to share.

The post Excel PivotTable Error Handling appeared first on My Online Training Hub.

]]>The post Excel PivotTable Percentage Change appeared first on My Online Training Hub.

]]>The Excel PivotTable Percentage Change calculation is achieved with the % Difference From option and is useful for quickly identifying if this month/quarter/year is better or worse than last month/quarter/year.

Add in some Conditional Formatting to your PivotTable, and throw in a Slicer and weāve got a super quick, visually appealing, interactive report at the click of a few buttons.

Letās look at how to build the month on month percentage change PivotTable above.

**Step 1**: Start with a regular PivotTable, and add the field you want the percentage change calculation based on, to the values area twice:

**Step 2**: Right-click any values cell in the Sum of Sales2 column > select Show Values As > % Difference Fromā¦:

**Note to Excel 2007 users: **The Show Values As options are in the Value Field Settings dialog box:

**Tip**: You donāt need the Sales field in the Values area twice to show the % Difference From. If you only want to show the percentage change and not the actual Sales amounts, then you can simply add the āSalesā field to the Values area once and then set that field to % Difference From.. via the āShow values asā¦ā menu.

**Step 3**: In the Show Values As dialog box set the Base field to Month and the Base item to (previous):

Your PivotTable should now look like this:

**Tip**: Give the āSum of Sales2ā field a better name. Simply type a new name in cell C3, making sure itās not the same as any of the field names in your PivotTable source data. Iāll just call mine %. Youāll see why in a moment:

We can make the % change percentages easier to read with some Conditional Formatting visual indicators. I like to place these in a separate column, but if youāre happy for them to share column C then you can skip steps 4 and 5.

**Step 4**: For this weāll need to add the āSalesā field to the Values area again:

**Step 5**: Right-click the Sum of Sales2 column > Show Values As > % Difference From, and then same as before; Base field is Month and Base item is Previous.

Also give the column a new name. Iāll call mine āChangeā, as you can see below:

**Step 6**: With any cell in the āChangeā column values area selected, go to the Home tab > Conditional Formatting > Icon sets. Here you can choose from different icons, but Iāll stick with the directional triangles:

This will apply the formatting to the selected cell.

It should now look like this:

Notice that there are some neutral/yellow icons. We want to change the formatting to simply show green up triangles for positive change and red down triangles for negative change.

**Step 8:** With any cell in the āChangeā column selected, go to the Home tab > Conditional Formatting > Manage Rules. This opens the Conditional Formatting Rule Manager dialog box (shown below). Select the icon set rule and click āEdit Ruleā.

**Tip**: You can also double click the rule to open the rule editor window, shown below:

Edit the settings as shown in the image below. Note: Iām choosing to only show the icon in this column because column C already has the percentages displayed.

Your PivotTable should now look like this:

**Tip:** Iāve centered the Conditional Formatting icons using the cell alignment on the Home tab.

For the icing on top, add a Slicer and allow your user to interact with the PivotTable, as Iāve done for the Category field:

**Tip:** Just in case some months donāt have any data, Iāve set my Month Field settings to āshow values with no dataā to ensure all months are listed:

**Note:** Months with no data will result in a #NULL! error for the % Difference From calculation. Itās not an issue here, but something to be aware of if you see #NULL! errors in your PivotTables.

Now, obviously we donāt want our PivotTables littered with errors, especially if weāre presenting them in a report, that would just create unnecessary questions and weāre busy enough.

Thankfully we can supress errors in the PivotTable options; right-click the PivotTable > PivotTable Options > on the Layout & Format tab check the āFor error values showā:

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

If you liked this please click the buttons below to share.

The post Excel PivotTable Percentage Change appeared first on My Online Training Hub.

]]>The post Excel 4.0 Macro Functions appeared first on My Online Training Hub.

]]>Even though these XLM macros are quite old they still work in Excel 2016. To use them you call them like a regular function e.g. =GET.CELL(64,A1) but entering this into a cell will give you an error.

What you need to do is combine them with defined names and then you can do some cool stuff.

You can do things that might normally require writing VBA, so if you don't fancy doing any coding check these out. Listing files in a folder, highlighting cells containing formulas, or getting the background color (ColorIndex) of a cell. Once you know a cell's background color, you could then do math with cells of the same color like sum, average etc.

Note: the last link to Jan Karel Pieterse site has other examples too.

The trouble I had was that I couldn't easily find any reference to the Excel 4 macros. Because they were written for such an old version of Excel, the help file for the macros is no longer a supported format as of Windows 10.

Even if you did have a Windows 7 or 8 PC you'd have to download an executable file from Microsoft, install this, then update Windows to allow you to open the help file. I know because I built a Windows 7 virtual machine on Azure and did all of this. It was just too hard.

So I decided to put together this 653 page reference eBook. It basically contains the official Microsoft Excel 4 macro functions help file, but in a useful PDF format.

A complete reference for all Excel 4 macros including syntax and examples.

Enter your email address below to download this free PDF.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

You may not find all of these macros will be useful, but some certainly are.

This is provided for reference and if you are curious to see what you can do.

Don't go building mission critical models with these, support for them could be withdrawn in the future.

The post Excel 4.0 Macro Functions appeared first on My Online Training Hub.

]]>The post Extract Date or Time from a Date and Time appeared first on My Online Training Hub.

]]>We can use the INT function to extract the date portion, and the MOD function to extract the time portion.

See examples below.

The INT function returns the integer or whole number portion of the date time serial number. You can also use the TRUNC function with the same results, but that requires two more characters and we busy Excel users don't have time for that!

The Excel MOD function allows us to retain the decimal or time portion of the date-time serial number.

The syntax for MOD is:

=MOD(number, divisor)

Your date time serial number is the 'number' argument and the divisor is 1. This returns the decimal or time portion of the date-time serial number

Easy!

Learn more about how Excel handles dates and time in our comprehensive guide to working with Excel Date and Time, or download the files below.

Everything you need to know about Date and Time in Excel - Download the free eBook and Excel file with detailed instructions.

Enter your email address below to download the comprehensive Excel workbook and PDF.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

Download the Excel Workbook and PDF. Note: This is a zip file including an Excel workbook with detailed instructions and a PDF version for your reference.

The post Extract Date or Time from a Date and Time appeared first on My Online Training Hub.

]]>The post Excel MOD Function appeared first on My Online Training Hub.

]]>Anyhow, just because I find MOD confusing, doesnāt mean itās not one of the most useful and versatile functions available to us. And to be fair, itās not a complicated function, but the description of what the MOD function does is as clear as mud, just take a look:

āMOD returns the remainder after a number is divided by a divisor.ā

Unless youāve heard of MOD before, it might as well read; blah, blah, blah, blah.

=MOD(number, divisor)

** Number** ā the number to be divided

** Divisor** ā the number to divide by

Letās look at some MOD Function examples and youāll (hopefully) see itās actually quite simple:

Iāll explain the way I understand what MOD does. Letās take the examples above, starting with row 5:

**Row 5**: 3 goes into 9, 3 times. There is nothing left, so MOD returns 0. In other words, 9 is divisible by 3. I can calculate the result of MOD like this:- 9 - (3 x 3) = 0

**Row 6**: 3 goes into 10, 3 times. There is 1 left. In other words, 10 is not divisible by 3. I can calculate the result of MOD like this:- 10 - (3 x 3) = 1

**Row 7**: 4 goes into 6, 1 time. There are 2 left. In other words, 6 is not divisible by 4. I can calculate the result of MOD like this:- 6 - (4 x 1) = 2

Good Things to Know about MOD:

- The result of MOD will always match sign of the divisor. Compare examples on rows 7:10.
- Using 2 as the divisor will always return 1 for odd ānumbersā and 0 for even ānumbersā. See examples on rows 11:14. We can exploit this pattern and use it in formulas that use Boolean TRUE/FALSE arguments. For example, Conditional Formatting.
- We can use MOD with 1 as the divisor to return the decimal portion of a number. See row 15. This is handy for extracting the time from a date-time serial number.
- If the divisor is 0, MOD will return a #DIV/0! error.

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

If you liked this please click the buttons below to share.

The post Excel MOD Function appeared first on My Online Training Hub.

]]>The post Excel DATE Function appeared first on My Online Training Hub.

]]>Syntax: |
=DATE(year, month, day) |

year |
The value of the argument can include one to four digits from 1900 to 9999.year |

month |
A positive or negative integer representing the month of the year from 1 to 12 (January to December). |

day |
A positive or negative integer representing the day of the month from 1 to 31. |

The example below illustrates how you can gather day, month and year components from separate cells and use the DATE function to return a date serial number:

**Tip: **We can use the DATE function to add or subtract months or years to a date. See below.

**Tip:** We can also use the EDATE function to roll dates forward by a set number of months.

**Note: **If we add days and they exceed the number of days in the month, the DATE function will add the excess days to the start of the following month. See examples below.

**Tip 1: **The Year argument must be a positive value, but month and day can be negative! If ** month** is greater than 12,

**Tip 2: **If ** month** is less than 1,

Learn more about how Excel handles dates and time in our comprehensive guide to working with Excel Date and Time, or download the files below.

Everything you need to know about Date and Time in Excel - Download the free eBook and Excel file with detailed instructions.

Enter your email address below to download the comprehensive Excel workbook and PDF.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

The post Excel DATE Function appeared first on My Online Training Hub.

]]>The post Convert Month Names to Numbers appeared first on My Online Training Hub.

]]>We can use the MONTH function to do this; see examples below:

**How it works: **By concatenating a 1 to the text month name in column B we provide the MONTH function with a text date it can recognise/use. We can see how the formula in cell C7 evaluates in the Evaluate Formula dialog box below:

We can exploit this use of the MONTH function to create a date serial number (shown in column C below). You can then format them as a date (shown in column D below), using Format Cells.

**Note: **The formula in column C converts the month name in column B to the first day of each month in the current year. You can replace the 'YEAR(TODAY())' part of the formula with a different year, if you prefer.

**Tip**: You can also convert abbreviated month names to numbers or dates:

Learn more about how Excel handles dates and time in our comprehensive guide to working with Excel Date and Time, or download the files below.

Everything you need to know about Date and Time in Excel - Download the free eBook and Excel file with detailed instructions.

Enter your email address below to download the comprehensive Excel workbook and PDF.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

The post Convert Month Names to Numbers appeared first on My Online Training Hub.

]]>The post Convert Dates to Text appeared first on My Online Training Hub.

]]>We can use the TEXT function for this. The second argument of the text function allows us to specify the date format we want applied.

Syntax: |
=TEXT(value, format_text) |

value |
This is typically a reference to a cell containing the date serial number, but could also be a DATE formula. |

format_text |
This is the date number format you want to apply (surrounded by double quotes). |

**Tip**: You can use the date number formats to construct the date's appearance in many different ways. You can also concatenate additional text using the & symbol, as shown in C19 and C20 in the image below.

**Note 1**: my dates are formatted dd/mm/yyyy, but if you format dates mm/dd/yyyy, then you can simply change the format in the format_text argument of the TEXT formula.

**Note 2**: the TEXT function converts the dates to text, obviously! But bear in mind that this means you can't use the values in column C in any math formulas, or many of the Date functions.

Custom formatting isn't limited to dates and times. There is a plethora of formatting options for all types of numbers that we can use to get our reports looking just the way we want. Click here for our comprehensive guide to Excel custom number formatting.

Now that you know how to convert dates to text, why not learn how to use them in dynamic text labels in your reports and charts.

Enter your email address below to download the comprehensive Excel workbook and PDF.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

The post Convert Dates to Text appeared first on My Online Training Hub.

]]>The post Fix Excel Dates Formatted as Text appeared first on My Online Training Hub.

]]>However, it can be tricky to tell if a date is text, because unlike regular values, a date will often appear the same in the formula bar as it does on the face of the cell, as you can see below:

Don't worry, I've got several never fail, dead easy ways to tell if dates really are date serial numbers, or if they're actually text masquerading as dates.

A quick test to check if dates are text is to select more than one cell containing your dates, then glance down to the status bar to see if you get a Sum result. The status bar is in the bottom right of the Excel window:

That's right, the dates that are correctly entered as date serial numbers will display the SUM result in the status bar when you select **more than one cell **containing a date. Whereas text will only display the count.

Another quick test is to change the cell format to General.

If your date format displays the date serial number (as shown in the second list of dates to the right) you're good to go, but if it still displays a date (as shown in the first list of dates on the right), then you've got yourself some text dates that need fixing.

It's not as simple as just changing the cell format to 'Date'. Those text dates are stubborn. We'll get on to fixing dates formatted as text soon.

Widen the column; any dates that are text will align to the left, assuming no other alignment formatting has been applied to the cells.

More recent versions of Excel are good at detecting dates incorrectly entered as text and tagging them with a warning notification. Hovering your mouse over the warning tells you the date is entered as text:

Clicking on the warning reveals a list of options for fixing the date, or ignoring the error, as you can see in the image below:

**Tip:** if you have a lot of dates to fix, or a lot of formulas referencing these dates then converting them to date serial numbers with this method could be slow. Option 2 may be quicker.

In cases where the warning doesn't display, as well as in earlier versions of Excel, we need to resort to other methods of fixing dates formatted as text. Let's take a look.

- In an empty cell enter 1 and copy the cell to the clipboard
- Select the cells containing the text dates you want to convert to date serial numbers.
- Home tab > Paste >
- Paste Special

In the Paste Special dialog box select:

- Values

- Multiply (or Divide)

**Tip**: Paste Special > Values shortcut keys:

ALT > E > S > V > Enter or CTRL+ALT+V > V > Enter

**Alternate approach:** You can also use Paste Special with 'Add'.

Simply copy an __empty__ cell > Select your date cells > Paste Special > Values & Add!

No need to go back and delete the '1'.

These methods will return a list of date serial numbers which you can then apply a date number format to.

**Tip:** on long lists with lots of formulas referencing your dates this method is often quicker than the error checking method in option 1.

If your text dates aren't formatted based on the standard structure for your region e.g. dd/mm/yyyy or mm/dd/yyyy, then a great tool to use is Text to Columns, because this allows you to specify the order of the date characters ensuring that they are converted to date serial numbers correctly.

- Select the cells containing your dates
- Data tab
- Text to Columns
- Delimited
- Next

In step 2 of the wizard simply click Next.

In step 3 of the wizard you can select the order of the date data from the drop down:

**Note**: this is the format of the text you're converting, not your final desired format.

Your final desired format can be applied with custom number formatting.

The DATEVALUE function takes a date text string and converts it to a date serial number, as you can see in this example:

**Tip: **You'll probably want to convert those DATEVALUE formulas in column C to values with Paste Special > Values and get rid of the text dates in column B. No need to have them cluttering up your spreadsheet.

Need more ideas on how to fix Excel Dates formatted as text? Check out this post: 6 Ways to Fix Dates Formatted as Text in Excel

Now that you know how to fix Excel dates formatted as text you'll want to format them, and maybe even use a custom date or time format. Here's a post with everything you need to know about formatting Excel dates and time.

Enter your email address below to download the comprehensive Excel workbook and PDF.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

Working with dates and time are one of the most common struggles for Excel users, so do your friends and colleagues a favour and share this post with them on LinkedIn, Google+, Facebook and Twitter.

The post Fix Excel Dates Formatted as Text appeared first on My Online Training Hub.

]]>