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 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.

Save the World, Grow a Rose

There’s a book I want to write about a grandmother who saves the world by growing roses. It seems like a ridiculous premise, right? What’s interesting about that? Where’s the conflict, the tension, the struggle, the triumph?

But, see, I think sometimes we focus too much on the conflict and triumph. We focus too much on escalation.

Today another disturbed person lashed out and took innocent lives. And I don’t think it’s a coincidence that they did so in an environment of escalating tension and rhetoric. That they did so during a time when people are being encouraged to take sides, to protect what’s “ours”, to see those who aren’t like us as a dangerous other.

It’s so tempting in times like this to lash out. To take the hurt that others cause us and spread that hurt. To receive hate and return hate. They hurt us, let us now hurt them. An eye for an eye, right?

It’s so much more difficult to take hate and turn it into something beautiful. To take the negative energy of others and instead of spreading that negative energy with our reactions or our words, to instead transform that negative energy. To use that awful momentum from an act of evil and somehow use it to produce a thing of beauty.

Because we each have a choice. To spread darkness or light. To push others down or lift others up. We have a choice to take what comes at us and spread it onward or transform it.

I haven’t written that novel yet, because I’m not sure how to write it. And I’m not sure anyone wants to read it. Think how boring or how frustrating it would be to see someone’s world attacked or destroyed and to see them take that pain and heartache and instead of fighting back to dig in the soil, plant the seed, water the soil, pull the weeds, and grow something of beauty in the midst of loss. To see them not fight back, not take an eye for an eye, but instead just…grow a rose.

I’m not sure I could do it if it were me. I’m not sure most of us could. I’m not even sure most of us should. What would that mean. Would you have to let evil run unchecked? What happens to hatred when it’s met with silence? Does it grow? Does it become more powerful? Maybe it withers. And dies. Maybe it tries harder for a time and then dies without fuel to keep it going.

I don’t know.

But I do wonder what the world would look like if each of us took the ugliness we experience and found a way to stop it instead of spreading it. If each of us found a way to transform this pain and loss into something beautiful or productive.

I suspect the world might be better for it. But I’m not sure I can do it…I’m not sure any of us can.

Annoyances

I wrote a post earlier about how furious I was with Amazon for disappearing a large chunk of my paperback sales for the last six weeks. I took it down not because I am okay with what happened, but because I was giving them too much benefit of the doubt. I assumed it was just some mis-reporting on the dashboard glitch that they’d fixed. Nope. Turns out they have disappeared all print sales for my top-selling print title. I emailed about it as soon as I realized the sales were missing and emailed again when I realized the cause but six hours later they haven’t even bothered to acknowledge my emails.

With business partners like Amazon who needs enemies?