"SEC | S20W2 | Data Analysis with Google Sheets: (Advanced Excel formulas, and pivot tables.)

ninapenda (74)in #spreadsheet-s20w2 • yesterday

AddText_09-17-12.39.14.png

Hello Teachers,

Thank you for having me again in the second segment of this challenge. I am pleased with the teaching so far.

I present my homework below,,,,

_{Explain what you understand by Advanced Excel Formulas, and show us where advanced formulas such as the lookup function, and logical function are found in Excel with clear screenshots.}

Advanced Excel Formula are functions that can help you perform complex calculations, although they are rarely used because of how uncommon they are.

Excel functions are over 450 and there are popular ones like; VLOOKUP, COUNTIF, SUMIF and so on. These functions are inbuilt formulas that one could use to retrieve specific data from existing ones., it is almost like duplicating the data. They can also be used to create reports or dashboards.

How to locate advanced formulas in spreadsheets

• Open to spreadsheets, highlight a column or row.

• Locate the text bar and click on the icon

• It takes you to formulas where you can Locate the lookup and logical function.

• Now you can select anyone you want.

_{Write the IF Function formula to calculate the total, average score, and grade of students given in the table below}

I noticed that IF function cannot be used to calculate the total and the avarage because it is used for conditional decision, but can be used for the grades.

To calculate Total

=SUM(B2-E2)

To calculate Avarage

AVERAGE (B2-E2)

To calculate the grade

=IF(G2>=70, "A", IF(G2>=60, "B", IF(G2>=50, "C", IF(G2>=45, "D", IF(G2>=40, "E", "F")))))

_{Briefly discuss four IF function Operators that you have learned and tell us their functions and when we are to use them}

Greater Than (>)

This Formula is used to check is the value of a number is higher than the other. This commonly used in offices for data entry and for simple calculations.

Less Than (<)

The less than is for checking if the value of a given number is less than the other. It is also used for simple calculations and data entries.

Equal to (=)

This Formula is majorly to access is two numbers are the same in value. To check the match between two numbers. We can use it to ascertain the equality between two numbers.

Not Equal to (<>)

When we use this formula, we are checking if two number values are different from each other.

_{Based on the given data below: Create a pivot table that shows (see) total sales by product, by dragging the product to the Rows areas, Region to the Column area, and Sales to the Values area. Please we want to see the steps you take in adding your pivot table}

Step one: I typed in all my data into my spreadsheet. As you can clearly see on my screenshot labeled step 1 below

	Step 1

Step two: High light all the written data imputed and clicked on icon below on the tool bal to locate the insert option.

	Step 2

Step three: I scrolled up to search the insert and I got it so I selected it, then i went further to locate the pivot table as seen on my screenshot.

	Step 3

Step four: I clicked the pivot table and it reflects on the data on my spread sheet.

	Step 4

Step 5: The pivot table appears immediately on a new spreadsheet so that I can fill in my data. See screenshot below

	Step 5

Step 6: I filled in the products on the rows of the pivot table, filled the regions on the coloum and filled the sales at the middle and we have the finally look as seen on the screenshot below.