We can calculate any Finance & Accounting KPI values using...

Read MoreThe post Sales Analysis Dashboards with Power BI – 30+ Alternatives appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>The post Sales Analysis Dashboards with Power BI – 30+ Alternatives appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>The post Announcing Power BI Dashboard Contest (win $500 prizes!) appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>- Participants must use Power BI (any recent version) to create the dashboard.
- The dashboard should be built with native visuals only. No add-ons or marketplace visuals or Python/R visuals.
- The dashboard should contain a readme / help tab to explain the features and methodology.
- Your dashboard should feature the Awesome Chocolates logo, contest title on it. These assets are included in the dataset file below.
- Limit your dashboard to one Power BI Page (readme / help goes on a separate page)
- You are free to combine the business data of Awesome Chocolates with any publicly available datasets. Credit any sources in the readme / help tab.
- The participant agrees to allow indefinite copyright and sharing of the workbooks (and any extra datasets) with chandoo.org for further publication, remixing, and usage.
- Your dashboard should be submitted by
**30th of April 2024**using the upload instructions (to be added later). - Participants agree to have their name and any social media handles listed on the contest website.
- Only one entry per participant. But your dashboard PBIX may contain more than one page with different versions of the dashboard.

I have set up an exclusive community on Microsoft Teams to run & manage this contest. To participate, please follow the below-instructions.

- You need a personal email address (such as @gmail.com or @hotmail.com) or your phone number.
**Go to my Dashboard Champions Circle on Microsoft Teams**- Use your personal email address or phone number to register. You will need to switch to “personal” mode of teams if prompted.
- Join the community and find the dataset & dashboard purpose information inside.
- Start building your dashboard.
- Submit your entry by 30th April using the instructions in the community.

Please download the Awesome Chocolates Dataset using the link inside the teams community.

The purpose of the dashboard is to inform the CEO of Awesome Chocolates about the performance of our business in the last 13 months. The dataset contains daily shipment data from 2023 February to 2024 February. Here are a few things our CEO is interested in, but she likes surprises too. So get creative.

- How sales, units, shipments, profit, profit%, low-box shipments (shipments with box count under 50) trends are looking
- A detailed performance understanding at product or salesperson level
- Interesting patterns in product / geography data
- Ability to drill down to details if needed.

You are encouraged to use various powerful interactive features of Power BI like tooltips, bookmarks, DAX, conditional formatting, pictures and anything else you might fancy. Again, get creative and showcase what you can build, but stick to one standard sized Power BI page.

You should submit your Power BI dashboard by 30th of April, 2024 (end of day, pacific time).

Remember, only one dashboard per person.

Please submit your dashboard using the instructions inside **my exclusive teams community.**

For more information and guidelines, visit the Awesome Dashboard community page.

The total prize money for this competition is $500.

- First prize: $250 Amazon Gift Card
- Second prize: $150 Amazon Gift Card
- Third prize: $100 Amazon Gift Card
- Don’t forget the bragging rights!

A panel of Power BI experts and dashboard pros will select the winners. I will share the details later on the teams community.

All the best

*Note: This competition is powered by Microsoft Teams*

The post Announcing Power BI Dashboard Contest (win $500 prizes!) appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>To calculate compound interest in Excel,

on principal amount P

at the rate of interest R

for the number of years N

and compounded T times per year

we can use the formula = P*(1+R/T)^(N*T)

In this article, understand how to calculate various kinds of compound interest values using Excel formulas.

The post Compound Interest Formula in Excel appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>The post Compound Interest Formula in Excel appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>The post How to convert test scores to letter grades in Excel? appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>In your workbook, set up a mapping (or lookup) table like this to map out each of the letter grades to the test score boundary.

- When setting up the mapping table, make sure to start from lower score to higher score (for ex: 0 to 100)
- For each grade, just specify the lower boundary value. So for example, Grade F begins from 0, Grade B- begins from 65

For this you need all the test scores for your students. Let’s say you have the test scores in column C, from cell C4. In an adjacent column, you can calculate the letter grade using the below LOOKUP formula.

**=LOOKUP(C4,$G$6:$G$16,$F$6:$F$16)**

In this formula:

- First value (C4) refers to the score for which you need the letter grade
- Second value ($G$6:$G16) is the “scores from” column of your mapping table set up in Step 1.
- Third value ($F$6:$F$16) is the “letter grade” column of your mapping table.

Once you have a result for the first test score, drag the formula down to see letter grades for all students.

Sometimes you may want to calculate the letter grade from the percentile of the test score. This sort of thing is also called relative grading (RG). To do this, we can use the PERCENTILERANK functions of Excel.

Here is a 3 Step process for that:

In your worksheet, set up a mapping table for letter grades like this:

Let’s say you have test scores in column C, in the range C4:C43.

In column D, write the formula =PERCENTRANK.INC($C$4:$C$43,C4) to calculate the percentile of test score in first cell against all scores. The result of this would be a percentile from 0% to 100% (both inclusive).

When you get the result for first cell, drag the formula down to fill up the rest.

**Tip: **If you want to calculate the percentile by excluding 0th and 100th percentiles, use the PERCENTILE.EXC function instead.

For this, we can use the LOOKUP function again. In column E use the below function:

**=LOOKUP(D4,$H$6:$H$16,$G$6:$G$16)**

In the above formula:

- First value (D4) refers to the percentile we calculated in step 2.
- Second value ($H$6:$H16) is the “percentile from” column of your mapping table set up in Step 1.
- Third value ($G$6:$G$16) is the “letter grade” column of your mapping table.

I made a quick Excel template to calculate letter grades from your test / exam marks. Just plug-in your values and see the results instantly. **Download the template here.**

That is right. All these formulas will work exactly same with Google sheets too. Here is a Google Sheets template if you need some help.

Do take these cautions when calculating alphabetic letter grades from your exam marks.

**Mapping table setup:**your letter grade mapping table needs to be from lowest marks / scores to highest. Just specify the lower boundary for each letter grade.- For example, if grade F is from exam score 0 to 35, then write 0.
- If grade B+ is from 80 to 85, then write 80

**Clean up your data:**If your test score data has missing values (for example, absent or hyphens) then the LOOKUP formula will give #N/A error. So clean up your data before you apply the LOOKUP function.

Now that you have calculated the letter grades, you may want to see the distribution of your student grades or understand which students are failing and need help. Use below Excel concepts & resources to do that.

- Understand the distribution of your data in Excel
- Create a histogram in Excel
- Using conditional formatting in Excel to highlight top 10 values
- How to use Excel Pivot Tables to understand and explore your data

The post How to convert test scores to letter grades in Excel? appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>The post How to calculate the Gender Pay Gap using Excel Formulas? (Free Calculator Template) appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>* According to NZ Government, *

Gender pay gaps are differences in pay for groups of women and men, usually based on the median or mean pay that men and women receive.

Source: Statistics New Zealand

Assuming you have average salary of men & women in two cells C3 & C4, we can calculate Gender Pay Gap using the below formulas:

**Gender Pay Gap in $s:**

**=C3 - C4**

Generalized formula = average of male salary - average of female salary

**Percentage Gender Pay Gap:**

**=(C3-C4)/C3**

Generalized formula = (average of male salary - average of female salary) / average of male salary

Excel is a great option for identifying and reporting gender pay gap issues when you have full employee data. Let’s say you have the staff data in an Excel table as shown above.

In this case, we can use below formulas to calculate the Gender Pay Gap:

Create a 3-column table in Excel with the staff ID, gender & annualized full-time salary. (Related: Learn how to create a table in Excel)

Name your table as “staff” using the Table Design ribbon in Excel.

You can use AVERAGEIFS function in Excel to calculate the male & female specific average salary.

The formula for male average looks like this:

**=AVERAGEIFS(staff[Annualized Full-time Salary],staff[Gender], "Male")**

And the formula for female average looks similar.

The formulas for this are explained above. They are:

GPG in $s: =Average Male Salary – Average Female Salary

GPG in %: =(Average Male Salary – Average Female Salary) / Average Male Salary

Format the GPG $ and Salary calculations in your currency formatting (Ctrl Shift 4)

Format the GPG % in Percentage formatting using Excel format cells option (CTRL Shift 5)

Please refer to below illustration for formula set up and help.

It is a good idea to calculate both average and median GPG values from your data. We all know that an odd high value can impact the average calculation. May be your CEO is a female and her high $$$ salary thus she bumps up the average female pay significantly.

Firstly, calculate the median pay for both male & female groups. *Unfortunately, Excel doesn’t have a MEDIANIFS function. *So, use the below formula instead:

**=MEDIAN(IF(staff[Gender]="Male",staff[Annualized Full-time Salary]))**

**Caution: Array formula**

After typing the formulas, press **CTRL+Shift+Enter **to get the correct result.

Change the gender value to Female for the respective median salary.

Once both medians are calculated, you can easily calculate the gender pay gap (both in dollars and percentage) using the same formulas as above.

Click here to download my **Gender Pay Gap calculator template**. Copy and paste your data and the file calculates the GPG automatically.

Once you have calculated the Gender Pay Gap in dollars, just divide the number with total annual hours of work. In most countries, this would be 2080 hours (ie 52 weeks times 40 hours per week).

So, for example, if you have a pay gap of $3,117, then the hourly pay gap is $1.50

This means, female staff are earning $1.50 less than their male counterparts *every hour.*

A negative GPG value indicates that your female staff are paid more (on average or median basis) compared to the male staff.

While Gender Pay Gap offers a great insight into the compensation of men vs women employees, it has a few limitations.

**GPG doesn’t explain any hierarchical distribution issues.**If you have a lopsided distribution of staff in your organization (may be more female staff at lower-level positions and more male staff in senior positions), GPG doesn’t expose this issue. I recommend visualizing the male vs. female distribution by salary bands or seniority for a better insight in to these issues.**A low or zero Gender Pay Gap is not enough.**If you want an equitable and fair organization, aiming for a zero gender pay gap at aggregate level is not enough. You need to examine GPG by:- department level GPG
- city / location level GPG
- manager vs. non-manager GPG
- new hires vs. existing staff GPG

**GPG is meaningless for small organizations**. If your total headcount is less than 30, GPG calculations can be meaningless or less insightful.

Gender Pay Gap is a key metric (KPI) in HR data analysis. Calculating, measuring and tracking GPG is helpful to understand any underlying pay issues in your organization. But don’t forget to explore the staff distribution, hiring patterns and historical trends to fully understand your data.

For more on HR data analysis, check out below articles:

- 9 Box Grid & talent mapping analysis with Excel
- Understanding Employee Churn using Excel
- Employee training tracker with Excel
- HR Dashboard using Excel – Video
- HR Dashboard using Power BI

The post How to calculate the Gender Pay Gap using Excel Formulas? (Free Calculator Template) appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>What is weighted average?

Wikipedia defines weighted average as, "The weighted mean is similar to an arithmetic mean ..., where instead of each of the data points contributing equally to the final average, some data points contribute more than others."

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

The post Weighted Average in Excel [Formulas] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>Weighted average or weighted mean is defined as [from wikipedia],

The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.

…If all the weights are equal, then the weighted mean is the same as the arithmetic mean.

**Well, it is because, in some situations normal averages give in-correct picture.** For eg. assume you are the CEO of ACME Widgets co.. Now you are looking annual salary report and being the numbers-gal you are, you wanted to find-out the average salary of your employees. You asked each department head to give you the average salary of that department to you. Here are the numbers,

Now, the average salary seems to be $ 330,000 [total all of all salaries by 5, (55000+65000+75000+120000+1200000)/5 ].

You are a happy boss to find that your employees are making $330k per year.

**Except, you are wrong.** You have not considered the number of employees in each department before calculating the average. So, the correct average would be $76k as shown above.

There is no built-in formula in Excel to calculate weighted averages. However, there is an easy fix to that. **You can use SUMPRODUCT formula**. By definition, SUMPRODUCT formula takes 2 or more lists of numbers and returns the sum of product of corresponding values. [related: Excel SUMPRODUCT Formula – what is it and how to use it?]

**So, if you have values in B4:B8 and the corresponding weights in C4:C8,** you can use SUMPRODUCT like this to get weighted average.

**=SUMPRODUCT(B4:B8, C4:C8)**

**Caution: **However, the above method works only if C4:C8 contains weights in percentages(%) totaling to 100%.

`=SUMPRODUCT(<your values>, <your weights>)`

May be your weights are more than 100 percent. Or may be they are less than 100 percent. In both cases, you can use the below formula variation.

**=SUMPRODUCT(B15:B19, C15:C19) / SUM(C15:C19) **

The idea is to divide the total of weights with the SUMPRODUCT result so that we can adjust Weighted Average as the weights don’t add up to 100 percent.

`=SUMPRODUCT(<your values>, <your weights>) / SUM(<your weights>)`

If you have count of observations instead of weights, you can still use the SUMPRODUCT formula to calculate weighted average in Excel.

Here is the formula for above example:

**=SUMPRODUCT(B26:B30, C26:C30) / SUM(C26:C30) **

Notice that this formula is same as the formula for weighted average with weights not adding up to 100 percent.

`=SUMPRODUCT(<your values>, <your counts>) / SUM(<your counts>)`

Let’s say you have city wise observations and weights. And you want to calculate the weighted average, only for ** Boston **values. In this case, you can use a variation of the formula like below:

**=SUMPRODUCT((C5:C16)*(B5:B16=F5),D5:D16)/SUMIFS(D5:D16,B5:B16,F5)**

**How does this formula work?**

- SUMPRODUCT calculates the total value for BOSTON by summing up C5:C16 (value column) where B5:B16 is Boston (highlighted portion of the formula) and multiplies that with the counts.
- So in the above example, this will just give us the total of Boston – ie 218,600
- We then divide this with the total count of Boston (using the SUMIFS formula) – ie 400
- This results in the weighted average for Boston values alone – ie 546.50

For more information on how the conditions work inside SUMPRODUCT formula, please read this article.

In this workbook, you can find 4 examples on how to to calculate weighted average in excel. Go ahead and download it to understand the formulas better.

Here is a video with Weighted Average formula explained. Please watch it below to learn more. Alternatively, head to my YouTube page to see the weighted averages in Excel video.

Weighted averages are a great way to explain data and every data analyst should know how and when to use them with their data. Apart from Weighted Average, I suggest learning how to use moving average and average of top n values. These will help you explain the data and trends to your audience better.

What do you use it for? What kind of challenges you face? Do you apply any tweaks to weighted average calculations? **Please share your ideas / tips using comments.**

- Syntax and Examples of Excel Average Formula
- Formula for Average of Top 5 values
- Calculating Moving Average in Excel
- Using SUBTOTAL formula and calculating averages
- Showing Averages in Pivot Tables

The post Weighted Average in Excel [Formulas] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>The post How to get non-adjacent columns with FILTER function in Excel appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>Imagine you have a table data named “staff” and you want to see all the staff who joined in year 2021. We can use below FILTER function for that.

**=FILTER(staff, YEAR(staff[date of join])=2021)**

This will provide a list of all staff who joined in year 2021, as depicted below.

But we don’t want all columns, just ID, Gender, Salary and Leave Balance.

To see just columns 1,2,7 & 8 of this filtered data, we can use below formula.

**=CHOOSECOLS(
FILTER(staff, YEAR(staff[date of join])=2021),
1,2,7,8)**

This will give you exactly what you need without anything else.

Say, you do want the columns 2,6,8&9 but you want them to show up in the order 6,8,2&9 in the final output.

You can still use the CHOOSECOLS function like below.

**=CHOOSECOLS(**

FILTER(staff, YEAR(staff[date of join])=2021),

6,8,2,9)

If you want to use a range of column names and show filtered data for only those columns, we can use XMATCH along with CHOOSECOLS and FILTER, as demoed below.

- Set up your column headers in a range like Z5:AC5
- Now, we can use XMATCH to find the positions of these headers. =XMATCH(Z5:AC5, staff[#headers])
- When you pass the result of XMATCH to CHOOSECOLS, you can pick these columns.

**=CHOOSECOLS(
** ** FILTER(staff,YEAR(staff[Date of Join])=2021),
XMATCH(Z5:AC5,staff[#Headers]))**

- Let’s go inside out.
- The FILTER() function gets all the staff data for people whose joining date is in 2021.
**Range Z5:AC5 holds the names of the columns we want to see.**- XMATCH(Z5:AC5, staff[#Headers]) will tell you the column numbers for the columns you want by looking them up in the table header row.
- CHOOSECOLS() will then return those exact columns

- How to use FILTER function in Excel, FILTER function video tutorial
- How to use XLOOKUP function in Excel
- How to use tables in Excel

The post How to get non-adjacent columns with FILTER function in Excel appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>