An Interesting Excel Question

Today I received an interesting Excel question from a user and thought I’d share the solution I came up with here.

Here was the question: In excel, what is the formula for taking a cost times a 30% margin then rounding it up to the next $x.x5 or $x.x9     (example: $3.45 x 30% margin = $4.93, roundup to $4.95) or (example: $3.48 x 30% margin = $4.97, roundup to $4.99)

At first, I thought, there’s no easy way to do this. And there isn’t. A quick search for functions that would do this specific task resulted in nothing.

But then I remembered that I’d covered the CHOOSE function in Excel 2019 Formulas & Functions and that it could be used for this. As it turns out, it still wasn’t easy to do, but it is doable.

(And there may very well be a much simpler solution to the problem, but this seemed to work for me, so it’s what I went with.)

Here’s what I ultimately came up with:

=ROUND(A1/0.7,2)+IF(TRUNC(A1/0.7)-A1/0.7=0,0.05,IF(ROUND(A1/0.7,1)-ROUND(A1/0.7,2)=0,0.05,CHOOSE(RIGHT(ROUND(A1/0.7,2)),0.04,0.03,0.02,0.01,0,0.03,0.02,0.01,0)))

The first part of that formula, =ROUND(A1/0.7,2) is calculating the marked-up price on a value in Cell A1. That gives the $4.93 or the $4.97 value.

The second part of that formula is where it gets interesting. Let’s drop out the IF functions for now and look at the CHOOSE function:

CHOOSE(RIGHT(ROUND(A1/0.7,2)),0.04,0.03,0.02,0.01,0,0.03,0.02,0.01,0)))

It’s best to work from the inside out.

So we start with the (ROUND(A1/0.7,2) portion which is just giving us the number we’re working with.

Next is the RIGHT function. What that’s doing is taking the right-most digit of our number. So in the examples above that would either be the 3 of $4.93 or the 7 of $4.97.

Finally, we have the CHOOSE function which basically provides a different result depending on the value you give it. So in this case a 1 returns a value of .04. A 2 returns a value of .03, etc. Once we cross the 5 mark we have to switch things up a bit so that 6, 7, 8, and 9 will result in values that end in a 9 instead of values that end in a 5.

That value gets added to our original calculated value to create a result that ends in either a 5 or a 9.

But because I used the RIGHT function we have a problem. Any value that’s a whole number, like $9.00, isn’t going to calculate properly because RIGHT will pull in a value of 9 not 0. And any value like $7.30, $8.20, etc. will also not work properly because RIGHT will pull a 3 or a 2 or whatever the next actual digit is instead of the zero.

We can solve that, though, with IF functions. Namely,

IF(TRUNC(A1/0.7)-A1/0.7=0,0.05,

and

IF(ROUND(A1/0.7,1)-ROUND(A1/0.7,2)=0,0.05,

IF(TRUNC(A1/0.7)-A1/0.7=0,0.05 basically says that if it’s a whole number then add .05 to it. And IF(ROUND(A1/0.7,1)-ROUND(A1/0.7,2)=0,0.05 says that if the rounded value with one digit is the same as the rounded value with two digits, then also add .05.

A little messy, but it solves those two issues.

And I think all of it taken together answers solves the issue.

7 New Releases

I had no intention of announcing the release of seven new books at one time, but, well, I forgot to announce the Word 2019 releases and then it happened that the Easy Excel 2019 proofs arrived at the same time as the Excel 2019 Formulas and Functions Study Guide was finished and here we are.

So, if you have an interest in Microsoft Word or Microsoft Excel 2019, read on. If not, you can skip this.

First up, the Microsoft Word 2019 versions of Word for Beginners and Intermediate Word are now out. Once more, if you bought the originals, no need to buy these ones, too. I don’t think there’s anything so drastically different between them that you’d need the new ones. I think I may have moved one item from the intermediate level to the beginner level but that’s about it.

You can click on the images below to be taken to the store of your choice.

Okay. Next up. The Easy Excel Essentials 2019 books are now out. These are intermediate-level titles that focus on one specific topic: PivotTables, Charts, Conditional Formatting, and The IF Functions.

In this case, I’d say there are substantial differences in the IF Functions title because Excel 2019 includes IFS, MAXIFS, and MINIFS which didn’t exist before. So if you’re looking forward and don’t need to worry about backwards compatibility, this is the book you want. If you work with a lot of different Excel users and so can’t risk using the latest and greatest, then stick with the old version. Excel 2019 Charts also covers histograms which was not covered in the original title and Excel 2019 Conditional Formatting is expanded a bit.

Also, the print versions of these books have very different formatting. I decided this time around to go with the standard computer book size formatting for the print versions so these are all 7.5″ x 9.25″. They also have larger text than the original series so may be better for those who struggle with small type.

The astute observer may also notice that the 2019 books do not include Formatting or Printing which were part of the original series. Mostly that’s because even though I meant the titles in this series to be bought as one-offs a lot of people buy the entire series at once so I wanted to focus in more this time around. Really, if you need formatting and printing, just buy Excel 2019 Beginner. It will cost you less and you will learn more.

Okay. Final release to announce, Excel 2019 Formulas and Functions Study Guide. This is the equivalent to the quiz books for the Excel Essentials series. Basically, it takes the content of Excel 2019 Formulas and Functions and walks through that content in a question and answer format. There are also ten bonus exercises at the end to test putting the functions to use in real-world scenarios.

Once more, if you already bought and worked with the original 50 Useful Excel Functions and 50 More Excel Functions and their associated quiz books, probably no need to buy this one. It does cover some new functions like TEXTJOIN and IFS but you can probably fill in the gaps from within Excel itself without needing to buy a new book for it.

(Not that I object to making money, so buy it if you want. Just saying you don’t have to.)

Errata – 50 Useful Fn Quiz Book

This last week I was working on the Excel 2019 Formulas & Functions Study Guide and it led me to review the 50 Useful Excel Functions Quiz Book and I noticed a few errors.

I submitted updated versions of the books today so anyone from tomorrow forward won’t see them, but for anyone who already owns that book (or the Excel Essentials Quiz Book), I wanted to mention the fixes I made.

(I think the book might be being used in a college class right now, so felt it was especially important to mention the updates.)

1. HOW FORMULAS AND FUNCTIONS WORK QUIZ, Question 7F and 8F. Those should be written as =(4+3)*2 and =(E1+A1)*C1, respectively or else the answer doesn’t work.

2. BASIC COUNT FUNCTIONS QUIZ ANSWERS, Question 11. Ignore the last sentence of the answer because there is actually overlap between COUNTBLANK and COUNTA.

3. VLOOKUP QUIZ ANSWERS, Question 5. The answer is actually one because you can use a VLOOKUP to look in the exact same column to find the closest answer to your lookup value.

New Year, New Releases

The Excel Essentials 2019 series is out! That consists of three titles, Excel 2019 Beginner, Excel 2019 Intermediate, and Excel 2019 Formulas & Functions.



I’m going to take a moment to talk about them and then I’ll dive in on some thoughts for the writer folks who follow this blog.

So, how do these differ from the Excel Essentials series? If you’ve already read Excel for Beginners, Intermediate Excel, 50 Useful Excel Functions, and 50 More Excel Functions do you need to buy these, too?

The answer is no. These books are written specifically for anyone using Excel 2019 but 97% of what I talk about in the two series remains unchanged so if you already read the first series you’re fine.

In the formulas & functions book I do cover a few new functions, IFS and TEXTJOIN being the two main ones. MINIFS and MAXIFS as well. But in the prior series I covered nested IF functions and CONCATENATE which were the old way to accomplish the same thing as IFS and TEXTJOIN. And the older functions are still better choices if backwards compatibility is an issue.

Which is why I continue to recommend the Excel Essentials books for anyone using an older version of Excel or who needs to worry about structuring things so they work for others using older versions of Excel.

I basically came out with these books because I just upgraded computers which meant upgrading my Office version to 2019 so I had access to it and also because I know there are users out there who want a book focused on their particular version of Excel so why not give it to them now that I could.


Which is the perfect segue (an interesting word because I want spell it very differently based on the way it’s pronounced) to talking about this from the writer perspective.

Whether you write fiction or non-fiction you always have to think about self-cannibalization at some point if you’re going to publish more than one title.

On the fiction side writers do this when they release bundles. If I have a bundle of books 1 through 3 and books 1 through 3 available on a standalone basis I should expect that some readers will buy the bundle instead of books 1 through 3 standalone. Which means that every sale of the bundle is a sale I don’t get of books 1, 2, and 3.

But it can make sense to do so anyway, because there are readers who are bundle readers who won’t buy a book standalone and it’s also often a way to reach readers who won’t pay as much without having to discount the standalone titles. So you broaden your potential audience in two ways.

The drawback is on a site like Amazon that is so rankings-driven it can decrease overall visibility. Maybe. Because sometimes getting a Bookbub promo is easier with a bundle which can then increase visibility. (Of course at that point you’re selling at high volume but low per-unit profit, but that trade off can make a lot of sense depending on when you do it. My general inclination is to price low only when I have somewhere more expensive for readers to go after that because it’s not easy to make a living on 35 cents a sale.)

In non-fiction there are any number of ways to do this as well.

One is an updated edition of a book. Most readers if there’s a 2010 and a 2020 edition of a book will buy the 2020 edition assuming it’s the “better” edition so publishing a new edition often means no longer getting sales of the editions.

If someone takes another pass at the material you assume they will find better ways to say what they were saying the first time around and update the book for any changes over time.

(Although I will say with cookbooks this isn’t always true. I have Better Homes & Gardens cookbooks spanning thirty years and some of the older recipes are the better-tasting ones because they weren’t trying to be heart-healthy. Although, let’s just take a moment to be glad that 1970’s entertaining suggestions stayed in the 70’s. Hanging bananas off of a centerpiece is an idea no one should have ever had, ever.)

Getting back to the point.

With non-fiction other ways I’ve cannibalized my own sales is through bundles. For example, I have the Excel Essentials title which is the four Excel books from the original series combined into one title.

(Even though it’s a discount over the four individual titles, the individual titles still sell much better, probably because the initial price point seems daunting to someone who hasn’t read my books yet.)

I also have the Easy Excel Essentials books which are extracted from the main series titles and focus on specific topics, like Pivot Tables.

They’re less economical for people to buy if they buy them all but people do still buy them either because they only care about one specific topic (Pivot Tables or Conditional Formatting) or because the price point seems more reasonable to them. They’d rather buy six books for $3 each than buy three books for $5-$6 each even if there’s less overall content in the six books.

Of course, another reason to release new titles has nothing to do with sales, but instead has to do with visibility.

For example, the newly-available-to-everyone AMS Sponsored Brand ads work best with three or more titles. So I went ahead and released Access Essentials so that I’d have three books on Access that I could advertise via one of those ads. I didn’t actually expect high sales on that title, but it gave me another advertising option so it was worth it.

And, as fiction authors who focus on Amazon sales know, there is value in being in the new release charts. (Although that’s only self-cannibalization when it’s an omnibus or bundle release, but that can make people realize they missed book three in that series and go buy it.)

Anyway. It’s something to think about if you’re a slower writer and trying to figure out what you can do. Think about new formats, bundles, etc.

But I don’t recommend new editions unless for this purpose. (These three books took me over a hundred hours to create and with novels or short stories I’ve redone it took as long as writing a new one and probably wasn’t such a vast improvement it was worth it.)

Also, I highly recommend having a release of some sort in January because it’s a nice, easy way to hit at least one New Year’s resolution. 🙂

Back To School Sale

Since the world is still on fire and many people are dealing with back to school craziness, I thought I’d do what I could and put a bunch of school-friendly titles on sale for a couple weeks. Each of the below titles is on sale for $2.99 USD.

Click on any of the images below to be taken to a Books2Read page for that title that has all the stores listed. (If you’re already set up with them you’ll go straight to your chosen store.) Or you can use any of the store pages on the right-hand side here and get to the books that way.

Excel Essentials

Excel for Beginners open sans boldv2 Intermediate Excel Open Sans50 Excel Functions open sans

50 More Excel Functions open sans

 

 

 

 

Word Essentials

Word for Beginners open sansIntermediate Word open sans

 

 

 

 

PowerPoint Essentials

PowerPoint-for-Beginners-Generic    Intermediate-PowerPoint-Generic

 

 

 

Access Essentials

Access for Beginners 20200202Intermediate Access 20200202

 

 

 

 

Data Principles & Budgeting

Data Principles for BeginnersBudgeting for Beginners open sansExcel for Budgeting open sans

 

 

 

 

Keep in mind this is only for the ebook versions, but all of these titles also have paperback versions and most have hardcover versions as well that are, I think, reasonably priced.

And some of these are very good deals indeed, because I was being lazy so I priced everything at $2.99 which means that Access for Beginners, for example, which is usually $7.99 is on sale for the same price as Excel for Beginners, which is normally $4.99. (USD. But equivalent discounts in your local currency.)

Enjoy.

 

 

Two Paperback Versions on Amazon

Just an FYI for anyone looking for my books. Right now Amazon seems to be showing a delay of five days to print and ship books that they distribute on my behalf. But most of my books also have another version distributed via IngramSpark that will ship sooner. The IngramSpark listing is rarely the primary listing, so you have to go looking for it.

Here’s how. This is the main page for Excel for Beginners. You can see on the right where it says it will normally ship within five days:

Main Amazon Page

Right above where all the prices are listed for the different formats it says “See all formats and editions.”

Click on that and you get another screen. There’s a little > next to the paperback listing. Click on that and it will become a downward pointing arrow instead and you’ll see two listings for the paperback.

E4B Paperback options

The May 2019 version is the version that’s coming from IngramSpark. If you click on “Paperback, May 9, 2019” you will be taken to that version’s listing.

And voila, there are six left in stock and you can get a copy in your hands within as little as two days.

IS Version Listing

It will also have text on the spine which the Amazon version doesn’t.

Amazon does allow resellers to do weird things on their site so I always approach book listings there with a certain amount of caution. But for any listing of my book that’s coming from IngramSpark you can scroll down to product details and if it’s mine you should see a publisher name of M.L. Humphrey and an ISBN-13 that starts with 978-1950902 and then three numbers that are specific to that particular book, in this case 002.

Product Details

And, of course, you can also order the books from other fine retailers that carry print books like Barnes & Noble.

 

 

 

 

New Release: Microsoft Office for Beginners

Just a quick note that buyers can now get Excel for Beginners, Word for Beginners, and PowerPoint for Beginners in one book, Microsoft Office for Beginners. This one is geared towards those who are looking to get a basis in all three program at once. It gives a bit of a price discount compared to buying the individual titles by themselves.

The ebook version ($9.99 USD) is already available everywhere. The paperback version ($29.95 USD) will be available within the next day or so. Click on the image below to choose the store you want or on one of the store tabs on the right-hand side.

Microsoft Office for Beginners4

Reading is My Refuge

My last two years at Stanford were two of the hardest years of my life. I had decided to triple major–which included a major I didn’t even start until my junior year–and I was also working more than full time to pay for room and board. So 19 or 20 units each quarter plus 50 hours of work a week. Oh, and I was commuting from Sunnyvale my junior year so add in a real drive each day as well.

It may seem strange, but what got me through it was reading. Mostly fiction books, but some non-fiction too. (That was when Guns, Germs, and Steel came out and I absolutely loved that book.) That first finals week I think I read three fantasy novels while studying for and taking all of my exams.

I was lucky to work in a bookstore and so have free access to books. (It was a company program, I wasn’t cheating in any way.) But even if I hadn’t, I would’ve worked an extra hour a week to be able to buy books, they were that important to me.

Which is why yesterday I dropped the ebook prices on about a dozen different titles. Since I know there are people like me out there who are desperately in need of a distraction right now, I thought I’d help out a bit.

We’re all going to need to get away and disconnect to make it through this. For some that will be video games or TV shows or movies. For others it will be books.

So…If you’re a book person, here’s what I’ve put on sale. It’s a very eclectic mix as you’ll see. Hopefully there’s something in there for everyone:

Just click on the image to be taken to the Books2Read page which should have links for all retailers. Or you can just go to your favorite retailer and look the titles up. All except for Erelia are available everywhere and most libraries should also be able to get them.

Non-Fiction ($2.99 USD each)

Excel for Beginners open sans boldv2

Excel for Beginners: A guide to Microsoft Excel for those who need to master the basics.

 

 

Budgeting for Beginners open sans

Budgeting for Beginners: A book that will teach you how to figure out where you are financially, judge what that means, and give tips for how to improve. Especially helpful right now for those who are finding themselves without a steady paycheck, because it covers how to approach irregular income like that. (Also available in audio as the Juggling Your Finances Starter Kit.)

Quick--Easy-Cooking-for-One-KindleQuick & Easy Cooking for One: Exactly what it says. A guide to cooking for yourself for the absolute beginner. More concept-based than step-by-step, but it does include recipes.

 

 

Writing for Beginners open sans

Writing for Beginners: An overview of what a beginning writer should know to get started. Includes discussions of point of view, tense, as well as agents and publishing paths. (Also available in audio under the title The Beginning Writer’s Guide to What You Should Know.)

 

Dont Be a Douchebag PC version 20160803v10Don’t Be a Douchebag: Online Dating Advice I Wish Men Would Take: A snarky guide to online dating for men who aren’t doing so well at it. (Also available in audio. Some retailers may have a different cover.)

 

 

Fiction:

Riders-Revenge-The-Complete-Trilogy-GenericThe Rider’s Revenge Trilogy: ($4.99 USD) A feminist YA fantasy adventure trilogy about a young girl who sets out to avenge her father and finds herself caught up in much bigger issues.

 

 

Erelia blue flame 20151222v5Erelia: (Available on Amazon Only, $2.99 USD and in KU) A dystopian utopia. Life seems perfect on the surface, but the reader sees just what horrible actions create that perfection. Also has a pandemic subplot. (I had unpublished this one just because I thought it needed a sequel and I wasn’t sure when I’d write that sequel, so be forewarned.)

 

A-Dead-Man-and-Doggie-Delights-KindleA Dead Man and Doggie Delights: (99 cents) First in what will soon be a six-book cozy mystery series set in the Colorado mountains. For lovers of Newfoundland dogs, Colorado, and quirky characters who like a little murder on the side. (Book 2, A Crazy Cat Lady and Canine Crunchies is also reduced to $2.99 USD.)

 

Something-Worth-Having-KindleSomething Worth Having ($2.99 USD): Contemporary romance bordering on women’s fiction. About a woman facing a breast cancer diagnosis who goes on a road trip with a man she is absolutely not allowed to fall in love with. (A related but standalone title, Something Gained, is also just $2.99 right now.

 

Excel, Word, and PowerPoint Essentials

Excel Essentials 20190222  Word-Essentials-Kindle  PowerPoint-Essentials-Kindle

I published Excel Essentials, the collection of the four individual titles in the Excel Essentials series a while back. At the time I didn’t publish the ebook version on Amazon, but that is now available on Amazon for anyone interested.

And because I am also done with the Word Essentials series and the PowerPoint Essentials series at this point, those too are now available as standalone titles. Note that Word Essentials and PowerPoint Essentials only contain two titles each so are that much less expensive than Excel Essentials which contains four titles.

Also, for at least the next week or so Word Essentials and PowerPoint Essentials will not be available on Apple but they will be there soon. (I’m changing how I distribute my books there and it takes a little longer than I’d expected.)

The books are all available in ebook, paperback, and hard cover but it may take a few days for them to reach all the stores.

For those of you who already own the individual titles (Word for Beginners, Intermediate Word, PowerPoint for Beginners, Intermediate PowerPoint, etc.) there is no new material in these books, it’s just another way to provide the information for those who know they want it all at the time of initial purchase.

As of now I’m done with writing new material on Microsoft Office, but if there’s something specific you want to see that I didn’t cover, let me know and if I think it’s within my skillset I’ll work on it. That’s actually how Excel for Budgeting and Mail Merge for Beginners both came to exist.

 

Excel, Word, and PPT Books Now in Hard Cover

Just a quick announcement to let you know that Excel for Beginners, Intermediate Excel, 50 Useful Excel Functions, 50 More Excel Functions, Excel Essentials, Word for Beginners, Intermediate Word, and PowerPoint for Beginners are all now available in a hard cover version.

IMG_4875 - Copy cropped

I have to say, I’m pretty excited about this one because the books feel much more substantial in hard cover than paperback. (That Excel Essentials one which combines the other four Excel titles into one book is a behemoth. It’s one inch thick and weighs two pounds! Who knew I had so much to say about Excel.)

The covers are case laminate so there might be a little denting at the edges like you can see on 50 More Excel Functions in the photo, but overall I was pretty impressed with them. And keep in mind with the skinnier ones that the spine text might be slightly off center because of print-on-demand variances, but it will be there on all of them.

They should be available on Amazon (here’s my author page for the U.S.) as well as Barnes & Noble and any other location where you can order print books.