Calculate How Many Pages are in a List of Bates Ranges

Suppose you get an angry letter from opposing counsel complaining about a set of redacted documents you produced, and attaching a list of the Bates ranges.  You want to know how many pages of documents there are to look at. Here’s an easy way to calculate the total.

Start with the list of the documents with their beginning and ending Bates numbers.

BatesCount1b

Now use Excel’s RIGHT function to convert the Bates numbers into regular numbers that you can calculate with.

The RIGHT function grabs a certain number of characters from the end of a certain cell. The format is =RIGHT(target cell, number of characters).  As always, Excel will remind you what the arguments are when you type in the function.

Here we want to grab the 6 characters at the end of each Bates number.  Create two helper columns to store the results and calculate the number of pages in each document:

BatesCount1

The formula in Column F subtracts the two numbers to get the number of pages.  Don’t forget to add 1!

Here are the values returned by the functions:

Batescount2

Looks good! (Now do you see why you have to add 1?)

Finally, use the Sum function to add up all those pages calculated in Column F:

batescount3

Voilà!  This list of documents consists of 1,104 pages.  Might be a long night!

Here are a few tips that can make this even easier:   

  • To copy the formulas to the entire range, use Edit –> Fill Series –> Autofill (or just click on the cross-hairs in the lower right corner of the first cell with the formula).
  • To display the formulas instead of the values, Press Ctrl + ` (the ‘grave accent’ key, to the left of the 1 key). Use the same combination to toggle back.

In a future post I’ll explain how to do a similar problem in reverse: turn a list of numbers into properly formatted Bates numbers with the right number of padding zeroes.

Please leave a comment below or email me with any questions.  Thanks for reading, and please pass it on!

Advertisements

About excelesquire

NYC attorney and Excel enthusiast.
This entry was posted in eDiscovery and tagged , , . Bookmark the permalink.

One Response to Calculate How Many Pages are in a List of Bates Ranges

  1. Vasant Patel says:

    Instead of this you can use below formula..
    =(RIGHT(B1,6)-RIGHT(A1,6))+1
    and autofill value using click on + (Cross-hairs)

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s