Excel SLOPE Function and Scatter Plot for a Pricing Formula

/in /by

This video takes us back to my very first paid project in Excel. A photographer had a problem calculating the price of a finished and framed photo. The main part of the problem was the mystery around the price of the frame.

We gathered receipts and I figured, “what the heck, let’s plot the known data on a grid.”
The Excel scatter plot showed a straight line. That made it easy to figure out the frame shop’s pricing formula.

The equation of a straight line: y=mx+b

 
y = final price
m = slope * length * width + b
EASY!!!

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

IDEAS in Excel: Be wary of these weaknesses

/in /by

Excel has a feature called Ideas. It offers to provide information about the user’s dataset, and accepts natural language questions. This video shows areas where you need to watch out or you can get into deep trouble.

I’m skeptical of AI/ML (artificial intelligence and machine learning). Why? Because the hype and marketing around this technology talks about the promises and what’s possible. But this video shows several examples where Ideas (using AI/ML) makes goofs.

Ideas–like any other AI/ML application–makes guesses. Some guesses are much better than others. Some guesses are way off the mark and if we have to be extra careful about results that we didn’t generate ourselves.

Yes, AI/ML can be helpful. But it puts more responsibility on the user to understand and verify everything around their data.

#ExcelIdeas
#Ideas
#ArtificialIntelligence

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Power Query: Import from PDF (2 Examples)

/in /by

One thing has been torture for those of us who work with data: breaking data out of PDF files!

Just recently, Power Query got a new feature: import from PDF. In this video I show 2 examples of importing a PDF into Power Query and also show the old methods of a plain old copy-paste and “open with Microsoft Word.”

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Managing Accuracy and Errant Entries with SUMIFS and Conditional Formatting

/in /by

This video pulls together a number of Excel features in order to get an accurate inventory number.

This is based on a real life situation where an organizations data was messy because they weren’t adding numbers properly or managing errant entries.

This video shows how to use SUMIFS, a dropdown list and Excel’s Conditional Formatting to ensure that inputs and outputs are accurate.

#SUMIFS
#ExcelErrors
#ConditionalFormatting

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Excel COUNTIFS function with OR Criteria

/in /by

#COUNTIFS
#CountMultipleCriteria
#COUNTIFSfunction

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Power Query: Split Multiple Columns into Rows All At Once

/in /by

It’s easy to split columns in Power Query and split into rows. But here’s a situation where there’s data in 3 columns that will fly apart and get unmatched if we split one column without splitting the other 2 at the same time.

One solution would be to split the source data into 3 pieces, split the columns into rows, add index columns, then use outer joins to piece the data back together.

We’re not going to do that today!We’re going to use Power Query’s lists, and 2 functions:
Text.Split
Table.FromColumns

These are going to make for a smooth solution!

#SplitMultipleColumns
#PowerQuery
#Text.Split

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Power Query: Combine rows into a single cell with Text.Combine

/in /by

A question came in from someone who wanted to know how to merge rows of data into a single cell.

Imagine you have a person’s name in one column and then details about the person on multiple rows; and then another name and details about that person; and so on …

Now you’d like to merge each person’s details so that each person and their respective details are on a single row with their details in a single cell/column.

To do this, I show Power Query and the Text.Combine function.

This video also demonstrates my example of building a small model so that we can focus on the solution and not a massive amount of our real data.

#Text.Combine

#PowerQuery
#Grouping

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Excel’s LET function in 3 Acts

/in /by

One of Excel’s newest functions: LET()
Incredible! This function allows the Excel user to create complex formulas that are easier to create and easier to read. But they do require the user to think several steps ahead.

In this video I show 3 examples and make 2 cases in favor of using LET:
– Very basic
– Recycle. This is when we have to do a complex calculation and then do it again later in the formula.
– Clarity. Here, when a complex formula is needed, it’s easier to set up the variables first and then do the final calculation using only the variables. This can be easier to real in several distinct chunks rather than a massive jungle of parentheses or use of helper columns.

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Try … Otherwise: Power Query’s version of Excel’s IFERROR

/in /by

Thank to my friend Gráinne who showed me this in Power Query:

Try … Otherwise

In native Excel we have the IFERROR function for when we anticipate errors and know what we want to do with them. In this case, we want to add the Shipped and Inventory amounts. But, if someone has put a note in the Inventory column–instead of an error–we want to return the Shipped amount. Pretty easy to do with Try … Otherwise

There’s also a warning: Power Query does not treat 0, null and [blank] as the same things.

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2