Hello guys! Hope you all are doing well in your internships. As a part of your internship you will have to handle data in different formats. Sometimes it gets difficult to handle data and of course a Google search would help, we thought we might try to reduce this effort of yours. Team Quantinuum is happy to release its summer internship edition of its monthly magazine Quriosity. The motive of this edition is to help students with the tools and techniques that can help them to handle data. With suggestions from seniors we have covered few formulas in excel which will come in handy and help you put up a good show at your internship. Hope this article will be of help to you during your internship. All the best!! Excel Formulas Excel is one of the most important tools used in the corporate world to handle different forms of data. Excel has the capability to solve a lot of problems and perform various analyses using a lot of formulas that are inbuilt in it. We have bought to you a set of excel formulas which will be helpful in handling the data during your internship. And these are just few of the formulas which Excel provides. We suggest you to explore the formulas tab of excel to get to know about the various formulas which can be useful in your domain. 1
Cleaning and Formatting Functions: Cleaning and formatting is the first step of any data analysis process. This is important so as to get your analysis and interpretation right. Formatting helps to display your data in a better way so that it appeals to the reader. 1) Upper: Description: Upper is a function which converts the text you have to uppercase. You can give the reference of a cell or give a word directly within double quotes. Helps to format the text copied from another source. Usage: UPPER(B23) or UPPER(“Hello world”) Output: HELLO WORLD 2) Lower: Description: Lower is a function which converts the text you have to lowercase. You can give the reference of a cell or give a word directly within double quotes. Helps to format the text copied from another source. Usage: LOWER(B23) or LOWER(“Hello world”) Example: hello world 3) Proper: Description: Proper is a function which converts the text you have to a case where every first letter of the word is in the upper case and all the remaining letters of the word are in the lower case. You can give the reference of a cell or give a word directly within double quotes. Helps to format the text copied from another source. Usage: PROPER(B23) or PPROPER(“hello world”) Output: Hello World 4) Trim: Description: Usually when we copy data from another source, there might be chances of having white spaces before or after the text. And this might cause problems when you are handling numbers. Use this function to remove the trailing and leading white spaces from any cell or string directly. Usage: TRIM(B23) or TRIM(“ 2345 ”) Output: 2345 5) Left: Description: Sometimes you might need only a selected number of characters to perform a particular operation. This function helps you to extract a particular set of characters from a string from the left side. 2
Usage: Left(<Word/Cell Ref>,<no of letters to extract>) Example: Left(“Hi all”,2) Output: Hi 6) Right: Description: Sometimes you might need only a selected number of characters to perform a particular operation. This function helps you to extract a particular set of characters from a string from the right side. Usage: Right(<Word/Cell Ref>,<no of letters to extract>) Example: Right(“Hi all”,3) Output: all 7) Mid: Description: Sometimes you might need only a selected number of characters to perform a particular operation. This function can help you to extract a particular set of characters from a string. The index of the numbers starts from 1. Usage: Mid(<Word/Cell Ref>,<Starting index>,<no of letters to extract>) Example: MID(“E9838L”,2,4) Output: 9838 8) Repeat Description: This function is used to repeat a character for a particular number of times in a given cell. This can be used in the form of a cell chart, typically a bar chart which will help you to present you data in a better way. Usage: REPT(<character in “”>,<no of times>) Example: REPT("|",A1/40) Output:
3
Sorting and Logical Operators: You might be aware of some of the sorting and logical operators which will help you to sort data and logical operators like if, else, if else, but there are a few other operators which will be helpful in some of the special cases where you have to simplify an if else function.
1. Small: Description: You would have used min function to find out the smallest number from an array but have you tried to get the second smallest or third smallest number from an array? This function will help you to do that. Usage: SMALL(<array/group of Cell Ref>,<n >) [n is the nth smallest value] Example: SMALL({1,2,3,4,5,6,7},2) or SMALL(B2:B5,2) Output: 2 2. Large: Description: You would have used max function to find out the largest number from an array but have you tried to get the second largest or third largest number from an array? This function will help you to do that. Usage: LARGE(<array/group of Cell Ref>,<n >) [n is the nth largest value] Example: LARGE({1,2,3,4,5,6,7},2) or SMALL(B2:B5,2) Output: 6 3. Choose: Description: This is a simplified version of using more than one if else function in Excel. For example, if you had to create something like a grade book this would come handy. You have to give a number according to which an option is selected. Usage: CHOOSE(<index>,<Value 1>,<Value 2>,<Value 3>..( [value could be a cell of string] Example: CHOOSE(2,”A”,”B”,”C”) Output: B Note: Here the number will be arrived by some other formula which will define which option to choose.
4
4. If: Description: This is a very simple function which can be used for various purposes like using two different formulas for two different scenarios, or formatting data by restricting your output to non-negative numbers or a particular text. Usage: IF(<condition>,<Value if true>,<Value if false>) Example: IF(B4>0,B5,0) Output: If B4 is negative the cell will have its value as zero else it will have the value which is in B4.
Date Functions: Date is a function which will be of frequent use in your data. These dates will help you understand the deadlines and if you are working on a project, you would have to use a lot of date functions. Below are a few date functions which will help you with this. 1. Today Description: This function gives you the present date and the reason that this is useful is that it can be used to calculate the number of days left for the project. Usage: Today() Output: The output will be today in the format which is specified for the given cell. 2. Network Days Description: This function will help you to find out the number of working days between two dates. This can be used to find out the number of days you get from the start and the end of the deadline. Usage: NETWORKDAYS(I17,I19)[ I7- 4/17/2017 I9-5/17/2017] Output: 23 3. Workday Description: This function will help you to find the exact date which is the number of working days from today or any other day. Usage: WORKDAY(TODAY(),20) Output: 5/15/2017
5
Mathematical and Financial Functions: There are some mathematical functions varying from basic sum, average to complex functions. These functions come handy whenever you have to deal with numeric data. Also there are few financial functions which will help you by doing most of the calculations. Below are a few formulas which will help to reduce the number of steps required for calculations or the need of using a calculator. 1. Convert Description: This function can convert a measurement from one unit to another. It could be distance units, weight units and even date. Usage: Convert(<measure>, <from units>,<to units>) Note: The from units and to units have a specific code. For example gram is represented by “g” and ounces by “ozm”. Check this link for the specification. Link 2. FV: Description: This function is used to find out the future value of a set of payments given the number of years, rate of interest and payments. Usage: FV(<rate in percentage>,<no of year>,<payments>) Example: FV(10%,12,-1000) Output: 21384.28 3. NPV: Description: This function helps to calculate the net present value of a series of payments. It requires the series of payments and the percentage of the rate of interest. Usage: NPV(<rate in percentage>, payment 1, payment 2, payment 3..) Example: NPV(10%,100,100,100) Output: 248.69 4. PMT: Description: This function gives you the monthly installments that you have to pay for a particular principal borrowed at a certain rate of interest. Usage: PMT(<rate in percentage>,<no of payments>,<principle>) Example: pmt(10%,12,-100000) Output: 14676.33
6
We have referred to the website Chandoo.org for the above formulas. It has quite a number of formulas in Excel which can be used as per your need. This was recommended by our seniors, and these formulas were of great use to them during their internship. Excel is a major tool which everyone will be using during the course of their internship and that is why we have come up with our first article based on it. Please write to us about your feedback by mailing us at quantinuum.simsr@ somaiya.edu. Hope these formulas would be helpful to you during your internship. They would be of considerable use to present your reports in a systematic manner thereby creating a good impression about you. Also, using these formulas will reduce the time required for a particular task which will prove to be a differentiator with respect to other interns. All the best for you internship once again and hope you return to college with a PPO from the company.
7
Below is a summarized list of all the functions that have been mentioned in the article.
Function
Description
Upper
Converts text to uppercase
Lower
Converts text to lowercase
Proper
Converts text to first letter capital
Trim
Removes trailing and leading spaces
Left
Trims a text from left side
Right
Trims a text from right side
Mid
Slices a part of text from between
Rept Small Large
Repeats a character a particular number of times Gives the nth smallest number from an array Gives the nth largest number from an array
Choose
Simplified multiple if else function
If
Used to execute a conditional statement
Today
Gives todayâ&#x20AC;&#x2122;s date
Networkday Workday
Gives the number of working days between two dates Gives the date after a number of working days from a specified date
Convert
Converts one unit to another
FV
Gives future value of a payment
NPV PMT
Gives the present value of a payment Gives each monthly installment for a specified cost of capital and number of payments 8