Using a progressive-pricing array formula in Excel to create proofreading project fees
Here's how to create ballpark prices for proofreading and copyediting using Excel.
There have been some interesting discussions about pricing models in the online editorial community recently.
If you prefer a per-word model, then you may like to consider using a progressive-pricing array formula. It’s not the only way of doing things, certainly, but it’s something I’ve tested and am currently working with. I like this model because it incorporates economies of scale.
Before I explain how the progressive-pricing array works, a very quick word on price presentation versus determination.
Price presentation versus determination
Price presentation and determination are two different things.
Economies of scale
When a proofreader is working on larger projects, there are economies of scale. I’m currently working with an independent fiction author on nine books (which I’m proofreading over a five-month period).
Most of the projects are between 70,000 and 80,000 words in length; a couple are around the 50,000-word mark; and one is a short story with just over 10,000 words. All of the books feature the same central protagonist and a small cast of supporting characters.
The serial nature of the content, the reappearance of key characters, and the concentration of action in predominantly one fictitious location all serve to save me time as I move through each book. This means:
Even so, the average number of words proofread per hour was fewer for the 10K-word short story than for the previous 70K-word novel. And in the first few hours of working on the the 70K-word novel, I proofread fewer words per hour than was the case in the hours that followed.
That’s because, even with all the benefits of working on a series, each book still needs a certain amount of ‘stuff’ done to it in its own right:
If we take the series element out of the equation, and compare the proofreading of two books in a similar genre for two separate authors, the impact of project length for the proofreader can become even more stark.
Consequently, I want to price the 30K-word novella differently from the 100K-word tome. It’s for this reason that while I like to build my quotations on a per-word basis, I don’t want something as straightforward as a £6, £8 or £10 per 1,000 words model. Instead, I want something that respects the economies of scale that come with larger projects. This is where the array comes into its own.
How does a progressive-pricing array formula work?
An array formula can look at a number (a word count, in our case) and then, based on a set of ranges that we’ve provided, price those ranges accordingly. Here’s a very basic example. You might set up your array such that the following are true:
This would result in the following quotes:
(1) If you were asked to provide a quotation for proofreading a 2,000-word article, the price would be £50 (£25 per 1000 words).
(2) If you were asked to quote for a 10,000-word short story, the price would be £175. This is based on:
The average price per 1,000 words works out at £17.50.
(3) If you were asked to quote for a 70,000-word book, the price would be £575. This is based on:
The average price per 1,000 words works out at £8.21 and reflects the economies of scale that the proofreader will be able to benefit from because of the size of the book.
A progressive-pricing array formula in action
I’ll admit that it did take some fiddling to get the actual formula working for me. I used this as my template: ‘Progressive Pricing Formulas For Excel’ (www.cpearson.com).
The example given is similar to the setup I wanted for my own quotation tool, and it provides a formula that I was able to tweak for my own data. See also my downloadable sample below.
Here's a screen shot of what a progressive-pricing array formula might look like in Excel.
And here's an Excel template you can download and adapt to suit your own preferences. Note that you'll need to look carefully at, and amend, the array-formula box to ensure that the cell descriptions are correct for your data (that's the fiddly bit!).
One size doesn’t fit all
The usual caveat applies – my way certainly isn’t the best way or the only way! It’s just one approach of several. I wanted to share my experience with you so that if you fancy testing a progressive-pricing array, you have a framework to get you started.
In practice, you might want to build more ranges into your array formula to provide increased flexibility. The numbers I’ve used above are just for illustrative purposes.
I find the array formula useful for ballpark quotations because I want to provide a quick quote based on a word count. Obviously, any professional proofreading project needs to be evaluated on more than just a word count before terms are agreed and confirmed.
Those editorial professionals working with complex projects that require varying levels of intervention might find a progressive-pricing array formula far too limiting. It functions well for me as a proofreader because of the nature of my work.
I do, however, have different arrays set up for different client types (e.g. students for whom English is a second language; independent authors whose first language is English) and for different levels of proofreading service.
The prices I assign to the various ranges are different in order to reflect the variances in how I work with the text and the speed at which I am able to proofread.
How do you build a price for editorial work?
How do you build your quotations? Per hour, per word, per day, per project? Have you tested different approaches for building your fees? And do you find that different models work better for different types of editorial work?
I’m always interested in learning how others go about pricing editorial work so please do leave a comment if you have something to share.
UPDATE (August 2020): For more help with fees, take a look at my guide How to Develop a Pricing Strategy.
Louise Harnby is a line editor, copyeditor and proofreader who specializes in working with crime, mystery, suspense and thriller writers.
She is an Advanced Professional Member of the Chartered Institute of Editing and Proofreading (CIEP), a member of ACES, a Partner Member of The Alliance of Independent Authors (ALLi), and co-hosts The Editing Podcast.
Visit her business website at Louise Harnby | Fiction Editor & Proofreader, say hello on Twitter at @LouiseHarnby, connect via Facebook and LinkedIn, and check out her books and courses.
26/9/2016 04:19:19 pm
I am just setting up as a freelancer and have been feeling rather daunted by the subject of what to charge and how to quote. It's really useful to get an idea of the different approaches and a clear explanation of this particular method. Feeling less daunted now - thankyou!
2/10/2016 11:40:36 am
You're very welcome, Catherine. I agree that it's worth considering a variety of options. You might also like to take a look at Melanie Thompson's Pricing a Project, available from the SfEP: http://www.sfep.org.uk/resources/guides/#PP
19/7/2018 02:54:17 pm
Thanks so much for this! I've been struggling for the past year to come up with a rate schedule that's fair to both the client and me -- not easy when you accept projects of all sizes. Hourly simply doesn't appeal to most clients (I totally understand needing to control the budget). I tweaked your spreadsheet for my US business and have been very happy with the results!
19/7/2018 03:05:53 pm
So glad to hear it's proved useful for you, Kerry! Thanks for letting me know!
25/8/2020 11:01:17 am
Louise, this is lovely and transparent. I'd originally come onto your site to find advice on pricing up a style sheet for a new client, but this progressive pricing gives me a more methodical way to quote for bigger projects, so I'll make sure I store it away. Thanks for being so generous with your advice!
25/8/2020 11:08:11 am
You're very welcome. I have a little book about pricing strategy if you're interested, but Melanie Thompson's CIEP guide is great for guidance on pricing up particular projects.
15/2/2021 02:35:37 pm
Thank you so much for this. I have spent the best part of 6 months divising my pricing strategy, and this is by far the clearest method I have come across.
Leave a Reply.
If you'd like me to email you when a new blog post is available, sign up for blog alerts!
'Louise uses her expertise to hone a story until it's razor sharp, while still allowing the author’s voice to remain dominant.'
'I wholeheartedly recommend her services ... Just don’t hire her when I need her.'
J B Turner
'Sincere thanks for a beautiful and elegant piece of work. First class.'
'What makes her stand out and shine is her ability to immerse herself in your story.'
'A million thanks – your mark-up is perfect, as always.'