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

Hello!
Welcome to my block. Interesting to fund myself here again for the week2 series.

^source

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.

Advance Excel Formulas in excel are usually those commands when applied help to solve complex problems that ordinarily would take man hours to complete. All mathematical functions including summation, subtraction, multiplication, probability, and other statistical indices are imbibed in it for quicker analysis.

It has made data analytics a lot simpler only if we understand how much to use these functions. Just like when we use the BODMAS to solve mathematical problems, it can also be used.in combination with other functions to generate quicker solutions.

The SUMIF, ARRAY, LOOKUP, TEXTJOIN, WORKIF, etc are some of the advance Formulas found while using the Excel sheet.

How we can find the Lookup and Logical functions:

• launch the excel spreadsheet using the laptop
• From the top tool bar, click on "Formulas" option
• Amonsgt the displayed options,.you can.see thr Logical and Lookup options.

---

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

Total Summation:

. I will simply enter this command =SUM(B2,C2,D2,E2) and then click on the enter button

• Total for simonnwigwe is 269.
• What I need to do for other students is to place the cusor on the cell edge and drag it down to represent the same cell function to other corresponding cells.

Average:

. I will simply enter this command =AVERAGE(B2,C2,D2,E2) and then click on the enter button

• AVERAGE sum for simonnwigwe is 67.25
• What I need to do for other students is to place the cusor on the cell edge and drag it down to represent the same cell function to other corresponding cells.

Grade:

• To get the student grades, this is to formula to achieve this command. =IF(G2>=70,"A",IF(G2>=60,"B",IF(G2>=50,"C",IF(G2>=40,"E","F")))))
  Note: Still using the same grade standards as demonstrated in the class.
• Grade for simonnwigwe is B
• What I need to do for other students is to place the cusor on the cell edge and drag it down to represent the same cell function to other corresponding cells.

---

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

There basically for types of IFS and their application.

• IF for logical operations. We have the AND, OR, NOT
• IF for Equality commands. >,<,=,<=,>=,<>,
• IFs for multiple conditions
• IFs Error.

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.

Step1:

• Highlight the data to be represented on the pivot table.
• Click on insert, then on pivot table

Step2

• the landing page shows a new interface where date, product, region and sales are displayed with tge corresponding report, column, row, and value filters showing.
• I will start dragging each of this item.to the requested filter box.

This was simple in practice but laborious to represent in publication. Thanks for the hands-on engagement and refreshment on the use of spreadsheet.

I am inviting @eveetim, @chilaw, @djanita