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.

Possible Dynamic Array Bug

Values issue

Recently I have been using more dynamic array formulas and have come across an unusual situation where a date is not treated like a date.

Remember dynamic arrays are currently only available in the subscription version of Excel.

Have a look at the image below.

You can download the example file at the button at the bottom of the post if you want to see it in practice.

Cell B1 is an input cell to set the first date of the sequence.

Cell E1 creates sequential numbers using the SEQUENCE function.

Row 2 has the dates and is formatted as a date.

Row 3 is the problem. If I try calculate the number of calendar days in the month using DAY and EOMONTH functions it gives a value error as if the entry in E3 is not a date.

If I multiply the E2# reference by 1 it fixes the issue as per row 4, but I shouldn’t have to do that.

I have had this is other cases where a number is not treated a number until you multiply it by 1 or perform a calculation with it.

Not sure if anyone else has seen this?

If you want to learn more about dynamic arrays I have a free Webinar Recording plus a pdf manual at the link below.

Free Introduction to Dynamic Arrays Course

Download Example file