Excel: How to Count TRUE or FALSE Entries in a Range

I’m working on a new Excel title right now and I was going to put a note in there about counting the number of TRUE or FALSE entries returned by the EXACT function, but then I realized that there actually isn’t a SUMA function that would let you do that. At least not in Excel 2013.

So I asked myself, how could I take a column of 10,000 entries and find out how many of those entries were TRUE versus FALSE?

You may be thinking to yourself, why would I even need this? When is that going to happen? The scenario I was looking at was using the EXACT function to compare two columns of text to identify any entries where those two columns aren’t the same value (like I do when I’m looking at AMS ad performance). I generally use an IF function for that so I have blank spaces or ERROR as my values, but if you use EXACT instead you’ll have TRUE and FALSE values. And it’s hard to scan down a list like that to count how many FALSE entries you have, if any.

So what can you do if you have a column of 10,000 entries that read TRUE, FALSE, TRUE, TRUE, TRUE, FALSE, etc. etc. and you want to easily count the number of entries that are TRUE or the number that are FALSE?

There are any number of options. You could filter your data. You could sort it. You could create a pivot table even. But how do you do this with a function?

It turns out that Excel assigns values to TRUE and FALSE. TRUE has a value of 1. FALSE has a value of 0. That’s why I thought there should be a SUMA function to tell Excel to sum your TRUE and FALSE entries. But there isn’t.

What there is is the AVERAGEA function which will average the values across a range, including your TRUE and FALSE values. So if all of your entries in a range are TRUE, you’ll get a value of 1 using AVERAGEA. If they’re all FALSE you’ll get a value of 0. If some are TRUE and some are FALSE you’ll get a decimal value representing the proportion of your entries that are TRUE.

It’s very simple to convert that decimal value to a count of TRUE or FALSE entries as long as you know the total number of values you’re averaging.

For TRUE entries it’s number of total values * AVERAGEA for the range.

For FALSE entries it’s number of total values * (1 minus AVERAGEA for the range).

And if you want to get really fancy because you don’t know the number of values you’re dealing with, you can use COUNTA to get the number of values.

So for Column G, for example, if we wanted the number of TRUE entries we’d use:

=COUNTA(G:G)*AVERAGEA(G:G)

COUNTA will count all entries including those with TRUE or FALSE values and then AVERAGEA will average all values in the range, including TRUE and FALSE entries.

This will only work if the entire range consists of TRUE and FALSE values or blank spaces, but it does work.

Like so:

Counting TRUE and FALSE Entries

The Excel PRODUCT Function

I have to confess that the PRODUCT function in Excel is one I’ve generally considered pretty useless. SUMPRODUCT is much more exciting to me. But this morning I was trying to do some projections for sales of my new cozy mystery and I ended up using the PRODUCT function two different times.

And because I’m a nerd and Excel things excite me, I figured I’d share where and how I used it.

This will also be useful to any writers who write in a series or are thinking of doing so and want to extrapolate from sales of a first in series to sales of the whole series.

So here’s the thought process and how the PRODUCT function can come into play:

At its most basic, if I publish a single book then my earnings on that book are equal to the price of the book times the payout percentage. (In reality, if you’re in KU or have a print version it’s more complicated than that because each format has its own value but we’re going to ignore that for now.)

Now, there’s a temptation to say, “If I write ten books in this series, I’ll earn ten times as much.” But that’s not how it works.

Not everyone who reads the first book will go on to read the other books. I have a series of eight related short romance stories and I see a drop off from story 1 to story 2 and then from story 2 to story 3. But from story 4 onward it’s very close to a 100% readthrough.

If I want to calculate the value of a new customer I can’t just take the price of each story times the payout. I also have to factor in how many readers actually make it that far into the series.

So the value of book 8 to me is not price times payout. It’s price times payout times % of readers of book 1 who read it.

This is the first place you can use the PRODUCT function.

Because the calculation you’re doing here is: price times payout times % who read book 2 times % who then read book 3 times % who then read book 4 times % who then read book 5, etc.

(In Excel for Self-Publishers I did a similar sort of calculation but used a different approach that just looked at book 1 to that particular book in the series because I had real data at that point. But this is extrapolating when you don’t have any data yet.)

One way to write that calculation is: =A1*A2*A3*A4*… where each of those values is in a different cell in Column A.

Another way to write it, though, is using PRODUCT. You just write =PRODUCT(A1:A9) assuming your price is in A1, your payout is in A2, and your readthrough rates are in Cells A3 through A9.

Isn’t that nice and simple?

I know, it’s a little hard to visualize. And you’re probably not going to set your data up exactly that way.

But let’s look at a simpler example, which is the second way in which I used PRODUCT.

First I had to calculate the value above. I assumed I had a ten book series with book 1 priced at 99 cents and the rest at $3.99 with a fifty percent readthrough from book 1 to book 2 that then goes up to 80% and then 95%, I calculated that I would earn $7.54 for each new reader.

Currently book 1 is priced at $3.99.

So that’s an increase in overall revenue by a factor of 2.70 just based on having more books out. (7.54 divided by 3.99).

But we also have that price drop factor at play. How many more people will read book 1 if it’s priced at 99 cents than are currently reading it when it’s priced at $3.99? For my purposes I said three times as many.

(This is for cozy mystery. Other genres might see no increase or even a drop. Or readthrough might be severely impacted depending on book 1 and book 2 prices. You have to know what you’re selling or have a guess how it’ll behave to do this.)

Next, we have the series factor. Some people prefer to read series instead of standalones. And more books means more visibility. So how much will having ten, presumably well-reviewed, books in a series impact book 1 sales? In this case I assumed that would double sales although there’s a good chance it could do more than that.

So if I want to take current sales on book 1 and try to figure out what I might earn if I have ten books out in that series what I need to do is take the amount I’m earning each month on that book and multiply it by that readthrough factor and then multiply that by the price drop factor (assuming I’m dropping book 1’s price, otherwise it’s just 1), and then multiply that by the series increase factor.

Which is another place to use PRODUCT. Here’s the Excel worksheet so you can visualize this:

Series Value Estimation

I have the increase factors in Cells C34 through E34, so my formula in Cell F34 is =PRODUCT(C34:E34) to get my overall factor of 16.2.

I can then take that number and multiply it by the amount I expect to earn on the first cozy when things reach a steady state. Let’s say $250. (In the image above that calculation’s done in Cell G34.)

(You can use PRODUCT again here, which is what I did. In that case, the formula is =PRODUCT(B34:E34) because I have Book 1 sales in Cell B34. Or you can just do =B34*F34.)

Based on these calculations I can say that if I have a Book 1 in a series that is earning me $250 a month and I write nine more books in that series and discount the first book to 99 cents while selling all the other titles at $3.99 with my assumed readthrough rates and price drop and series increases that I will earn, on average, $4,000 a month from those ten books.

Understand, though, that there are many many assumptions at play here. If my Book 1 to Book 2 readthrough is 75% instead, that number goes up to $6,000 a month for a ten-book series. If I see a four-fold increase in sales from having a series rather than a two-fold increase then it’s $8,000. If I have 75% readthrough AND a four-fold increase, then it’s $12,000 a month.

On the flip side if there is no series increase and dropping the price only doubles sales then it’s just $2,000 a month for a ten-book series. And if people fall off of the series as it continues (say you have lower and lower readthrough rates after Book 6) then it’s just $1,445 a month for that ten-book series.

(Don’t continue a series like that. Wrap it up as soon as you can. Book 1 to Book 2 you should see drop off. Family and friends will buy Book 1 to support you but not read the whole series, some people will find that the book just isn’t for them, and some will buy and not get around to it for five years. Book 2 to Book 3 may have another drop off because people who were on the fence with Book 1 may try Book 2 and then decide not to continue. But from Book 3 onward, you should really have your core audience.)

(I should also add that there could be a time factor at play here. The longer the series the longer it takes someone to read through that series. On a recent podcast someone mentioned six months for how long it can take to read through a series. For a very long one, I’d agree that might be true. For a trilogy, if you’ve really hooked a reader, I’d say one month at the longest. Of course, that’s from when they start reading it. But once you reach steady state for a series that should disappear in the wash because people should always be reading through your series each month even if they’re at different points in doing so.)

Anyway. You can make all of this insanely complicated if you want, but this was just a basic calculation I wanted to share using PRODUCT which I had so unfairly misjudged. Once you set something like this up, it’s easy enough to play with the numbers and see your full range of values.

 

 

Excel Templates Now Available

I’m trying an experiment. We’ll see how it goes.

Basically, I’m making the Excel files I used for Excel for Writers, Excel for Self-Publishers, and Excel for Budgeting available for purchase via the Payhip store. These are for anyone who wants to perform the calculations in those books but would rather not go through the steps of recreating all of the worksheets themselves.

They are not ready to use out of the box. They will need to be tailored to each individual’s information, so there is definitely still Excel work that needs to be done to use them. But for those who aren’t as comfortable with Excel as I am or who just want to save a little time, they’re now available.

The Excel for Writers and Excel for Budgeting templates are 99 cents. The Excel for Self-Publishers template is $2.99.

Note that these templates are meant to be used in conjunction with their respective books so they do not come with detailed instructions for how to use each tab because that is covered in the books.

We’ll see how this works. Hopefully it’s useful for people but if it turns out to create more confusion than it’s worth, these won’t remain on sale for long.

Excel Essentials Quiz Books Now Available

I wrote Excel for Beginners, Intermediate Excel, and 50 Useful Excel Functions to standalone, but it occurred to me that some people will want to test their knowledge of the material covered in each book.

So to accommodate that I have just published three new titles: The Excel for Beginners Quiz Book, The Intermediate Excel Quiz Book, and The 50 Useful Excel Functions Quiz Book.

The-Excel-for-Beginners-Quiz-Book-Generic     The-Intermediate-Excel-Quiz-Book-Generic    The-50-Useful-Excel-Functions-Quiz-Generic

 

 

 

Each quiz book includes a series of questions that cover the material from the original titles as well as providing written answers to those questions that you can review. There are also five bonus exercises in each one that let you practice applying what you’ve learned with real-world scenarios.

The books are available in ebook on Amazon, Kobo, Nook, and Apply. Print books are available on Amazon and Barnes & Noble and should be available on other retailers in the next few weeks.

Also, you can also always buy direct through Payhip using Paypal at https://payhip.com/mlhumphrey. And, in fact, if you think you’ll be buying more than one book and are comfortable buying that way, you can get some discounts that way. Just select your first book and look for the pop-up offer for other titles at 10% off.

Please to enjoy and for those in the U.S., Happy Thanksgiving.

A Little Reminder on Backwards Compatibility

Microsoft is at it again, releasing new versions of its Office suite of products. So new users will have Word 2019 and Excel 2019, for example.

And on the Excel side there are a few exciting (to me, because I’m a nerd) changes they’re making. A lot of it around IF functions. Here’s the link to what’s new in Excel 2019 and the link to what’s new in Word 2019 for the curious.

The key issue here, though, is backwards compatibility. Just because you may have the latest and greatest does not mean anyone else does. And using one of those new functions if you’re going to be sharing your files with someone who isn’t upgraded to the latest version is going to mean they can’t use what you send them.

So by all means, upgrade and try the new functions in Excel. But if you intend to share that document with clients or counterparties, be sure that they can use the file you send them.

Let me give you a personal, very painful example of how I learned this lesson the hard way.

Back during the big mortgage crisis I had a consulting client who had a large residential and commercial loan portfolio. And I ended up in a role where I was helping someone who was an expert in calculating a bank’s allowance for loan losses automate that process using Excel. The goal was to create an Excel workbook we could then hand off to the client so they could make that calculation going forward whenever they needed to. When we were done, all they’d have to do is put their current data into one or two worksheets and everything else would be calculated for them.

It took about a week for me to put the workbook together because there were a lot of moving parts, but I finally had it ready to go and handed it over to them to test.

(Now, I should add here that for a long-term solution Excel was not an ideal choice. But for something they could have up and running in two weeks? It was probably the only choice. And this was at a point in time where bidding things out and taking six months to build a technology solution were not options.)

So, anyway, I handed it off.

The client came back and said they couldn’t use it. Because it relied, in part, on using the SUMIFS function, which was available in my version of Excel but not their version. And getting a large corporate client to upgrade their version of Office is not a simple process, especially during the midst of a financial meltdown where it was very possible that company wasn’t going to exist in six months.

So I had to spend a couple days rewriting that whole workbook to remove the use of every SUMIFS and replace it with multiple IF functions in multiple columns that could accomplish the same result.

It was not fun.

Thankfully, I worked for the type of boss who didn’t blame me or yell at me for my mistake, just told me to fix the issue. If I’d been working for a different client or a different boss that whole situation could’ve been much much uglier than it was. As is, it was bad enough.

So remember: keep in mind who you’re working with when you create an Excel workbook (or even a Word document or PowerPoint presentation) and make sure that they’ll be able to use what you give them when it’s done.

(I should add here that all of my current Office guides are written using the 2013 versions of the products–so Excel 2013, Word 2013, and PowerPoint 2013–and with most of it compatible back to the 2007 versions, partially for these reasons.)

Excel for Budgeting is Live

File this one under why I should never read my emails. Haha. Just kidding, this was one I’d been thinking of writing for a while but just hadn’t written yet. So that email someone sent a couple months ago asking how to apply Excel to budgeting was just a good reminder that I wanted to get this done.

Over the last ten years of self-employment I’ve developed an Excel workbook I use to juggle my finances. Because one of the biggest challenges of self-employment is cash flow. I can make $25K in one month and then nothing for three. So I have to always be monitoring where I am in terms of cash to pay my bills. And I have to know where I can look to get cash to pay my bills if there isn’t a big paycheck coming and there isn’t enough in the bank account.

So over the years I’ve developed a focus on my short-term liquid net worth and also on haircutting my assets. (Much like a broker-dealer is required to haircut their securities portfolio when they value their holdings.) Because a 401(k) is great and all, but it doesn’t do you any good if you can’t pay your bills today.

In Budgeting for Beginners I talked about all of this and how to judge where you are financially and take steps to improve upon that. But what I didn’t include there was the Excel workbook I use to track all of this. And it’s not exactly intuitive how you’d create something like this.

I now have one nice little worksheet I can print off that shows my next three months of cash flows, my assets and liabilities, my available credit, and my short-term and long-term net worth. But when I look at my trackers from ten years ago they are nothing like what I now have.

(I think I missed my calling as some sort of data nerd. But, oh well.)

So anyway, that’s what Excel for Budgeting is. It walks through how to create this workbook for tracking your finances. It’s what I use and (knock wood) I’ve somehow managed to stumble along for close to a decade now with uncertain income and making extreme life choices. I do think you should know Excel before you try to use it, but there are step-by-step instructions in there for how to create everything if you want to give it a try anyway.

(And, as a weird added bonus, if you’re like I once was and split time between two countries so have to deal with payments in multiple currencies, I covered that, too. Because, why not?)

Now maybe I’ll be turning to writing some fiction. Maybe. As long as I don’t walk my dog or check my emails before I start the next project.

Free Video Course: Pivot Tables

To celebrate the release of my latest series of books, the Easy Excel Essentials series, I have a free video offer for anyone who wants to learn how to use Pivot Tables in Excel.

Here’s the link: https://www.udemy.com/pivot-tables/?couponCode=EEE_PT_50FREE

If you are a self-published author, have Excel, and don’t know how to use pivot tables yet, please use the link and learn how. Because I honestly do not know how authors monitor their sales on Amazon if they don’t know how to use pivot tables.

True story: My annoyance at the fact that authors do not know how to use pivot tables (even though they really are as easy as dragging and dropping fields in the right places), led me to write four books.

I started writing an Excel for writers book that would include how and why I use pivot tables as an author. That book ended up being split into two books: Excel for Writers and Excel for Self-Publishers. But then I realized that there would be some folks who didn’t know how to use Excel at all and some who knew some Excel but not enough. So I wrote Excel for Beginners and Intermediate Excel.

Four books written. All because people annoyed me with their overreliance on BookReport and their complaints about not being able to see their sales using the Amazon reports.

(Yes, I am crazy…)

And, as a side point, here’s a picture of the new books:

New Release Image EEE

The astute observer will realize that these books actually cover material that I’ve already covered in my other Excel titles. But I figured there was a market for people who just wanted to learn one specific topic. (And do so for just $2.99 in ebook, nonetheless.)

This was one way I thought of to “extend” my Excel books when I felt that I’d pretty much already said what I felt needed to be said about Excel. I have another idea for extending the Excel titles, but pretty sure that’s going to have to wait a while. I think I’m just about to pivot back to writing fiction for a bit.

See what I did there? Pivot.  Haha.

(That wasn’t intentional…)

Anyway. Time to upload links for all six of those books for all five sales platforms I have linked from this site. Happy happy, joy joy.