A New Release (or Six): Mail Merge

It’s been a busy week. I know better than to do this to myself, but I just released six new titles. The big one is Mail Merge for Beginners, which covers how to create customized letters, envelopes, and mailing labels in Microsoft Word using an Excel-based list of entries.

Mail Merge

And, because it’s a pretty short and sweet guide, it’s only $2.99. So if that’s something you need (I certainly used mail merge back when I was working as a secretary at my dad’s little sign shop), then check it out.

It will also be available in paperback for $7.99. The paperback is up on Amazon now, but not yet linked to the ebook–that should happen in a couple days–but it will come up in search. It will slowly make its way to anywhere else you like to buy paperbacks in the next couple of weeks.

In addition to the mail merge book, I also just released five titles in a series called Easy Word Essentials. These books take specific topics from Word for Beginners and Intermediate Word and present them as standalone topics. They cover text formatting, page formatting, lists, tables, and track changes.

So if any of those topics are of interest and you haven’t already bought the two main Word titles, then those might be worth checking out as well. Each one is $2.99 and the paperbacks are $7.99. Same situation as above, the paperbacks aren’t yet linked on Amazon but can be found with a search and will be soon. They will also make their way to other platforms over the next couple weeks.

Easy Word Essentials

Text Formatting open sansPage Formatting1 Lists2 TablesTrack Changes

 

 

 

And now I can go enjoy my Easter and get back to proofing the next cozy mystery. Those murders don’t solve themselves, you know. 🙂 Happy holiday and/or family time to you all.

A Quick Excel Copy & Paste Trick

It’s been a while since I shared an Excel trick on here and this is one that didn’t make it into the books although I do find it useful.

As a refresher: If you want to copy an entry and paste it into other cells, an easy way to do so is to click on the cell with the information you want to copy, use Ctrl + C, then highlight the cells where you want to paste that information and use Ctrl + V or Enter.

Another option if the cells where you want to copy the information are located next to the cell with the information you want to copy is to highlight the cells you want to copy, left-click on the bottom right corner and drag down or drag to the right.

(If there’s already data in other columns and you want to copy downward you can just double left-click instead of click and drag.)

I tend to use the click and drag option a lot, but it fails me sometimes, especially when I want to copy date information because it gets too clever.

For example, in a lot of my sales tracking spreadsheets I add a column for month and a column for year and then need to copy that down however many sales entries there are for that vendor for that month.

When you click and drag with month and year information, Excel treats the data as a series by default and does the following:

Excel Copy Paste Default

I wanted every entry to be April 2019, but Excel in its wisdom advanced the month and the year by one for each row.

It turns out you can fix that by clicking on the Auto Fill Options image at the bottom right corner and changing it from “Fill Series” to “Copy Cells.”

Auto Fill Options

You then get what I actually wanted:

Excel Copy Paste 2

Another way to copy the exact same value to multiple cells in Excel is using Ctrl+Shift+Enter. Highlight all of the cells that you want to have a specific value, then enter that value in the first cell of the highlighted range, like so:

All Cells Highlighted

Then instead of using Enter, use Shift+Ctrl+Enter. The value you input into the first cell will be copied to all of the highlighted cells.

It only works for one value at a time, though. So in my scenario above I’d have to do the month first and then have to do the year separately.

Both of these tricks work in Excel 2013, which is what I’m working in, so I’d assume they work in all versions of Excel beyond 2013 as well. They may or may not work in prior versions of Excel.

Edited to add that now through June 29, 2019 you can get a 15% discount on any of the ebook versions of the Excel Essentials titles on Barnes & Noble by using promo code BNPEE15.

 

Excel Essentials Now Live

For those who are ready to dive into Excel and move from a beginner level to an advanced intermediate level all at once, Excel Essentials is now live.

This title combines Excel for Beginners, Intermediate Excel, 50 Useful Excel Functions, and 50 More Excel Functions. So with this one book you can basically move from not knowing anything about Excel at all to understanding how to input information, format that information, print that information, use conditional formatting, charts, and pivot tables, as well as learn over one hundred Excel functions.

If that’s not what you need, each of the individual titles are also still available and the even more specific Easy Excel Essentials series of titles are available, too.

Excel Essentials is available for $39.95 in paperback or $19.95 in ebook. (Those are the USD prices) For those who want a Kindle-compatible version, you can find it on my Payhip store. The ebook will not be listed on Amazon.

Excel Essentials 20190222.jpg

A New Release: 50 More Excel Functions

I decided to start the new year off right with a new release.

When I originally wrote 50 Useful Excel Functions I chose those functions from a list of about 125 total functions I thought could be really useful to someone. I didn’t want to write them all up in one book because that’s just too much to handle for the average user in my opinion.

But it wasn’t easy to narrow that list down either, because which functions a user considers most useful will very much depend on why they’re using Excel. I also had to include in that book certain functions just for completeness sake. If I was going to discuss X function then I also really needed to discuss Y and Z functions, too.

Which meant that I was left with about a hundred functions that I didn’t cover in the first book but that I figured a certain number of users might want to know about.

Well, now I’ve covered another batch of them in 50 More Excel Functions.

This one really digs into some of the date and time functions and discusses the quirks of how Excel handles dates, at least one of which threw me a nasty surprise on a work project a few years ago. Hint: Don’t work with really old dates in Excel, it doesn’t turn out well.

Anyway. Happy new year. Enjoy.

50 More Excel Functions open sans

 

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.