Some Interesting Numbers

It’s an absolute fact that all self-publishing journeys are unique. We can talk about what the average writer needs to do to be successful at this, but at the end of the day people will find success by following a number of different paths and those who follow the tried and true path aren’t necessarily guaranteed to succeed.

Today I was crunching some numbers. I’ve been feeling a purge of books coming on. I have eight pen names I’ve published under but I don’t actively promote everything I’ve written and there are some names or titles that I have no intention of going back to.

The question is, do you unpublish titles like that or do you leave them up for the occasional sale they’ll generate? A hundred bucks a month is still a hundred bucks a month. I go back and forth on it. And I’m not sure what I’ll do this time around.

But while I was in there crunching numbers I did something else I’ve never really done, which was look at sales across each of my series across different platforms. And what’s kind of crazy is that for 2018 I have made more in paperback sales than I have in ebook sales. By about $600.

I knew that I’d made more in paperback sales for one of my series, but it turns out I’ve made more in paperback sales for four of them. And I had one series that’s made more in audio than ebook this year.  I also have one series that sells better on D2D than Amazon and another two that sell better on Nook and Kobo, respectively, than Amazon.

I also made more in audio on both ACX and through Authors Republic than I made in ebook sales on D2D, Google, Kobo, or Nook. Not combined, individually. And that’s with most of my audio being for non-fiction titles. (Still not going to do anymore audio at this point. I simply don’t sell in high enough numbers to justify it.)

So what’s that mean for anyone who isn’t me?

It means that there are lots of ways you can do this gig. And just because one way is working for most people that doesn’t mean you have to follow the same path as them. Find one that works for you and is sustainable for you and block out the voices that tell you there’s only one way to do this “right.”

And don’t discount those other formats or other sales channels. You never know what will work for a particular title until you try it.

New Release: 50 Useful Excel Functions

And it’s done. My first book of 2018 has been published. 50 Useful Excel Functions is just what it sounds like, a guide to the fifty Excel functions I find most useful.

It was a weird experience writing this one because at first I was going to do fifty functions and then I decided maybe I’d just do 25 and have four other guides for specific types of functions like Date & Time. Then I started writing it and realized 25 functions didn’t feel like enough content. But by the time I finished it 50 seemed like I was stretching for the last couple even though I’d started with a list of about 125 I thought were useful to some extent.

Anyway. It’s done now and I’m pleased with it and think people who want to learn about Excel functions will find use in it .

It does bring up an interesting point for self-publishers/writers.

Because one of the challenges with writing non-fiction is what to do when a title sells well. If you’ve said what you thought you needed to say what else is there to do with it? Write a new version hoping people will buy it again? Eh. Write another title that basically says the same thing over again? Ugh. (Not for me.)

Fortunately, when I was working on the video courses for Excel for Beginners and Intermediate Excel I realized that there were a few ways I could deliver more value and extend on what I’d covered in those two books. 50 Excel Functions is the first of those, but I have a few more ideas in the pipeline. So if nothing else the video courses were good for that.

Oh, and speaking of Excel for Beginners…It had a nice little #1 Bestseller tag on Amazon today. Might not be the first time but it’s the first time I’ve noticed it, so that was cool:

Excel 4 Beg #1 Bestseller Tag

Conspiracy Theories

The indie self-publishing world needs to take some deep breaths. The blows have been coming fast and hard lately. Kindle Worlds is gone, people lost half their KU page reads for April, RT convention is gone, there was that whole trademark thing…

Everyone wants to understand. They want an explanation. They want it to make sense. And it doesn’t. At least not given what we know.

So then the conspiracy theories start…

Like the one that says that running AMS is why people lost page reads.

Or the one that says that somehow Amazon used Pronoun as some sort of data gathering effort and then turned on Pronoun and merged with Draft2Digital? (By the way, person who said that, that word does not mean what you think it does. A merger is a very different thing from a distribution agreement. VERY. Also, Amazon doesn’t need Pronoun’s data. I think you got that backwards.)

So let’s all take a deep, deep breath. In…..Out…..

Here’s the deal:

Self-publishing is chaotic. It’s one of the only areas in my life where I’ve ever felt the need for the serenity prayer. You know the one that basically says grant me the strength to do what I can and ignore the rest?

If you’re going to stick with self-publishing long-term, you need that. Put it on your wall. And every time one of these situations arises ask yourself: “Is this something I can do anything about?” and “Is this something I should do something about?”

If it’s yes, then act. If it’s no, then move on. Usually the answer to one or both of those questions will be no.

Also, if someone makes some sort of insane claim about what’s going on, step back. Think Occam’s Razor. The simplest solution is usually the correct one.

It’s not some vast conspiracy. There is not some masterplan bent on your personal destruction. Usually what’s happening is the outcome of a lot of people or companies making decisions that are best for them without thinking about the impact on anyone else. It’s that simple.

Yes, there are cutthroat assholes out there. At the individual level and the corporate level. For them it’s rarely personal. They make decisions based on what will earn them the most money. So once you know that about them, act accordingly. Don’t feel betrayed that they acted within their nature. They are what they are.

One more point.

I was about to respond to the umpteenth post I’d seen recently about how everyone who had page reads stripped last month seems to have been running AMS. (Not necessarily true. Just like it wasn’t true that they were all running Bookbubs last year when page reads precipitously dropped for some people.)

I was going to make a comment about how it’s easy to look at a small number of people who had X happen to them, see that they all had Y in common, and conclude that Y was the cause, while failing to see that there are a hundred times as many people who also had Y but where nothing happened to them.

But then I asked myself whether I should be spending my time on the internet trying to convince strangers to calm down and stop seeing conspiracies around every corner.

No, I should not. I should be writing. I have a book I need to finish editing. So I can publish it. And advertise it. With AMS.

AMS and Writing

I had an interesting conversation over the weekend about AMS Ads for Authors and writing in general. And one of the points we discussed in that conversation is something I specifically call out in the AMS video course (now renamed Easy AMS Ads), but maybe not as strongly in the book, so I thought it was worth addressing here.

Which is that: as a self-published author looking to make money off of your writing (lots of assumptions in that sentence, but that’s who I’m talking to here), you need to keep producing new material.

Yes, you should market what you’ve already done. (And I am arguably not as good at that as I should be which is why I thank my lucky stars for AMS because I can run them full-time and with maybe fifteen minutes a day spent on them.)

But more importantly, you need to feed your readers. You need to give them new material. Otherwise you’re spending all this money to acquire customers (readers) and then you’re losing them because you have nothing more to offer them.

The most effective use of advertising is when you can bring people in the door and then keep them there and buying more from you. (See Amanda M. Lee for a good example.) Now, not everyone can write that fast, but if you’re spending all your time advertising what you’ve already written to the detriment of producing new material that is not a successful long-term strategy.

And what’s even more important about this is that AMS are an Amazon ad product. Meaning they favor new and shiny and already successful.

Now don’t get me wrong. I’m running ads on books I published in 2013, 2014, 2015. But my most successful ad the last six months was on a book published September 2017. My second most successful, same thing. There’s a reason for that. Amazon is the reason.

So writing one or two books and then running AMS on them to the expense of everything else will perhaps do really well for you the first six months or year or maybe even two years you run the ads. But after that you need something new. You need new material to throw at the ads.

(And you need new material for your fans, too. Don’t forget them.)

Never ever lose sight of the fact that new material is what will keep the lights on. The JK Rowlings of the world who have a series selling well a decade after release without new material are the rarities. (And even she has had new stuff come out related to the original HP books. The movies. A play. A book of the play. The website that tells you your house and your patronus.)

Always be sure that whatever strategy you take to promoting your books doesn’t keep you from producing new material.

Speaking of. I have a book that’s waiting on final edits.

Why Let Someone Control Your Dreams?

I realize that I am wired differently than most of the world so I’m probably not seeing something about this, but it fascinates me the number of authors I see who are broken-hearted or angry that they can’t find an agent or publisher for their work.

Today’s example was someone who worked for seven years to craft a story they clearly cared a great deal about. They queried it over a hundred times, didn’t find an agent, and now are done with writing.

And they’re broken because of this. The pain they’re feeling is clear.

They’re not alone.

I’ve seen author after author talk about how they were worried that this book they’d written that mattered so much to them was never going to be published. Or how they’ve moved on to more commercial books but that book of their heart is still sitting in the drawer waiting for the day when someone agrees to buy it.

I don’t get it. Because if you loved that book that much. If you spent hundreds, perhaps thousands, of hours writing it. If it physically hurts you that no one else will have a chance to see this book, then why not publish it yourself? A decade ago maybe that wasn’t easy to do.

But today? If you’ve put seven years into writing that book of the heart, you can put three months into getting it out there. If you’re broke it takes more time because you’re going to have to do it yourself, but it can be done. Anything you need to do to self-publish a book you can learn.

So if you’re one of those people, stop letting a very small group of people keep you from sharing your book with the world.

Now, will it sell?

Perhaps not. And that’s not on you.

It could be amazing and wonderful and just get lost in the sea of other books. And that may hurt, too.

But if you cared that much about your book, is how many copies it sells what really matters? Won’t holding that book in your hands, knowing it’s a real, physical object that can sit on your shelf be better than having it be lost forever?

Put it in print. Order twenty copies. Give them to all your friends.

Give them to the woman who’s nice to you at the supermarket.

If this is about love and passion, then put it out in ebook, price it at free, and let it go. (But don’t think that just because you do that hundreds of thousands of people will buy it, that’s not how it works.)

All I’m saying is if this means this much to you don’t stop just because one path didn’t work.

Far too many people in this world spend far too much time and energy trying to be accepted by people who will never accept them no matter what they do. Stop being one of those people.

Take control back. If someone won’t publish your book for you, publish it yourself. You’ve come this far already, don’t stop now.

Library Reminder

Just a quick reminder to folks that all of the titles listed on this site are available wide, which means they’re available through Overdrive which is a company who supplies ebooks to libraries and through CreateSpace who sells paperbacks to libraries.

So if you ever have an interest in one of my books but can’t afford it, be sure to check with your local library about ordering in either a paperback copy or an ebook copy. I fully, wholeheartedly support people reading through their library.

Using Excel for an Optimization Problem

The astute reader will note that I didn’t (and won’t) write a book called Advanced Excel. Not because there aren’t a number of advanced Excel topics I could’ve written about but more because that becomes the realm of people who really get in the weeds on Excel and I’d also have to find a way to make it comprehensive and that just seemed a little too…much.

But yesterday on Facebook a friend of mine asked for Excel help so I thought I’d share the question and what I came up with here.

Here’s the scenario she gave: It’s career day. You have fifty students. Ten lecturers. There are four sessions, so each student can attend up to four lectures. Students have provided a ranking of their preferences from 1 to 10 with 1 being their top choice. Is there a way to have Excel tell you which students should be paired with which lecturers?

Short answer: Yes. Slightly longer answer: Not in the basic version of Excel. At least not at the scale we’re talking about here. (It can handle up to 200 decision variables but what I gave you above would have 500.)

So if you actually wanted to do this you’d have to get access to a more robust version of Solver. But you can use Excel to build a mini version of the problem.

I used four students and eight lecturers and rigged my data so that I knew there’d be a solution.

Here’s how it goes.

Start with your student data:

Student Data Data Optimization

Here you can see I’ve built a table with one student per row and their ranking of each lecturer across the different columns. (See how I rigged it so that two students want the first four lecturers and the other two want the last four?)

Next, create a “decision grid”. It’s the exact same as the first table except instead of student rankings all the values in the table are set to 1. (Could be set to zero, too. Either way works.)

Decision Grid Data Optimization

You also want to have the total lectures given by each lecturer (the last row) and the total lectures attended by each student (Column V). This is because these will have constraints that need to be met for our final solution.

Finally, create an “outcome grid” which takes the student ranking in the first grid and multiplies it by the value in the second grid. So if you put 1s in the second grid this will look just like the first grid, but it’s using a formula.

Outcome grid data optimization

At the end of the outcome grid you want a total value for each student (Column AE) and then at the bottom of that column of values you want the average of those student scores.

Now, to do this next part you need to enable Solver, which comes installed in Excel but isn’t automatically activated. Go to File->Options->Add-Ins and at the bottom where it says Manage Excel Add-Ins click on Go. This will bring up the Add-Ins dialogue box. Click the box for Solver Add-In and say OK.

Once you do that you will have the Solver option in the Analysis section of the Data tab. Click on it.

This will bring up the Solver Parameters dialogue box.

solver screen

Here’s where all the fun happens. In this case we want to minimize the average student score. So we’re minimizing the value in Cell AE7 which is where we have the average student score after they’ve been assigned to their lecturer. (This is because in this scenario 1 is the best outcome, 10 is the worst, so the lower the score per student the better.) So our Set Objective is Min AE7.

To do this, we need Excel to say yes/no to which lectures a student will attend. That is done by adjusting the 1s we placed in Cells N2:U5 to either be 1’s (for yes, go to that lecture) or 0’s (for no, don’t go to that lecture). That’s what we specify in the “By Changing Variable Cells” box. When Excel solves the problem it will do that for us.

But Excel needs to know our constraints. For example, that students can only attend 4 lectures. That’s why we tell it V2:V5 must equal 4. (Only make four entries per student equal to 1.)

And we need to make sure that Excel doesn’t do something wacky like decide you can attend half a lecture. So we have to say N2:U5 must be binary. (That means an integer that is either 1 or 0.)

And in this scenario, I had to limit the number of students each lecturer gave a lecture to to 2 so there could be a possible solution. (In the real life example this would be a minimum of number of students per session times the number of sessions and a maximum number of students per session times the number of sessions.) That’s the N6:U6>=2 part.

Finally, I wanted each student to at least be marginally happy with the outcome so I made it so that AE2:AE5 were all less than 12. (We don’t want one kid having a terrible outcome while everyone else has a good outcome.)

And that was it. Then you just tell Excel to solve it. Excel will run scenarios until it finds one that meets all of those criteria. (Or it will tell you it doesn’t have a solution which happened a few times to me when I was building this because I’d built it so it wasn’t solvable.)

If you let it implement its solution, you get something like this for that second grid:

Solver solution

In this case, each student attends four lectures, each lecturer lectures to two students, and (you can’t see it) all students have a score of 10.

As you can see, your constraints are crucial on something like this. And you really need to look at the outcome when it’s all done and see if makes sense and to confirm you didn’t miss some crucial constraint or set it wrong. (If that happens, change it, reset the grid, and run the solver again.)

Also, the model type you choose can have an impact. (It tells you when to use each type but I just try each one to see which one works.)

Now, one final caution. Solver will stop on the first feasible solution. I originally had student scores set to a possible value of 20 instead of 12 and it gave me a solution that worked, but wasn’t the ideal solution you see above.

Anyway. This is why I didn’t do much writing yesterday, because, sadly enough, I find this kind of thing fun so I was happy to set aside my current project to puzzle it out. And I thought it might be fun to see for those looking for more advanced uses for Excel.