Updated: Jun 12

You can use the Excel file linked to at the bottom of this post to automatically calculate how many pages and lines are in a single deposition designation, and multiple designations.


The beginning and ending pages and lines for each range of testimony are entered in columns B through E. Column H has a formula which computes the total number of lines in a single range of testimony:

=IF(B2=D2,(E2-C2)+1,(E2-C2)+((D2-B2)*25)+1)



If the designation is only on a single page, it simply subtracts the line number in column C from column E. If this is not the case, it multiplies the number of pages by 25, and adds them to the difference between E and C to get the line total.


Column I contains the formula that divides the line count by 25, and rounds down the result to the nearest whole number, unless there are less than 25 lines:

=IF(H2<25,0,ROUNDDOWN((H2/25),0))



The formula in column J: =IF(I2=0,H2,H2-(I2*25))

. . .subtracts the line count for the rounded down result given in column I from the total line count, unless there is less than 1 page, in which case it returns the total line count from column H.



On the TOTAL row, the formula in cell I9 not only adds up the page total listed above in column I, but also gives the sum of lines in column J divided by 25, rounded down to the nearest whole number. This gives the total page count for all the designations given in columns B to E on the above rows.


=SUM(I2:I8)+(ROUNDDOWN((SUM(J2:J8))/25,0))



Finally, the formula in J9 subtracts [the total line count divided by 25 and rounded down to the nearest whole number, but then multiplied by 25 - to get a number divisible by 25] FROM the total line count, in order to get the number of lines left over when the lines adding up to full pages are counted .


=SUM(J2:J8)-(ROUNDDOWN((SUM(J2:J8))/25,0)*25)



Deposition Designations v3
.xlsx
Download XLSX • 12KB


32 views0 comments

Tonight's tip of the night explains how you can use an array formula in Excel to confirm that values on a single row for multiple columns are the same, but discount any instances where a column has a blank entry. So if the same value to filled in one or more of the columns in the given range, and none of the other columns has a different value, or simply has a blank value, the result of the array formula will be TRUE.


In this example we have both numbers and text entered in columns B to E. The formula:

=(SUM(IFERROR(1/COUNTIF(B2:E2,B2:E2),0))=1)


. . . will check columns B to E on row 2. This is an array formula, so be sure to press CTRL + SHIFT when entering it to enclose it in curly brackets.



As you can see, regardless of whether numbers or text are use in a row range, the formula indicates if they match or differ, and skips over the cells with no content.

30 views0 comments

Note that will it not always be possible to access a virtual private network when using wifi in a public space. The admin of the network may have it configured to bar VPN protocols. This came up for me today in the Minnesota state district court in the Hennepin County Government Building in downtown Minneapolis. The speed of the wifi network in a courtroom was great (more than 50 mbs per second for uploads and downloads), but it would not allow me to connect to my firm's network via VMWare. I got around the problem by getting online with a MIFI device, which connects to the web using a cellular network.

28 views0 comments

Sean O'Shea has more than 15 years of experience in the litigation support field with major law firms in New York and San Francisco.   He is an ACEDS Certified eDiscovery Specialist and a Relativity Certified Administrator.

The views expressed in this blog are those of the owner and do not reflect the views or opinions of the owner’s employer.

 

All content provided on this blog is for informational purposes only. The owner of this blog makes no representations as to the accuracy or completeness of any information on this site or found by following any link on this site. The owner will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of this information.

 

This policy is subject to change at any time.