Benford’s Law in Excel – Part Two

Benford’s law is used in auditing to identify data sets that may have been manipulated or adjusted. In my previous post I created a report to analyse a data set based on Benford’s Law. In this post we will create a single formula to create the report and then convert that into a custom function.

Benford’s Law in Excel – Part One

Benford’s law is used in auditing to identify data sets that may have been manipulated or adjusted. In actual data sets when reviewing values the 1st digit of the values tends to follow a predetermined frequency. For example, roughly 30% of the values should start with a 1.

One Minute to Excel #30 – Extract Data Based on Sheet Name

Shortcut to speed up name creation

Here’s a technique I use a lot to speed up report development.

Sheet names have to be unique, so they can’t be duplicated. This makes them great for department names or states.

This short video combines a few techniques to extract from a data set based on the sheet name.

All in less than a minute.

Adding up Text Numbers In Excel Another Technique

If you have a list of numbers that are a text numbers or a combination of text numbers with real numbers there is a technique I covered in this blog post to add them up. But if the range also contains text then the technique won’t work. There is the work around. The solutions below work in the subscription version of Excel. Check the comments section below for a solution for all versions.

One Minute to Excel #24 – 1,000 random dates

A RANDARRAY solution

Let’s say we need to do some testing and we need 1,000 random dates in 2022.

We can use a new function to make this easy to create and easy to change.

RANDARRAY usually works with numbers but in Excel dates are numbers, so we get it to create random dates for us.

I set myself a challenge to do this in less than minute – see how I went in the video below.

Switching Reports from Rows to Columns in Excel

TRANSPOSE and OFFSET solution

I was recently helping someone with a budget which they had built vertically, with the months going down the sheet. They then asked to display it horizontally, with the months going across the page. In the latest version of Excel this is straightforward.