Prime Numbers (and AI) Using ChatGPT and other AI tools to write Excel formulas has become popular. Bill Jelen aka Mr Excel has a simple test to see whether the particular AI engine is reliable: ask for a formula to determine whether a number is a prime number. Until recently, the response is this formula to test cell A2: =ISPRIME(A2) This makes use of the well-known but nonexistent ISPRIME function (which does not exist in Google Sheets either). ChatGPT is now using a newer body of knowledge to generate its answers, but it still includes “may occasionally generate incorrect information” amo ..read more

In two recent articles, Excel Lambda Moving Average and Improved Excel Lambda Moving Average, I described my efforts to build a Lambda moving average formula that worked the way I wanted it to. I’ve included average calculations for the first few data points before the number of points being averaged is met. I’t’ve also included only counting points within a set number of days, even if there are missing days within the range. Here I will compare three different moving averages, the Simple Moving Average (SMA), the Weighted Moving Average (WMA), and the Exponential Moving Average (EMA), often c ..read more

Main Topics Cornerstone Articles (Important and Popular Posts) Axis Labels Axis Scales Chart Events Combination Charts Conditional Formatting of Charts Custom Chart Types Data Labels Dynamic Arrays, LET, and LAMBDA Dynamic Charts Error Bars Floating Bars Gantt Charts Gaps Histograms Legends Marimekko Charts Pivot Tables and Charts SERIES Formula Slope Charts Statistical Process Control Statistics Tables Trendlines and Regression Cornerstone Articles (Important and Popular Posts) Good Chart Data The Excel Chart SERIES Formula Install an Excel Add-In Excel Waterfall Charts (Bridge Charts) Clu ..read more

I recently wrote about how I wrote an Excel Lambda Moving Average formula. I started with internet searches, which led to formulas which didn’t work or formulas which were too complicated for me to understand. After a brief and amusing foray into ChatGPT, I built myself a workable formula that worked by generating a running sum, subtracting an earlier point’s running sum from the current point’s running sum, and dividing by the number of points. This worked fine for a simple N-point moving average, but it broke down for N-day moving averages where some days in the data set may be missing. And ..read more

Moving Averages When averaging time-series data, you often want to smooth out peaks and valleys. A moving average is an easy way to smooth your data. When I track my weight, for example, I use a 7-day moving average. This smooths out peaks associated with weekends when I might go out to eat and enjoy a beer or two. The image below shows 25 random data points and a five-point moving average. The points were generated with this Dynamic Array formula in cell B5: =RANDARRAY(25,,0,10,TRUE) and the moving average was calculated with this formula in cell C5, filled down to C29: =IF( COUNT(OFFSET ..read more

A certain data layout can produce a chart axis which divides and subdivides the categories into logical subcategories, such as years, quarters, and months in the following chart. Generally, this data layout must be produced by hand, because it relies on an arrangement of filled and blank cells to help Excel parse the data into subcategories. In this post I’ll show how a LAMBDA formula can build the range for you. Bulgaria Excel Days 2022 Earlier this month I had the honor and pleasure of participating in Excel Days 2022, held in Sofia, Bulgaria. On one day I held my Advanced Excel Charting Ma ..read more

tldr; Stick to XY Scatter charts if you need trendlines for your data. Line charts may misrepresent the relationships in your data. Trendlines and Chart Types A user had problems with my Trendline Calculator for Multiple Series and sent me his workbook. It turns out, he was using the program on a Line chart, and I recalled that Line charts can have problems when calculating trendlines. Here is some simple data, plotted in a Line chart. A trendline has been calculated, and the formula and R² are shown in the chart. Wow, that’s a very nice straight line fit, with all points exactly on the line ..read more

There are occasions when you may want to break the link between a chart and its underlying data. Maybe you copied the chart from another workbook, and you no longer have access to that workbook. Maybe you want to avoid the headaches that may arise from pasting a chart into PowerPoint or another program. Maybe you’re just tired of seeing this warning when you open the file: There are several ways to disconnect your chart from its data source. Chart Data First let’s review chart data. I’ve written a lot about chart data, including Good Chart Data – The definitive description The Excel Chart SE ..read more

Last week we released the PBCharts (Process Behavior Charts) tool for performing SPC control chart analysis. I’ve been working on PBCharts for well over a year, and we had an extended beta testing period, during which we cleaned up numerous issues. I thought I’d walk through an analysis to show how easily PBCharts does its work. My colleague posted a quick analysis of inflation since the start of 2019, and that looked like a good data set to analyze. The data file CPI YoY Pct.xlsx (click to download) looks like this, showing date and year-over-year percentage increase of the consumer price in ..read more

How many points can I plot in each series of my chart? How large of a VBA array can I plot in my chart? Two good questions, which I’ll investigate here. A colleague emailed me asking about the VBA array size limit for plotting in a chart. He said he thought the limit was 32,000 points, but couldn’t find any official documentation of this, and his trials only worked for half that many points. I couldn’t find any documentation of any limit on how large a VBA array can be used to populate a chart. tl;dr The number of points in a chart series populated by worksheet ranges is limited by available m ..read more