Spreadsheets

This week we learned about spreadsheet software.

What are Spreadsheets and how are they used?

Spreadsheet software refers to software that can organize and calculate data in a table. Spreadsheets have numerous real-life uses, such as accounting (calculating the finances and allocation of money spent for a company), creating lists, and putting data in a table and a chart that is easy to analyze. There are many different functions that can be used in spreadsheet software. During class, we created an online spreadsheet through Zoho so we could try out different spreadsheet functions together. The functions are talked about more in depth below.

RANDBETWEEN

This function returns a random whole number within the range given by the user (including the top and bottom number.) To use this function, you type in the cell =randbetween(bottom;top). “Bottom” refers to the number that is the minimum number within the range, while “top” refers to the number that is the maximum number within the range. Everytime you make a change, the function comes up with a new random number. This function does have some uses. For example, it can be used as a simulation for probability: if you set the bottom and top number as 1 and 2 respectively, you can simulate the probability of tossing a coin (with one digit representing heads or tails).

SUM

This function adds the values of the cells collected. It looks like =SUM(number1;number2…number n). The greatest value that n can be is 50. SUM is very helpful: for example, it can easily calculate the total amount of money you spent in a time period.

AVERAGE

Average finds the average value of the cells selected (only including numbers, no text). It is typed in the same way as SUM but “sum” is replaced with “average”.
Obviously, this function is very handy. One use of it that is particularly relevant to students is that it can find the average of your grades.

In Picture 3, the formula of “Avg (formula) is =AVERAGE(B2:B9).

MAX&MIN

 

MAX finds the largest value within an array of selected values, while MIN finds the smallest. MAX and MIN are both typed in the same way as “sum”, except “sum” is respectively replaced with “max” and “min”. Once again, at most 30 values can be selected at a time. Continuing with the example of being a student, MAX and MIN can be used to find the greatest and worst performer, or your best and your worst subject (in terms of grades).

In Picture 3, the formula of MAX is =MAX(B2:B9), while the formula of MIN is =MIN(B2:B9).

ROUND,ROUNDUP, & ROUNDDOWN

These three functions round numbers. The formulas are written as =ROUND/ROUNDUP/ROUNDDOWN(number;places).  (ROUND will round the value to the nearest places; if the nearest pladce is unspecified, then it is rounded to the nearest integer. On the other hand, ROUNDUP rounds the value to the nearest number up to a certain precision, while ROUNDDOWN rounds the value to the nearest number down to a certain precision (once again, if the precision is unspecified, then the place is assumed to be the integer place). Rounding is useful for when you only need a value to be of a specific precision. Roundup and rounddown can be used for when you can’t use decimals of something (ie., you can have 3 or 4 apples, but not 3.6 apples per person), and you either only have so many apples to distribute and therefore must have 3 apples per person, or you can buy more apples and make it 4 apples per person.

In Picture 3, the formula of ROUND is =ROUND(A2), ROUNDUP is =ROUNDUP(A2), and ROUNDDOWN is =ROUNDDOWN(A2).

 

ABS

This function calculates the absolute value of a number. Its formula is ABS(number). This function is handy for calculating vector quantities, which are all greater than zero.

In Picture 3, the formula of ABS is =ABS(A3-A4).

COUNT, COUNTBLANK, COUNTA & COUNTIF

These functions can count the number of cells that fit a certain condition within the selected number of cells. COUNT counts the number of values within a selected range, excluding text entries. COUNTBLANK counts the number of empty cells within a selected range, while COUNT IF counts the number of cells in a range that meet the conditions specified by the user. COUNT’s formula is =COUNT(value1;value2;……value30) (at most 30 values), while COUNTBLANK is =COUNTBLANK(range). COUNTIF is =COUNTIF(test_range;condition). Condition can be a number or a range such as <=2. These functions can be respectively used to count the number of grades you should have, the number of grades you are missing and the number of grades you have that are above a certain grade. COUNTA is =counta(value1;value2;…value30) and counts the non-empty values in a list of cells. It can be useful for telling how many entries you do have within a certain range.

In the 3rd picture, the formula of COUNT is =COUNT(B2:B10), the formula of COUNTBLANK (the D10 cell) is =COUNTBLANK(B2:B10), the formula of COUNTA is =COUNTA(B2:B10), and the formula of COUNTIF is =COUNTIF(B2:B10).

RANK

This function ranks a certain number within a specified number list. Its formula is =rank(number;numberlist;order). If order is zero or omitted, the numbers are ranked with the highest being first. If order is nonzero, the numbers are ranked with lowest being first. Rank is helpful to tell how big a number is compared to other values within a certain list.

The formula of RANK in picture 3 is =RANK(A5;A5:A7).

CONCATENATE

CONCATENATE is a very special function. Its formula is =CONCATENATE(text1;text2;text3…text30). What concatenate does is combine values from several different cells (that can be on different sheets) all into one cell. Concatenate can be used in a group project to combine work written on separate sheets by all people.

During class, we used concatenate to create a story together. The class was divided into groups, and each group was responsible for coming up with a story about a certain layer of the a computer system. Our group was responsible for data. Then, all of the seperate stories from the different groups were combined into one big story with CONCATENATE. It was very interesting because different parts of the story were written independently.

A part of the concatenate formula and the story we created are in picture 4.

IF&IFERROR

The if function returns a certain number depending on the condition given. Its formula is =IF(test;value1;value2). “test” is a value or expression that is used to evaluate which number to use. This function can be used to quickly determine if a certain value fits an expression or not.

Iferror returns one of two values, depending on the first value entered. Its formula is IFERROR=(value;value_if_error). Value is checked to see if the value has an error. The value remains “value” if there is no error, otherwise the “value_if_error” value will be shown.

In the picture, the formula of IF is =IF(K1>3;1;2) and the formula of IFERROR is =IFERROR(K1<0). IFERROR is the false cell.

AND,OR&NOT

The AND function will return TRUE if all the values entered are true. Otherwise, FALSE will be displayed. Its function is =AND(argument1;argument2;…argument30). Any value that is a non-zero number or is text is considered TRUE. This function is very useful for when you have to make sure that all values are real before you can proceed.

The OR function returns FALSE only if none of the values entered are true. Its function is the same as AND except it only returns FALSE if all of its values are false. This function can be used when you only need one of the values to fulfill a condition to move forward.

The formula of AND is =AND(Q14>Q15;Q16>Q15), OR is =OR(Q14>Q15;Q17=9), NOT is =NOT(Q14<Q15). OR is the one on top. Try to guess where the two other ones are.

NOT is a function that returns TRUE if the statement is false and vice-versa. Its function is =NOT(logical_value). For example, if =NOT(A1>3), and A1 is 2, then the argument is true. This function is used to quickly tell if something doesn’t fit a certain condition. NOT

LOOKUP

This function returns  a value from a table that corresponds with a searched value from another table. Its formula is =LOOKUP(lookupvalue;searchtable;resulttable). Lookup value is  a certain value that you search within the search table. Once the value is located in the search table, the computer finds the value with the same position within the result table. The corresponding value within the result table will be displayed in the cell that For example, if the number 7 is searched and found 3 cells from the top, then the value 3 cells from the top within the result table (maybe “Biology”) will be displayed within the cell that the LOOKUP function was originally entered. LOOKUP is obviously incredibly useful for looking up values and there corresponding values  (ie., 97 and 7) and creating a list of said values.

LOOKUP’s function is at the top of the photo.

With all that said…

This was one of the most important classes that we ever had. Spreadsheet is an extremely versatile function that is used for industries such as finance, accounting, computer science, etc. I gained a lot by learning how to enter basic yet critical functions with spreadsheet software.