Traducir el blog
Salary Data Comparator
🔝To translate this blog post to your language, select it in the top left Google box.
I have designed this Salary Data Comparator to know the salary of data professionals:
- Data Analyst: Helps make decisions by analyzing, cleaning and transforming data.
- Data Scientist: Knows statistics, big data, algorithms, scientific computing, etc.
- Data Engineer: It is capable of designing databases, analyzing and processing segmented information (data pipeline), etc.
- AI/ML Engineer: Artificial Intelligence (AI) and Machine Learning (ML) Engineer.
In this animated image I compare the salaries in the USA vs Italy of these 4 types of data professionals.
I show the salary range between the minimum and the maximum salary, with the option of also showing the average salary, which is the most common among professionals in the two chosen countries.
The Italian Roberto Mensa is the greatest charts expert I know, so he has managed to be Microsoft Excel MVP.
His specialty is "doing the impossible" with Excel charts, and he demonstrates it in his Gallery:
A few weeks ago he published a radar chart with the Types of Data Professionals:
In addition to the radar chart, with the skills of the 4 types of data professionals, it also represents the popularity of these professionals in a line chart, and the salary range of these 4 professions in a scatter chart, in thousands of US dollars ( k$).
It does not specify whether the salary ranges are worldwide or what country those salaries are from. It is assumed that they are from the United States, since their salaries are considerably high compared to those in European countries.
I have uploaded Roberto's file to my OneDrive cloud with his authorization.
To adjust cloud zoom:
- On your mobile or cell phone, use two fingers on the screen, like you do to enlarge or reduce a photo.
- On the PC, place the cursor inside the search engine and press the <Control> key by turning the mouse wheel.
I have simply made the following changes to the Roberto's salary range chart:
- Salary range with the average salary marked with a diamond 🔶
- Option to show average salary.
- List of countries to compare.
- Selection of one or two countries.
- Labels of the types of professionals to the left of zero salary.
- Salaries in a country or comparator of salaries in two countries.
- Dynamic chart title to show the names of the countries compared.
- Comparator in 6 languages: English, Spanish, French, Italian, German and Portuguese.
- Exchange rate of 3 types of currencies: Euros (€), Pounds sterling (£) and American dollars ($).
The first thing I did was obtain the salary range data of the 4 types of data professionals for 9 countries, along with their average salary, and I saved them in a table on the 'Data' sheet:
I have decided to compare the salaries of 7 European countries, Mexico and the United States, to make it more international.
I obtained these annual salary data, in thousands of euros (k€), by asking the AI of Microsoft Copilot on Bing, with some Prompts that I am not going to reproduce here.
The AI responded with salary data imported from various sources in thousands of euros (€), as I requested.
When the currency rate changes, it will be necessary to update it in the cells in yellow background, with the conversion rate from pounds sterling (£) and US dollars ($) to euros (€).
Transformation of salary data
Roberto Mensa's version uses the latest Excel features, such as functions LAMBDA y LET, to obtain the series data from the chart.
The hidden sheet 'Support' contains the formulas for the scatter plot with straight lines and markers, with which the salary ranges are represented. In cell Q2 is the Spilled dynamic array formula with the X values of the series. In cell Q7 is the formula with the Y values of the series.
These formulas are very elegant and complex, since they ensure that the headers with the names of the series and their data are generated with a single formula.
The biggest problem is that using the LAMBDA function limits the chart to the versions of Excel for Microsoft 365 and Excel for the web, so it is not compatible with older versions of Excel.
It also adds a level of difficulty for Excel users who are unaware of these new functions, so there will be few who understand them and even fewer who can modify the functions of cells Q2 and Q7.
I haven't even tried to analyze and understand those formulas! and even less modify them!
Roberto, we need a detailed explanation of those formulas to understand them!
I am in favor of designing spreadsheets compatible with all possible versions of Excel, from Excel 2010 to Excel for Microsoft 365, since my followers are scattered around the world and many do not have the most recent versions of Excel installed. Which forces me to use functions common to all those versions of Excel and check compatibility following these guidelines:
My Salary Data Comparator transforms the data in the hidden 'Auxiliary' sheet.
The pivot table for the A:E columns has the data source in the salary table of the 'Data' sheet.
The pivot table for the M column contains the Average (Yes/No) option.
The auxiliary table of the range G1:K8 are the X values of the scatter chart series, obtained with formulas compatible with the GETPIVOTDATA function, that returns visible data from the dynamic salary table.
The auxiliary table of the range G12:K19 are the Y values of the scatter chart series, obtained with the auxiliary formulas of the range H11:K11 and the value of cell G22, to know if it has been filtered by a single country or by several countries.
Since the ranges H5:K5 and H16:K16 are empty, the straight lines that would join a salary range of the first country to be compared with the salary range of the second country disappear.
The value -50 in cell G11 is the X value of the series labels with the names of the 4 types of data professionals. This way the labels with the low values of the salary ranges do not overlap in the scatter chart!
The chart title is obtained in the G21 and J21 cells.
In the 'Languages' sheet there are the translations into the 6 languages of this Comparator:
The Salary Data Comparator is represented in a single XY scatter chart with straight lines and markers.
Salary ranges are represented with 4 data series, one for each type of data professional.
The labels of each type of professional are represented with 4 other data series.
The origin of these series are the auxiliary tables discussed in the previous section.
The range values are united by lines of different color for each of the 4 types of professionals.
The minimum and maximum values are marked with a circle 🔴 and the average values with a diamond 🔶.
The X axis goes from 0 to 250, in thousands of euros (€).
The Y axis goes from 2 to 18 and is not visible. It is used to separate the 8 possible visible salary ranges in the Salary Data Comparator, shown in the following image:
This is the result of comparing the salary ranges of the 4 types of data professionals from 2 countries.
With 3 segmentations you achieve:
- Filter from one to four types of data professionals.
- Filter by one or two countries.
- Filter the option to show (Yes) or not show (No) the average salary.
To change the language of the Salary Data Comparator there is a drop-down list in cell S2.
ATTENTION: After a Language change you must update chart with: Data > Refresh All (Control + Alt + F5)
ATTENTION: Update Currency if the language changes.
I've shared the Salary Data Comparator in the Microsoft OneDrive cloud, so it's easy to test even if you don't have Excel installed on your computer.
To adjust cloud zoom:
- On your mobile or cell phone, use two fingers on the screen, like you do to enlarge or reduce a photo.
- On the PC, place the cursor inside the search engine and press the <Control> key by turning the mouse wheel.
NOTICE: The changes you make in my cloud are not saved. To save your changes you have to make a copy in your OneDrive cloud.
The Salary Data Comparator is compatible with versions from Excel 2010 to Excel for Microsoft 365 and Excel for the web.
Download the latest version from one of these links:
- Microsoft OneDrive: Salary Data Comparator - PW1.xlsx
- Sites Google Drive: Salary Data Comparator - PW1.xlsx
Open the file and press the button: Enable Editing when the PROTECTED VIEW prompt appears.
Sheets and workbook are protected without password, so you can study and analyze the formulas, and view the hidden sheets.
ATTENTION: You can modify this Excel workbook respecting this license:
CC BY-NC-SA 4.0 Deed | Attribution-NonCommercial-ShareAlike 4.0 International | Creative Commons
This page is a classic on salary charts, so I can't help linking it here:
Do you know other types of Excel charts with salary ranges? Comment please!
![](http://www.blogger.com/img/icon18_email.gif)
Mi lista de blogs
-
New Australian Bridge Code and py-RC Elastic update 3 - The Australian Bridge Design Code for concrete (AS 5100.5) was recently updated with Amendment 2 to the 2017 version. There are significant changes in many...Hace 23 horas
-
Regresos 11: Construcciones con un número y sus cifras - Hace un tiempo, publiqué en Twitter (@connumeros) esta identidad: 22622*2*2*6*2*2=2171712 En ella, un número capicúa (o palindrómico), multiplicado por s...Hace 2 días
-
Introducing Checkboxes in Excel - We're excited to announce the release of Checkboxes to production in Excel. They are great for checklists, managing tasks and visualizing your data at a ...Hace 2 días
-
Carga Móvil: Comparte Archivos entre tu teléfono móvil y tu PC - ¿Qué navegador usas: Chrome, Edge, Firefox, Safari? Si no mencionaste Edge, en este post te daré una razón muy importante […] The post Carga Móvil: Compa...Hace 3 días
-
Can Excel Replace Your Moody Chart? Find Out How - If you’re like me, you’ve probably spent hours tinkering with Microsoft Excel to solve complex engineering problems, longing for ways to make your workflow...Hace 4 días
-
Salary Data Comparator - 🔝*To translate this blog post to your language, select it in the top left Google box. * Types of data professionals I have designed this *Salary Da...Hace 4 días
-
GENERAR SECUENCIA DE SUCESIÓN DE FIBONACCI CON POWER APPS - Hola a todos! En esta web hay varios post en los que genero el algoritmo de sucesión de Fibonacci en varios idiomas, VBA, Typescript, etc.. … La entrada...Hace 1 semana
-
Asignación automática de turnos en Excel: Cómo usar fórmulas para rango de horas 📑 - En el mundo de la gestión de horarios y turnos, Excel se destaca como una herramienta invaluable. Un suscriptor reciente me hizo una pregunta muy interesan...Hace 1 semana
-
11 Ways to Delete a Comment in Microsoft Excel - Do you want to learn how to delete a comment in Excel? Then you must follow this ultimate guide on Excel comments. Often, you add comments in your Excel wo...Hace 1 semana
-
Debra’s Excel News – June 2024 - There’s an Excel calendar, Excel lookup functions, and more, in this month’s Excel news. Excel Event Calendar Quick Ribbon Tip Excel Lookup Functions Excel...Hace 2 semanas
-
Facturando con Copilot Edge y Excel - Facturando con Copilot Edge y Excel es preciso, rápido y sencillo, a continuación te explico cómo. Introducción Facturando con Copilot Edge y Excel En el...Hace 3 semanas
-
Sales Analysis Dashboards with Power BI – 30+ Alternatives - Do you need inspiration for your upcoming Power BI sales dashboard? Well, I got you covered. In this page, let me present 33 alternatives for Sales Analy...Hace 5 semanas
-
-
Minutos de juego y puntos. El Espanyol, sus finales de partido y mis enfados - Pienso que el Espanyol este 2024 se está dejando muchos puntos al final de los partidos. Cuando el ... Leer más » The post Minutos de juego y puntos. El ...Hace 3 meses
-
TikTok’s search evolution - 2 in 5 Americans use TikTok as a search engine. Nearly 1 in 10 Gen Zers are more likely to rely on TikTok than Google as a search engine. More than half of...Hace 3 meses
-
MASTERCLASS Gratis – Gráfica de Gestión Proyectos en #EXCEL. - Aprende a crear un Gráfico de CURVA S, ideal para GESTIÓN DE PROYECTOS, porque te permite identificar como esta tu proyecto tanto en COSTOS como en TIEMP...Hace 4 meses
-
Interés compuesto con Excel - En la imagen, se presenta un esquema de las distintas funciones Excel para calcular el valor del dinero en el tiempo mediante la capitalización compuesta...Hace 5 meses
-
Demos cursos de Excel 2007, 2010, 2013, 2016, 365 - Puedes consultar las demostraciones de los siguientes capítulos de los cursos Excel. Demo cursos ExcelHace 5 meses
-
Atajos esenciales de Excel que todo profesional debe conocer - Manejar Excel con destreza puede ser el superpoder que no sabías que necesitabas. Agiliza tus tareas diarias y sorprende a tus colegas con estos atajos c...Hace 10 meses
-
Unblocking and Enabling Macros - When Windows detects that a file has come from a computer other than the one you're using, it marks the file as coming from the web, and blocks the file....Hace 1 año
-
-
Navigating Outlook Favorites - I have these four favorites defined in Outlook: From the inbox, I could hit Shift+F6 to get into the Favorites area but sometimes I would end up in no man’...Hace 1 año
-
Office Scripts: Trabajando con Tablas - [image: Office Scripts: Trabajando con Tablas] Me he dado cuenta que últimamente solo escribo de lenguaje M (es mi pequeño vicio)... pero hay que liberar l...Hace 1 año
-
Progress on the Block Protocol - Since the 1990s, the web has been a publishing place for human-readable documents. Documents published on the web are in HTML. HTML has a little bit of… Re...Hace 1 año
-
Hello world! - [image: Hello world!] Welcome to WordPress. This is your first post. Edit or delete it, then start writing!Hace 1 año
-
Decálogo para realizar Trabajos de Fin de Grado (TFGs) y de Fin de Máster (TFMs) - 1.- Tanto TFGs como TFMs son un requisito para graduarse a la vez que una oportunidad para aprender. Así, el tiempo que se le dedica es muy variable. De...Hace 2 años
-
London Excel Meetup Workbooks - The workbooks used in my presentation on “Analytical and Interactive Dashboards in Excel” at the London Excel Meetup, September 3, 2020Hace 3 años
-
Cálculo de jornada que termina al día siguiente (Power Query) y despedida - [image: Cálculo de jornada que termina al día siguiente (Power Query) y despedida] Este blog se ha ocupado de cálculos de tiempo con bastante intensidad, c...Hace 4 años
-
Dimensiones-Propiedades de perfiles de acero (IMCA) - Saludos compañeros, en esta oportunidad compartimos esta Planilla Excel que contiene las Propiedades y dimensiones de diferentes tipos de perfiles de acero...Hace 4 años
-
Visualize parts and whole - combine clustered column and stacked column charts - *Inga: Disa what?* *Igor: -ppeared.* by The FrankensTeam ------------------------------ Really it was 3 years ago we posted our last article? *Freddy: Th...Hace 5 años
-
-
-
-
-
-
-
-
-
No Response to "Salary Data Comparator"
Leave A Reply
Dime si te gusta lo que lees y, si no te gusta, dime por qué. Tengo habilitada la moderación de comentarios. Tu comentario se publicará pronto.