This article is basically a reference for all Excel formulas that are helpful when working with databases and projects.
Area Code Reference List
For this formula:=VLOOKUP(VALUE(LEFT(D4,3)),A4:B413,2,FALSE), it creates a reference list for all area codes and states and matches them by what you have on column D to column E.
You can do this by going to E4 and click and pull down from the small square on the right to populate what's on column D, such as 818 from the example below.
Show 15+ Digit Numbers or any amount
Excel by default will show something like this : 1.2346E+E
if you try to type in a 15+ digit like this : 12345678901234500000
You will need to add a formula like this : =LEFT(A1,20)
(In the cell A1, shows 20 digits starting from the left.)
This is also useful when doing something like area codes: =LEFT(A1,3)
This will only show the first 3 numbers.
Reference 2 columns and match to a 3rd column to give you a result
In this formula which is =INDEX(B:B,MATCH(D3,A:A)) , you will look up columns A and B to match column D.
This is useful when you have a bunch of names or phone numbers and you don't know what it belongs to.
In this example, the formula is added to the last column (column E) and the formula is checking A:A, B:B, and the entry D3.
Calculate Short Duration for Reports
The purpose of this article is to calculate the ratio of calls that are being billed as 6 seconds.
This is shown when getting a .CSV file that shows a billsec column, such as the one below.
To calculate this, you will need to go to Excel or Google Sheets and add the following formula.
A:A means the range
6 is what's in the cell
This just takes the cells that are one value and divides it from the total that has digits.
You can also try =COUNTIF(A:A,6) to show all cells with 6 in them.
=COUNTA(A:A) will count all cells with numbers in them.