Learn Data Analysis With Excel Free in Applied Way With Part wise labs(part-2)

If are you seeing it for 1st time do check out the Ist tutorial here.

Problem Statement:

While your first attempt to show the company’s performance to Lucky was not bad, clearly he has a lot more requirements than what you provided. he wants to know more about the year over year sales, sliced into different categories, sub-categories, and countries. he also wants to see additional information such as customer demographics. He is not smart so he needs every detail in hand.

Data is provided by jacky  lab2data.xlsx

Things to do :

The first thing you need to do is to convert the data into an Excel table.

 Add a total row for the table, and use the Sum aggregation to show the total of the Revenue column and then filter the data only for United States.

Use : Autosum

First, let’s add a “Month” column. Insert a new column to the left of the Customer ID column, and use formula to derive the month of sales from the Date column.

Next, let’s add an “Age Group” column. Remember to clear any filters you previously applied. Insert a new column to the left of the Customer Gender, and use formula to derive the age group from the Customer Age column. Let’s group the customers based on the following criteria:

  • Youth (<25)
  • Young Adults (25-34)
  • Adults (35-64)
  • Seniors (>64)

Use this to bar =IFS([@[Customer Age]]<25,”Youth”, AND([@[Customer Age]]>=25,[@[Customer Age]]<=34),”Young Adults”, AND([@[Customer Age]]>=35,[@[Customer Age]]<=64),”Adults”,[@[Customer Age]]>65,”Seniors”)

Now, let’s add a “Frame Size” column. Insert a new column to the left of the Order Quantity, and use a combination of the IF() and RIGHT() functions to derive the frame size of a bicycle from the last two characters of the Product column, when the Product Category is Bikes. Otherwise, leave it blank.

=IF([@[Product Category]]=”Bikes”,RIGHT([@Product],2),””)

Find profit of the table

Do it yourself

Congratulations on finishing this step.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s