Getting Color Right in Products

Color is a feature of many products’ branding and benefits. Here I discuss how the “easy way” is not preferred for tolerancing color –that is quantifying  how close one measured color is to another either for quality control or for quantifying cleaning product efficacy. To do this appropriately in clients’ applications, we use a validated calculation template, and its formulas adapt readily to scripts in data pipelines. The template generates both ΔE* and CMC ΔE* color difference statistics –with the latter recommended for its superior correlation to how humans perceive color differences. Note that officially, the difference measures have a “*” in their names, but we will omit that for simplicity here.

Color Basics and the Easy (But Not Preferred) Way to Quantify Differences

Color is typically quantified by three numbers that can be measured by lab instruments or online sensors. The three sub-measures are L* (Lightness/darkness), a* (so-called red-to-green), and b* (yellow-to-blue). They locate a set of measurements in 3-D space as diagrammed below. High L-color of 100 is white and low L-color of zero is black. All points in between on the L-axis (e.g. a* and b* = 0) are a shade of gray ranging from very light to very dark.

The a and b rectangular coordinates are also commonly converted to polar coordinates --L,C,H space. Here the radius length is referred to as Chroma, C. It reflects the intensity of the color starting as a shade of gray at the a-b origin and increasing in visual intensity as C increases. The hue angle from 0 to 360 degrees, h, quantifies the color’s shade from red to green to blue to purple in a continuous scale while rotating around the a-b origin. The meaning of hue can be understood from the diagram. A hue angle of 45 degrees is a shade of red (e.g. the bold arrow on the diagram is at 45 degrees). Similarly, a hue angle of 135 degrees is green; 225 degrees is blue and so on.

Wikimedia Commons CIELAB chroma.svg

For quantifying color differences, it is neither convenient nor consumer relevant to individually control L, an and b (or L, C and h). Who wants to simultaneously track three numbers on a specification when one will do? The easy and historically-used method is to compute the ΔE color difference whose equation is shown below. It is the Euclidean distance between a color and its target/standard. The circle in the plot shows constant ΔE around a target in the a-b plane if L* is fixed. In 3-D space, these boundaries are spheres. ΔE is easy to calculate, but it doesn’t conform to how humans perceive color differences. We discuss CMC ΔE next. It was developed to overcome the shortcomings of ΔE, and the graph shows a CMC ΔE tolerance boundary for comparison. Note that the absolute limits are not comparable between the two systems. To display similar-sized regions, we chose 4.0 for one and 7.0 for the other.

CMC ΔE: Quantifying Color Differences in a Consumer-Relevant Way

Consumer relevance comes from switching to using the CMC ΔE statistic. Its tolerances are stretched and resized to correspond to human perception. This makes CMC DE boundaries elliptical in the a-b plane and in the out-of-plane L* direction --making them ellipsoids in three dimensions. They are oriented along lines of constant hue angle and are stretched in the Chroma direction as shown in the graph above.

The elliptical shape accounts for the fact that humans don’t perceive Chroma differences as readily as they do L* or especially hue differences. Adding to the complexity, human perception also depends on proximity to the a-b origin and to black (low L*) or white (high L*) in the third dimension. At least this latter point makes sense qualitatively. It’s difficult to tell what color an object is in a dark room.

The graph below shows color tiles generated to illustrate differences. These were generated by the excellent, colorizer.org website and have the same CMC ΔE of 4.0 versus the target. The differences are subtle at this level (and possibly especially viewed on a computer monitor). At this hue angle of 21 degrees (e.g. red), the +h* sample is slightly yellower and -h* is a bit more purplish. The -C* tile tends towards grayish versus +C*, and +L* and -L* tend towards lighter and darker respectively. The latter difference is made especially noticeable by placing those two tiles side-by-side. Acceptability would depend on the application.

The formula for CMC ΔE (See the Wikipedia Color Science article or ASTM standard D2244-16) is understandably complex based on the above discussion, but it is straightforward in Excel (below equation) or in data pipeline scripts. A previous Tech Notes blog discusses some intricacies of h calculation. To safeguard calculations, our template stores the CMC ΔE and other formulas in a “refresh from” recipe sheet that keeps things updated and correct as data are entered and analyzed.

CMC ΔE Excel Formula

A rule of thumb from literature and personal experience is that, for non-textured/smooth samples in the stringent setting of well-lit, side-by-side testing, a CMC ΔE of 0.75 is the threshold where differences become perceptible with statistical significance in a consumer panel. Under these conditions, a CMC ΔE of 2.0 is clearly noticeable leading to comments like, “That one looks a bit yellower” or “That one is the same color but somewhat duller.”  If the product is more textured or if the lighting is not perfect, it takes a bigger difference to be noticeable.

For a given product context, CMC ΔE limits can be validated by consumer panel protocols such as triangle testing where products (for example makeup foundation shades, paints or molded plastics) are generated that vary CMC ΔE. In such a panel, panelists are asked to choose which of three products are different from the other two.

Some situations are harder to test because of the difficulty of generating samples having a specified difference. Tolerances for liquid products can often be set by conducting design-of-experiment (DOE) panel testing. This gets into what would be a separate blog topic about color formulation, but liquid products of a specified color difference can be made in test quantities using a mixing model that calculates the amounts to blend of mono-pigment versions of the product.

A watchout in panel testing is ensuring that the test design is appropriate for the in-market context. If the product is textured/matte finish or consumers are unlikely to experience true “side-by-side” or “well-lit” conditions, a higher tolerance is appropriate. In the case of cleaning efficacy, the goal is to bring the substrate back to its original color –imperceptibly different from the pristine material. This might lead to a side-by-side context in market if the consumer will see both cleaned and uncleaned zones.

If you are not yet using CMC ΔE as your color difference statistic, you should be! Please reach out if we can help!


Well-Behaved Color Data Calculations

Color tolerancing is critical for quality control on colored products and for applications like assessing efficacy of cleaning products. We use a standard Excel data entry template (subject of an upcoming post) that can calculate the CMC Delta-E statistic to quantify the color difference between two samples in a way that correlates to human perceptibility (see the formula here under the "CMC l:c (1984)" heading or see ASTM D2244-16). This blog covers details of calculating needed hue angles from CIELAB (aka L,a,b color) data as commonly measured by lab instruments. It is an illustration of how good models go to lengths to get calculation details right even when that is not easy. The graph’s second set of datapoint labels shows the needed 0 to 360 degree hue angles for measurements mocked up to all have a Chroma (e.g. radius) equal to 10. The data file is ATAN_Check.xlsx in this Github repository. These calculations serve as our validation of this portion of the CMC ΔE template calculations --validation being a key characteristic of best-in-class models

Starting with CIELAB data necessitates converting between rectangular x, y (a-color and b-color) and polar r, Θ (e.g. Chroma, C, and Hue angle, h) coordinates. No problem, right? An initial, (and stay tuned, incorrect), thought is that a data entry template could utilize Excel’s built-in ATAN function to compute h as arctangent of b/a –with the color science reminder that a-color is red-to-green and b-color is blue-to-yellow and, when converted to polar coordinates, the radius or Chroma, C, is the intensity of the color and h is the hue which describes whether the color is red, green, yellow and so on.

Per the ATAN column (Column C) below, that function only returns results as first and fourth quadrant angles from -90 to +90 degrees and with #DIV/0! unpleasantness at the points where a-color is zero. We don’t get the required 0 to 360 degree hue angles. (If you are unfamiliar with Excel’s “@” notation in these formulas, see the note at the end).

We get past this situation by using Excel’s ATAN2 function. Historically, ATAN2 probably represents Microsoft recognizing ATAN’s limitations and (almost...keep reading) correcting them. The ATAN2 column (Column D )and the first labels on the graph’s datapoints shows ATAN2 calculations for our test dataset.

ATAN2 results range from -180 to +180 degrees with no errors when a-color (aka x axis value) is zero. That’s better, but still not 0 to 360 degrees. For that, we add a conditional correction in the ATAN2_mod (Column E) values that converts negative angles to positive. These are the second set of labels on the graph datapoints. This modified ATAN2 formula solves the problem of getting 0 to 360 degree hue angles for color tolerancing.

Note on Excel “@” Notation for Referring to Named Ranges

Our mockup file’s formulas use the modeling best practice of naming columns A and B “a_meas” and “b_meas”, respectively and using what Microsoft calls “implicit intersection” with the “@” symbol preceding the names in the formula. The “@” says to return the row’s value from the named column, and this is a best practice for creating easy-to-understand formulas (e.g. compare symbolic “@b_meas/@a_meas” here to classic cell reference syntax like “B4/A4”).  The first is easy to understand as the ratio of b:a colors. The second is more cryptic. We teach these best practices and more in our Digital Transformation with Excel online course.


Data And Models that Behave Like A Considerate Human

Can a data table or calculation model be designed to be either courteous or rude? They certainly can! Designing for courteous and considerate is a guiding principle for enhancing usefulness of these products –helping customers get insights from their data whether these users are expert or non-expert.

My long-time guiding principle: Data, models and dashboards should behave as would a considerate human. I notice this remains fresh and intuitive based on people immediately nodding their heads in agreement. This is true 20+ years after Cooper et al. published their “designing considerate software” inspiration as a chapter in their renowned book now in its 4th edition, About Face: The Essentials of Interaction Design. Quoting from the 2nd Edition (2003) chapter’s opening paragraph:

If we want users to like our software, we should design it to behave in the same manner as a likeable person. If we want users to be productive with our software, we should design it to behave like a supportive human colleague.

This is sage advice. Cooper et al.’s teaching anticipates current AI software that starts to closely resemble “supportive human colleagues.” That’s a hot topic, but I will focus here on user interface principles for great data products and models that encourage teams to get insights – leading to being able to scale these models and use the fruit from that to grow their business.

A warning at the onset: You can’t unsee this. Software is everywhere. Learning to look through the “considerate design” lens can turn you into an instant critic of the rude little corners of even luxury products’ software:

“My car’s software is being inconsiderate and not deferring to my needs again today. I instructed it yesterday and the day before that I want to use Bluetooth for audio and Apple Carplay for navigation. Why is it blaring FM radio from the speakers and making me thumb through its screens to re-enable Carplay while I’m trying to back it out of the garage?”

Me to anyone sitting in the car with me when I back out of the garage

Cooper et al. share 13 great characteristics. Examples include that considerate software should be deferential meaning it puts your needs first. Considerate software doesn’t burden you with its personal problems. It fails gracefully.

In our context, “deferential” could mean that, if we are designing a cost model that will be used by the General Manager or Owner, he or she should see the overall numbers first. The model should arrive with the detail columns collapsed while still allowing the Finance or Product Design teams to expand things to work with and graph the details. Many “considerate data” and data usability discussions center on how a user can instantly get the data they want in view and get it analyzed starting from a bigger whole –without unnecessary distractions and without being tempted to break the whole set into smaller pieces that limit doing big-picture analysis.

That’s the high-level flavor of the topic. Read on for some additional motivations and examples. Of course, please reach out if we can make your spreadsheets, dashboards and models more courteous. This is a core, guiding principle for solutions we design for clients. The examples below are based on our online course, Digital Transformation with Excel where we teach an extensive module on how to use Excel's features to build considerate spreadsheet models.

Additional Considerate Design Motivation and Examples

Cooper et al. make the software case. That’s great but, for data products and models, users often have additional options for heading down a false path versus for the software design topic where they either enjoy using the software or they don’t. Inconsiderate data designs often cause users to avoid using the data (missed insights!) or to descend into bad habits of creating their own “bad spreadsheets” to try to make sense of things. This leads to missed insights and missed chances to scale and connect with adjacent data.

The following sections relate a few of Cooper’s principles to data and models --using content from the Digital Transformation with Excel course. We apply similar thinking to building models in coded languages like Python.

Considerate data and models keep us informed.

Cooper cites the need for software to avoid “pestering us incessantly about its little fears and triumphs,” but we do want it to inform about important updates and status. I extend this to say that data and models should intuitively inform us about data source and state. In spreadsheets, an example is using Excel’s built-in Calculation style (orange bold text below) for columns containing a consistent formula in all rows. The formatting makes clear that the only [visible] inputs in black text are the Tier, Initiative and Size columns B, C and D. There might be additional input columns lurking in the collapsed sections though.

Keeping the user informed is closely related to doing a good job “curating.” That is an important digital transformation topic too. We strongly encourage use of cell notes in header cells (Row 1) to describe variables and give units. In Python models, this is a reason to have multiple sets of columns in DataFrames –one for the variable names and one for the description and units.  We often use automation (an Excel add-in or Python library) to apply and update these comments from a metadata table describing the variables. While not part of keeping the user informed, the model below also illustrates use of macro-refreshable conditional formatting in Columns A to C to avoid the visual distraction of repeated values. This staves off users’ urge to merge cells which would break the table’s structure and eliminate being able to graph the data and to filter and sort the rows.

An Informative Cost Model

Informed

To keep users dynamically informed, we use our home grown Excel macro (VBA) user messaging system to issue informative update and error messages. I recently ported this to Python for use with a client’s model that needed to do extensive pre-checks on inputs for problems before running a model. If all goes well, there are no messages, but we included 100+ potential messages for describing problems and how to correct them.

 

Considerate Models Fail Gracefully

It's sad but true. Models may not always work when run or recalculated. Sometimes this is due to the user making inappropriate inputs, but sometimes unexpected errors occur. “Gracefully” means that, in these situations, the model does its best to help you pick up the pieces and fix the problem. Showing mysterious gobbledygook to the user is not graceful!

Non-Graceful Failures in an Excel VBA Macro and a Python Model

A quick illustration of failing gracefully with spreadsheet models is that lookup formulas should include a message to explain the problem if a lookup fails --something that can easily happen if the lookup table doesn't contain an expected value from the master table doing the lookup.  The formulas below show examples of equipping Excel formulas to fail gracefully and avoid the dreaded and difficult-to-understand “N/A” result. Are you not yet familiar with XLOOKUP options and the IFERROR function? Not learning them now would be...inconsiderate.

Excel IFERROR to Advise Correcting Inappropriate Input for Square Root Function

 

Excel XLOOKUP Function's if_not_found Argument

 

For dynamic errors in coded models, our error handling module allows VBA models to flag specific, problematic cells as in the example below. The “Model contains an error…” dialog alerts the user that there is a problem (so they don't miss this important fact!). The cell comment describes specifically what’s wrong and how to fix a problem like the typo shown.

   

 

Considerate Models are Forthcoming

For a data product, the basic task of scrolling through data is a great illustration of “forthcoming.” Scrolling is like breathing, right? But equipping models for this to occur in a considerate way is A BIG DEAL. The potential learning from a model or data goes up if it can be scaled and not broken up into little silos that are hard to cross-compare and graph. It is an immediate discouragement if a user ends up looking at a sea (or even a small lake!) of unidentified rows and columns of numbers like this example. Do you remember what Column F is? You shouldn’t need to!!

What am I looking at????

While the above scroll view is rude, the model can be made forthcoming by using Excel's freeze panes features to anchor the view in the Row 1 column names and “key” columns at the left edge of the data. This makes clear what’s in whatever pane of data is in view. As a bonus for anchoring the view, the key columns at left can be used to filter rows to a desired subset. In consulting projects, When a model outputs data to Excel from another source, we use automation to apply these splits and other formatting –not forcing the user to need to know to do it themselves.

A Forthcoming Model's Scrollable View

 

These illustrations of considerate software principles give a flavor for how to bring user-interface design excellence to data and models. I recommend that model designers and those designing data analysis study the full, 13-item list of characteristics in Cooper et al.’s book. It has permanently reshaped how I approach design of both basic and advanced “considerate model” elements.


Spreadsheet Models...Why Talk About It Now?

Why talk about spreadsheets in the closing months of 2023? This blog answers that question and serves as an intro to a series on spreadsheets’ role in companies, how to use them with excellence and their role in the general topic of personal digital transformation. The series will include sharing gold standard approaches and open-source links to tools and trainings.

There’s a dirty little secret from consulting and corporate experience. Spreadsheet models are pervasive and powerful in most companies. They do both glamorous and mundane tasks. It’s possible to simultaneously embrace the latest AI tool and modeling software while making amazing and constructive use of great spreadsheet models. For many, cleaning up bad spreadsheet models is a great jumping off point to better efficiency and further advancements with digital transformation in general.

Within companies, Microsoft Excel bridges between technical people and business, project management and finance team members. Even for those working with advanced, R&D or business simulation applications, Excel is great for placing models and data in the hands of non-coders. Well-formatted spreadsheets are also useful as Python model outputs for giving data to non-coders for inspection and study. Google Sheets is in the game too, but Excel and especially the desktop client version of it is still paramount in most companies I encounter. Finally, as a rationale for hitting the spreadsheet topic head on, spreadsheets’ wide-open grid can be used for both good and evil. Looking at this positively, this makes Excel a great teaching tool for the general topic of digital transformation

Ironically, after saying those positive things about spreadsheets, awareness and expertise with them helps put appropriate limits on usage for situations where they are not the best tool. I recently staked out a written position on these limits in writing about The Right Modeling and Analysis Tools for the Job directed at a generalist engineer and scientist technical audience. I am teaching undergraduate engineers from this basis in their senior design course. The blog includes a significant section titled, “Don’t overuse Microsoft Excel.”

 No shame if you haven’t thought about these topics directly, but here are subtopics that may help you engage. I will retroactively link to these bullets as I write about them:

  • There is a complex brand called Microsoft Excel. Microsoft Teams promotes a watered-down, online version. Which Excel are we talking about as the gold standard for building and using spreadsheet models? (and necessarily treading into the dangerous area of thoughts on Google Sheets versus Excel pros and cons)
  • What are the problems with typical spreadsheet models within most companies? I refer to this as anchoring the conversation in “a standard of crap” or SOC. Bad spreadsheets cause problems! Sincere apologies to whoever I lifted the SOC term from. I do not remember, but it’s perfect in this context. For spreadsheets, knowing the SOC means being grounded in the problems caused by poor quality spreadsheets that haunt even great companies.
  • What characteristics define a Standard of Excellence (aka SOE) for spreadsheet models and, quite frankly, an SOE for models in any other software? This SOE is great for model authors, but it’s also a checklist that managers can use to survey their current landscape.

  • A pivotal topic for desigining great spreadsheet models and models in general: What is the difference between structured data and non-structured (but very necessary!) summaries of data? I use a multi-point definition for what makes data “structured.” Once you know it, you cannot unsee it, and you can apply it to any situation.
  • What describes a great user interface for “data”? Most people have not thought deeply about what makes a rows/columns table easy to digest, but we have. One thing that causes people to deviate into SOC practices is desperately and pragmatically trying to find humane ways of simply looking at and working with their data. We have had  success creating great user interfaces that discourage needing to do that.
  • Along the way, I will share additional general or “Tech Notes” blogs on specific tools and techniques for creating outstanding models. Consulting projects have led to development of general VBA macro tools for authoring and working with Standard of Excellence models. I will share and discuss the role of those tools too.

If you are interested in a quick start on improving your MS Excel mastery, our online Digital Transformation with Excel course is a great place to start. It is a one-workday investment to take but can be broken into shorter sessions with topics covered in short videos that refer to hands-on examples.


Google Colab Tutorial For Running Python Notebooks

This provides a short tutorial for Google Colab as an alternative to Jupyter for running Python code. We show how to bring in, modify and run a Jupyter Notebook from a Github repository.

Colab (short for "Colaboratory") is a Google cloud service. It allows users to write and execute Python code in a web-based environment without needing to install anything locally. Within limits, Colab is free to use, and it interacts with a user’s Google Drive, so Colab notebooks can import additional Python libraries from *.py files. Additionally, the instructions here would allow usage from a Chromebook or on a CPU that does not allow local, laptop file storage. To demonstrate Colab, we will use a case study of running the Jupyter Notebook in this Pandas introduction Github repository called Pandas_Intro_For_Noncoders. This tutorial walks step-by-step through using Colab to run the notebook including modifying the repository notebook to import its data from a repository folder on Google Drive.

Note that several helpful code snippets are available in pasteable format at the bottom of this blog.

Opening Colab and Cloning the Pandas_Intro_For_Noncoders Github Repository

  1. Open Colab by navigating to https://colab.research.google.com. This opens a blank notebook (e.g. Untitled0.ipynb in the picture below)
  2. To access your Google drive from the notebook, mount the drive by executing the following to Python statements in a cell.
    • Type the statements into a blank cell (Can use +Code button to add cells as needed)
    • Run the cell by clicking its run button (black circle with triangle) or by clicking in the cell and typing Shift+Enter
  1. Optionally, add a new folder (e.g. Projects_Python in example) to your Google Drive by clicking on the three-dot menu next to the folder and choosing New Folder.
  2. The picture shows how to access your Google Drive’s folder tree.
  3. It is helpful to also open a browser tab pointing to your Google Drive (https://drive.google.com/
  4. Use the +Code button at the top of the notebook to add two blank cells
  5. Enter and run the %cd command to change directory to the desired folder
  6. Enter and run the !git clone command shown below to clone (e.g make a copy of) the Pandas Intro Github repository directly into the selected Google Drive folder.

Opening and Running the Pandas_Fundamentals.ipynb Notebook in Colab

  1. The Colab window does not have a way to open the notebook directly. Go to your Google Drive tab and right/control-click on the *.ipynb.
  2. Choose Open With / Google Colaboratory. This opens the notebook in a separate Colab browser tab
  3. We are done with the previous Untitled0.ipynb notebook. It is ok to close this browser tab

  1. Note that you can run notebook cells individually or choose Colab’s Runtime / Run All menu
  2. Jupyter notebooks such as this one typically point to files assuming a hard drive (local folder) address. This causes a FileNotFound error when the notebook tries to open sample Excel data several cells into the notebook

 

  1. To point the Colab notebook to your Google Drive folder, insert cells as shown below to a) mount the drive and b) create a prefix string for the Google Drive path

  1. Add the dir_google_drive string as a filename prefix in the read_excel statement as shown. This allows the notebook to run from the sample data copy on your Google Drive. Be careful to include “/” delimiters as shown and pay attention to case sensitivity.

That gets the repository’s notebook running in Google Colab with the repository's sample data!

 

Useful code snippets:

#Mount user's Google Drive without precheck that it is already mounted
from google.colab import drive
drive.mount('content/drive')
#Clone a Github repository as folder to current (Google Drive) working directory
#Use !cd xxx to change directory to desired parent folder
!git clone https://github.com/jlandgre/Python_Colab_Template.git
#Attempt to mount user's Google Drive
is_drive_mounted = os.path.exists('\content\drive')
   if not is_drive_mounted:
try:
   from google.colab import drive
   drive.mount('\content\drive')
except ModuleNotFoundError:
   #Add statements for case where notebook is run as local Jupyter
   pass
#Set a string prefix for Google Drive directory path
dir_google_drive = '/content/drive/My Drive/Projects_Python/Pandas_Intro_For_Noncoders/'
#Add project's Google Drive path to sys.path to allow importing *.py libraries
dir_libs = dir_google_drive + 'libs'
if dir_libs not in sys.path: sys.path.append(dir_libs)

The Right Modeling And Analysis Tools For the Job

Software tools in the data and modeling arena often lead individuals and teams into counterproductive patterns. By being informed and intentional, you can choose the best tool for a particular job. It’s good to recognize that software providers, meaning companies and open-source communities, keep adding features and advocate for using their software broadly. Good for them, but modern tools let you pick the best tool for each part of a bigger job and port data among tools for efficiency and robustness.

As an engineer, data scientist and business generalist, I advocate (and here freely share my opinions about) a software ecosystem that works well in many parts of corporate and university research worlds. It has three primary tools for shaping and sharing data, for creating models and for exploring and visualizing data for decision-making.

  • Microsoft Excel® for making data democratically viewable regardless of source, for generating “end of the pipeline” reports and for creating spreadsheet models with calculations for use by non-coders
  • Python scripts for data reshaping and for developing coded models and data pipelines (possibly mixing in a little SQL)
  • JMP® software for design of experiments (DOE), exploratory analysis and data visualization with accompanying statistical analysis of data

This is solid, but a disclaimer is that it’s possible to be successful with different choices. I recognize that statisticians are going to learn and be fluent with R for example but this requires a lot of expertise and is limited for collaboration inside a company. This also doesn’t preclude using additional tools, which I do in consulting practice. However, considering this core ecosystem can help you think critically about your situation and what’s best for you and your team.

From experience with various organizations, here are two suggestions for fighting the “wrong tool for the job” pressure in corporate and academic cultures, and the discussion below goes into more detail on these:

  1. Don’t overuse Microsoft Excel where it is not the best tool.
  2. Don’t overuse Python for exploratory data analysis, visualization, and statistics. Instead use best-in-class JMP® for this

 

Don't Overuse Microsoft Excel

Not overusing Microsoft Excel takes some discipline. Our online Digital Transformation with Excel (DTE) course teaches responsible use for situations where collaboration and accuracy matter. We teach a surprising combination of long-time, core features. We call that a “hidden layer” that leads to well-curated spreadsheets that are transparent to understand and easy to validate. Excel is wonderful for making data viewable and formatted in intuitive ways. Well-curated spreadsheet models are a way to bring calculations and simulations to a broad range of users. In consulting practice, I use a home-grown, VBA validation suite to ensure that spreadsheet models are correct and stay correct over time. I use DTE Course user interface design principles and this openpyxl Python library to format Python model outputs to put well-formatted spreadsheets into clients’ hands for their review and understanding.

Excel at its Best –  Portable-Across-Software Model

with UI Features for Ease of Reviewing and to Highlight Calculations

Although you can buy books on how to do it, Excel is non-ideal for exploratory data analysis or for graphing data except for hard-coded dashboard-like graphs. Additionally, it is wise for Excel experts to be sparing in incorporating advanced, “new Excel” features into models (PowerQuery, PowerPivot with DAX language, Tables, matrix functions etc.). I observe that these are collaboration and robustness killers for most models and 90+% of users --in lieu of simply using “old Excel” to create broadly-understandable spreadsheets.

Controversial with some but avoiding “new Excel” as much as possible also steers experts to use a modular, pipeline architecture where advanced things get done by coded scripts that can be validation-suite verified. Yes, this means that I am lukewarm or even cold on the recent (August 2023) launch of “Excel can do Python” by Microsoft. I gently note that there are pre-existing, robust ways to script Python and even test scripts in Python prior to Microsoft’s “discovery” of Python for Excel. This is a good example of a software company getting marketing mileage out of continually adding features. Be wise to this practice!

Advanced-Feature Hype for Python-In-Excel

Don't Overuse Python

The second, flip-side recommendation is to avoid overuse of Python (or R) for exploratory data analysis, visualization, and statistics. This unfortunately seems to be a hallmark of the Python community. The cost can be seen among data science and analytics experts. Commercial JMP® software is superior in this space for general purposes. It is produced by the venerable and respected SAS Institute. The practical reality is that, within companies, there are wide swaths even within technical organizations where nobody is going to write a script to make a graph let alone fit a three-term regression model to make sense of business or industrial data. Without a tool like JMP, they will either go without analysis or use sub-optimal Excel.

Example JMP Graphical Output With Relevant Statistics For Decision-Making

Graphical Elements and Stats show that Product "b" is Superior and "c" is inferior

 

Python graphing and stats tools are great in specialized situations, but JMP is useful even for data popping out of Python scripts. JMP is currently on version 17, and its team has long put exceptional quality of thought into how to visualize statistics for decision-making. They have great self-training and webinar resources openly available. JMP is not free. However, it is widely available in companies through negotiated licenses, and it is available under educational discounts in most universities.

Interestingly, since I have long used JMP and started my Python journey more recently, I conducted informal, tabletop “consumer research” at global, scientific Python (SciPy) conferences. I note that many in the Python community are literally unaware of JMP’s existence. When people saw a demo, they were generally amazed by its capabilities and ease of use relative to what they were doing with Python tools –usually visualization tools that lacked direct, statistical support. These JMP demos brought immediate dismay to typical graduate students when they witnessed the ease of use. While not a good solution for everything, JMP analysis can be a workhorse for data-driven decision making in many situations

 

Making intentional software choices helps avoid getting sucked into doing ineffficient things, and it can break a culture of poor habits. Hopefully this discussion gives you food for thought about your personal or organizational software ecosystem for analysis and modeling.


What Data Delve LLC Does for Clients

My Data Delve LLC business comes down to a simple benefit for people I work with. Namely, I help you and your team be more effective at getting insights from your data to grow your business.

Here are six strategies I use for this. It is a toolbox from which we mutually pick the right tools.

  1. I help you define what you really want. This is sometimes called “problem definition” but it needs to also include “deliverables definition" to be productive. It is catalyzed by discussing and listening to what you innately know would help your business development, technology development or personal and team efficiency.
  2. I make your existing data work more efficient. Smart people have ways of getting work done, and there are no successful businesses that don’t, but we have tools and methods for removing the friction and migrating to better places. This can start with advice on and help with “spreadsheet straightening” or involve more complex help with migrating you to efficient “pipelines” to bring together data from multiple sources.
  3. I build “flight simulators” to let you try out different approaches for growth. These can consist of business simulators, ways of testing technical product formulations and processes or ways of simulating consumer responses. You already have the intelligence, and wisdom to master complex situations. Having low-cost ways to simulate and explore new things can be game changing. The “exploring” may consist of us building tools you can run yourself or you telling us what to try and reporting back to you from coded tools where we are a guide to bridge between your ideas and the potential solutions.
  4. I help you creatively visualize your data to bring out what it can tell you and how it can guide you. This may involve cross-connections such as reapplying techniques for visualizing complex manufacturing data to shed light on your in-market results with your business or with in-market products.
  5. I can educate you and your team on best approaches for working with data and the coded solutions for that. We have a suite of online and in-person trainings on software tools from basic to advanced. We are connected to and participants in world class training venues at companies, technical conferences, universities, and online schools. We know how to personalize the training message to you and your company large or small.
  6. I modularize data and modeling solutions to marry your confidential work with non-confidential open-source software. This avoids needing to start from scratch, and it makes us light on our feet and cost-efficient at getting answers for you. I am active in posting open-source building blocks I create in the Excel/VBA macro and Python language spaces, but we also use free, open-source code packages that are reliable and whose usage is in the 10's of millions. We bring these things to bear against your needs.

Good, Better, Best For Python Projects

I blogged about my observation that Python-coded models and analyses can be grouped into three robustness levels by architecture and macroscopic coding arrangement. Here is a more detailed discussion of the three levels taught also at this training link to a Practical Python for Modelers short course.

The 3-level scale is based on how a project does in three areas: First, is the model or analysis “curated” for the developer and others to understand, inspect and review? Second, are the data transformations and calculations validated with re-runnable, coded tests to build confidence in correctness over the useful life of the model? Finally, are solutions maintainable and extensible by the original developer and colleagues or successors?

Level 1 is what I call the “ad hoc Jupyter notebook approach.” It will garner the highest grade in Data Science coursework and is the “I claim Python expertise on LinkedIn” threshold. Level 1 notebooks will not be questioned for models built for personal usage at a company.  It is the go-to for short, linear, one-time analyses and demos. It leaves a lot to be desired beyond that though, and one-time analyses have a funny habit of morphing into permanence.

Level 1 code is rarely well curated and is difficult for anyone else to re-use. From personal experience, its utility breaks down with complexity of data sources and analyses. Level 1 analyses cannot practically be validated with a re-runnable test suite. Usually, Level 1 “validation” consists of printing out and looking visually at a result or performing quick hand calculations: “Yep. That looks plausible. Move on.” That makes things high risk for errors either initially or especially if re-used later.

What I call Level 2 is my go-to approach for client-requested, one-time analyses and for exploratory work. At Level 2, this is still [usually] based in a Jupyter notebook for convenience, but use cases get organized into what I call procedures –groupings of single-action functions that, when executed in order, perform some task or data transformation.

I use the diagram to illustrate a Level 2 and 3 “architecture.”  The left block represents a model’s user entering some inputs and executing a use case. The right-most functions are single-action –easy to debug and, at least at Level 3, validated with re-runnable Pytest testing. The “procedures” in the middle are procedural functions that call the elemental ones sequentially to do the work as shown in example code below. It is arbitrary what defines a “procedure,” but they should generally be single-topic and independent (e.g. “transform the ingredients DataFrame into MKS units and add calculated columns.” Or “Create a rows/columns summary of business unit sales and revenue by calendar month”).  Delivering the user-facing use case consists of performing 1+ of these more abstract “procedures.”  While there are other architectural frameworks out there, I find that this one works well for 90+% of user-facing models and analyses.

 

The structure makes troubleshooting easy, and it makes it possible to maintain and extend the model by refactoring individual functions, splicing new functions into a procedure or by adding new procedure functions and their right-side collections of elemental ones.  At Level 3, all such upgrades can come with re-running the test suite to ensure nothing got broken.

Level 3 is what I call Tuhdoop –referring to a combination of Test-Driven Design (TDD) and Object Oriented Programming (OOP).  I coined the term and wrote about Tuhdoop and its synergistic benefits in this Github tutorial. I notice that I can create Level 3 code as efficiently as Level 2 and as efficiently as Level 1 if I factor in rework and debugging time.  I am gradually trending to a hybrid work process of developing at Level 2 and then porting the code to Level 3 as appropriate. A distinction between Level 2 and 3 is that Level 3 is object-oriented –often consisting of a single Class per use case or sometimes a Class for the use case that relies on other helper Classes.

To go with the architecture diagram, this screen picture shows typical Level 3 code where the application code (left side) and test code (right side) mirror each other and are organized by procedure. Well-organized code is self-documenting. The testing also forms part of the documentation by making it easy to probe and learn about what individual functions are doing.

As a closing aside, I found that the TDD theme in Level 3 and using ChatGPT to spit out function skeletons has influenced me personally to improve at planning the procedures and elemental functions and in prewriting the tests. I experiment currently with using ChatGPT with prompts like, “For the above rows and columns table listing Python functions in Column A, write the skeleton of the Python function with its arguments (Column B) and write a Pytest test function to verify that it generates the expected output.” I am still undecided whether this will ultimately be advantageous versus writing my own Python helper script to do this systematically. This seems like it might be easier than trying to cajole ChatGPT to do the same thing repeatably. Regardless of how that turns out, credit to GPT-4 for the inspiration.


Robust Models and Analyses – Defining Good (or Meh!), Better, Best

Python is everywhere these days. Universities crank out Data Science majors and minors. Middle school kids are learning the language. There is a groundswell of coding as a generic skill for non-software professionals --engineering, scientific, business, journalism and even liberal arts disciplines. That’s great, but, when it comes to building models and doing important analyses with the language, I observe three distinct levels of robustness. I group projects into these levels based on how they are organized (aka “architected”) and whether they come with re-runnable testing. That is a standard in the software industry for good reason. If you are learning Python, you can use my distinctions as a roadmap and ensure you have the top two levels in your repertoire. If you are a manager of people who create data products and models for decision making (or if you hire consultants!), it should be a priority for these analyses to be curated, validated and maintainable/extensible. The quality of these three items varies wildly and honestly tends towards the low end of the scale.

I recently created a workshop training called Practical Python for Modelers for an audience of R&D engineers and scientists ranging from Python beginners to computer modeling experts. I am reapplying it in collaboration with Dr. Will Hartt and colleagues at University of Delaware Chemical Engineering. We seek to educate highly technical undergrad and graduate students for whom data science is outside their immediate domain. The slides and training data are available at the link.

A followup Tech Notes blog goes into more detail about what distinguishes the three levels of Python styles/architectures. Here are definitions of the three robustness objectives:

  • Robust models and analyses are curated. Another knowledgeable human can look at the files and not only repeat the analysis but extend it without the author needing to be present.
  • Robust models and analyses are validated. They come with re-runnable test cases consisting of mocked up data proving correctness of calculations, data cleaning steps and output generation.
  • Robust models are maintainable and extensible. They are written in a modular way, so that they are easy to build on and easy to reapply.

Look for these things in what you generate. If the work is done externally for you, ask consultants to write them into proposal deliverables along with the direct, decision-making models and analyses! You will not be sorry.


Our New Online Course: Digital Transformation with Excel

This announces a foray into online training with launch of our Digital Transformation with Excel or DTE course. I partnered with Toronto-based Predictum Consulting LLC and CEO Wayne Levin on this. It brings together my data and modeling mentoring insights from decades of P&G R&D work as well as from recent consulting work. DTE recognizes the pervasiveness and unabashed usefulness of Microsoft Excel in organizations. The result is something I’m proud of and something that is uniquely beneficial among the soup of trainings out there.

Please reach out on LinkedIn or Twitter (@data_delve or @Predictum_Inc) if you want to discuss setting up a preview call, live training options or group discounts. DTE's target audience is broad. I hope a wide range of folks will experience it:

  • Modeling experts, Finance experts and Analysts can expect to learn a rather surprising “hidden layer” of features for authoring models in a way that is relatable to less technical customers. This audience bullet point is my tribe too. Elegant use of Excel connects models to the business in a relevant way since the downstream world runs on it. Learning how to structure and curate data and models can often be new even to many experts versus a standard practice of constructing non-structured, spreadsheet model “dashboards.”
  • Multi-functional leaders can expect to gain personal, working-with-data skills but also to get cultural insights on what to emphasize and encourage as “this is how we do things around here” for digital transformation
  • Administrators, project managers and anyone else who works collaboratively on teams can expect to learn skills for combining “business process” data in new ways that calm the constant barrage of chat messages and emails on topics ranging from budget questions to project lists, product designs, project status and so on.

DTE is more than just a collection of skill tidbits such as you can find for free on YouTube. It is built on four, higher-level themes that, when combined together, create data and modeling magic:

  1. How to be fluent to work well with large and small data sets and models (Example skill: 50+% speedup via practical use of built-in shortcuts for navigation and for issuing commands)
  2. How to structure data for extensibility and portability (Example skills: How to include “keys” in data for filtering and sorting; building multi-table models with the XLOOKUP function)
  3. How to curate data to maximize personal and collaborator useful life of data (Example skill: Cell and Range naming to curate formulas)
  4. Designing excellent spreadsheet user interfaces to encourage use of data and calculation models for collaboration (Example skills: Column Outlines and unique Conditional Formatting techniques for working comfortably with large models)

Besides Excel’s pervasiveness, I will add an additional word on “why Excel?” as a training topic in 2022. We know that company software ecosystems are complex. People work with multiple tools as I do in my consulting practice. However, Excel’s wide-open, grid structure makes it ideal for teaching how to make data and models collaborative and portable across software. It is also true that Excel can be used for both the good of constructing great models or for the evil of making hard-to-decipher, spreadsheet scraps that die on individuals’ hard drives. To bring about true digital transformation, we teach how to bend towards the good and how to steer clear of spreadsheet evil.

DTE practical details:

  • It consists of 3 hours of short videos combined with case study examples. When taught live, it is a “one-day” course.
  • DTE is cross-platform for Mac and Windows versions. We discuss relative strengths and weaknesses of Excel variants but make the teaching fluid and relevant regardless of preferred operating system
  • DTE is hosted on Predictum’s Thinkific “academy." Here is a link to Predictum’s LinkedIn announcement of DTE and intro of me as lead trainer.