Power of Spreadsheet 101

Data Analysis Using SpreadSheet

Photo by Luke Chesser on Unsplash

Before we begin, do you know the difference between MS excel and Spreadsheet. Well, most of you would have seen a spreadsheet before, if not then let me tell you it looks exactly the same as excel sheet. Then what is the difference between them, it’s easy –

“You need Excel to create a Spreadsheet”

In simple term think Spreadsheet as food and Excel as a utensil, you can use any type of utensil to make food but the end product will be food.
I hope that cleared it out. Now moving on to what do Spreadsheet do or what is a spreadsheet?

The Answer- Spreadsheets are used to store and manage data in the cells which are represented in the form of rows and columns. The data is stored as a record in the spreadsheet and can be manipulated.
In Spreadsheet we call functions to give output (Functions perform calculations on your data), obviously there are different functions for different purposes.

Now the big question, Power of Spreadsheet.

Recently I’ve complete a MOOC coursefrom Datacamp name “Data Analysis in SpreadSheet”.
There has been a hype around for Datascience and related toolboxes like Python, R, SQL and whatnot, but today we’ll take a look at the father of all- SPREADSHEET.
Let’s get started and discuss some functions, tips and tricks which you can excel in excel.

#ROUND

The First Function is “ROUND”, the “ROUND” function calculates the rounded value of its input.
ROUND(value): rounds the number you give as input, value.

Example-  =ROUND(7.47) will give 7 as output

Note — (In Spreadsheet to call a function you must type “equal to (=)” sign first)

#SQRT

The “SQRT” function calculates the square root value of the input.

Example- =sqrt(9) will gives 3 as output

ROUND function and SQRT function both can be used together as

=round(sqrt(value))

#MIN & MAX

Yes, you guessed right, this function finds the minimum and maximum value from the given range.

MIN(value1, [value2, ...]): searches for the minimum value in its arguments
MAX(value1, [value2, ...]): searches for the maximum value in its arguments

Or
You can just give the range as “=min(A2: A9)”. This will find the minimum value in column A from 2 to 9 rows.

#SUM, AVERAGE, MEDIAN

The following functions are used to -

SUM(value1, [value2, ...]): calculates the sum of all its arguments.(or =sum(A2:A9)
AVERAGE(value1, [value2, ...]): calculates the average of all its arguments.
MEDIAN(value1, [value2, ...]): calculates the median of all its arguments.

#RANK

RANK gives you an idea of how a value compares to other values in a range. In simple term give rank to certain values.
RANK(value, data): evaluates to the rank of a value in a range, data

Example- =rank(A1,$A$1:$A$3) will check the rank of A1 in term of boxes A1 to A3.

Note:- $A$1 is the absolute address.
You Also Rank the data in descending order, and that require a third argument which is just “0 & 1”
“RANK(value, data, [is_ascending])”
“1” — for Ascending
“0” — for Descending.

#LEN & SEARCH

The two new functions are used as:

LEN(text): evaluates to the number of characters of text. E.g. =LEN("Cell") would
evaluate to 4.SEARCH(search_for, text_to_search): searches forsearch_for in text_to_search:
o search_for: the string to look for
o text_to_search: the string to look inExample – “=SEARCH(“e”,A1)” will search for the number at which ‘e’ appears in the string
present in A1.

#CONCATENATE

This function combines two strings.

CONCATENATE(string1, [string2, ...]): combines one or more strings into a single string.
E.g. =CONCATENATE("foo", " ", "bar") evaluates to foo bar.

#WEEKDAY

This function is used as –

WEEKDAY(date, [type]): evaluates to the day of the week of a date.type is 1, 2 or 3

For example, using =WEEKDAY(A1, 2) (where A1 contains the date 2019–01–01) would evaluate to 2, because January 1st 2019 fell on a Tuesday and setting type to 2 sets Monday at 1.

#DATEDIF & NOW()

Sometimes you might need to compare certain dates to each other, or to the current date. There are some useful functions for that –

DATEDIF(start_date, end_date, unit)

it calculates the time difference between two dates. The difference will be calculated between start_date and end_date.
The end_date must take place after the start_date. A third argument here is the unit,
this can be:
“Y” the number of years between two dates
“M” the number of months between two dates
“D” the number of days between two dates
NOW(): a function without arguments, evaluates to the current time

For example, =DATEDIF(“2018–01–01”, “2018–01–03", “D”) would evaluate to 2

These were some the easy trick which we can perform in spreadsheets to get the better insight without any programming knowledge.
With this we end here and will come back with another course review.
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
END NOTE :

For more such posts, do follow out our Publication :
https://medium.com/tek-society

Also do clap! It encourages me to write better!

Thank you!