Median vs Average wages

by | Feb 7, 2023 | Excel

Why do politi­cians lie to us when talk­ing about aver­age wages? Let’s ask Excel.

Medi­an vs Aver­age wages 

Let’s cal­cu­late some aver­age. The AVERAGE func­tion is a sim­ple func­tion that sums up val­ues from all cells in a range and divides it by the num­ber of cells with val­ues (Fig. 1). 

=AVERAGE(D2:D12)

AVERAGE function
Fig. 1 AVERAGE function

In our exam­ple, the aver­age is $2,900. It means that 8 peo­ple earn less, which gives us around 80% of all peo­ple. It’s impor­tant that one per­son at the bot­tom earns much more than the rest and affects our aver­age more than oth­er peo­ple (Fig. 2). 

Average wages
Fig. 2 Aver­age wages

If we want to talk more pre­cise­ly about the dis­tri­b­u­tion of those wages, we can use the MEDIAN func­tion which returns the val­ue in the mid­dle (Fig. 3). 

=MEDIAN (D2:D12)

MEDIAN function
Fig. 3 MEDIAN function

Since our val­ues are sort­ed, we can clear­ly see the mid­dle val­ue, which is $2,000. We can see that 50% of peo­ple earn this val­ue or less and 50% of peo­ple earn this val­ue or more (Fig. 4). 

Median value
Fig. 4 Medi­an value

If we have an even num­ber of val­ues and there are two mid­dle val­ues, the MEDIAN func­tion cal­cu­lates the aver­age of those two val­ues near the mid­dle line (Fig. 5).

=MEDIAN(G2:G11)

MEDIAN function with even number of cells
Fig. 5 MEDIAN func­tion with even num­ber of cells

We can see that it cal­cu­lat­ed the mid­dle val­ue (Fig. 6). 

Median value
Fig. 6 Medi­an value

If we add one zero to cell D12 which belongs only to one per­son, we can see that the aver­age hit $11,900. It means that only one per­son earns more that the aver­age. On the oth­er hand, the medi­an val­ue stayed the same. It’s very impor­tant to know this dif­fer­ence (Fig. 7). 

Higher average
Fig. 7 High­er average

I also want to add one col­umn with the RAND func­tion (Fig. 8).

=RAND()

RAND function
Fig. 8 RAND function

I want to show you that we don’t have to have our data sort­ed to use the MEDIAN func­tion (Fig. 9). 

Random order, median number the same
Fig. 9 Ran­dom order, medi­an num­ber the same

https://www.youtube.com/watch?v=wLV-xl1735s

SUMIFS AVERAGEIFS and COUNTIFS functions SUM with conditions

by | Nov 15, 2022 | Excel

Today, we are count­ing sums, aver­ages and num­ber of trans­ac­tions with more than one con­di­tion. In our exam­ple, we have trans­ac­tion sales table and we want to count the val­ues for giv­en months. How­ev­er, the months are writ­ten as the first day of the month and the last day of the month. It means that we have to add two con­di­tions in order to count it. Let’s start with sums. We can use the SUMIFS func­tion. The first argu­ment of this func­tion is a range on which we will be sum­ming. In our case it’s the sales col­umn so we have to select cell C2 and press the Ctrl + Shift + ↓ com­bi­na­tion, then the F4 key to lock it (Fig. 1).

=SUMIFS($C$2:$C$1193

SUMIFS AVERAGEIFS and COUNTIFS func­tions SUM with conditions
SUMIFS function with a locked range
Fig. 1 SUMIFS func­tion with a locked range

We write a com­ma, and then we are going to the cri­te­ria range 1. This will be the range where we will be check­ing the first cri­te­ri­on, which is the third argu­ment of the SUMIFS func­tion. In our case, we are check­ing dates, so let’s press cell A2, Ctrl + Shift + ↓, then the F4 key. We are adding a com­ma again, and now we have to write the first con­di­tion, which is the first cri­te­ri­on. The order of our con­di­tions is not impor­tant because, in the end, all con­di­tions have to be met to count the thing we want. Let’s start with the From date. We want val­ues that are big­ger or equal to this date, which means that we have to write the sym­bols of larg­er than and equal in dou­ble quotes, then an amper­sand because we are com­bin­ing this text with the val­ue from cell E2 (Fig. 2).

=SUMIFS($C$2:$C$1193,$A$2:$A$1193,”>=”&E2

SUMIFS with the first condition
Fig. 2 SUMIFS with the first condition

After press­ing the F9 key, we can see that Excel changed the date into a num­ber. This makes Excel check our con­di­tion prop­er­ly (Fig. 3).

=SUMIFS($C$2:$C$1193,$A$2:$A$1193,“>=43831”

Excel changed the dated
Fig. 3 Excel changed the dates

How­ev­er, let’s move back to pre­vi­ous text by press­ing Ctrl + Z. Now, we have the first con­di­tion. We can see that the sec­ond con­di­tion is nec­es­sary with sec­ond critiri­on because the next two argu­ments are in square brack­ets (Fig. 4).

=SUMIFS($C$2:$C$1193,$A$2:$A$1193,”>=”&E2

Arguments in square brackets
Fig. 4 Argu­ments in square brackets

Cri­te­ria range 2 in our case is the same as cri­te­ria range 1, so we can just copy it (Ctrl +C, Ctrl +V), but we real­ly need to put it because each cri­te­ria range is work­ing just with the argu­ment of its cri­te­ria. It means that cri­te­ria range 1 is work­ing with cri­te­ria 1, and cri­te­ria range 2 is work­ing with cri­te­ria 2. In our case, the cri­te­ria 2 are num­bers small­er than or equal to the last day of the month in cell F2. 

Now, we have the whole for­mu­la (Fig. 5). 

=SUMIFS($C$2:$C$1193,$A$2:$A$1193,”>=”&E2,$A$2:$A$1193,”<=”&F2)

Whole SUMIFS formula
Fig. 5 Whole SUMIFS formula

When we want to sum the val­ues from the sum range, the first and the sec­ond con­di­tion have to be met. Let’s put the for­mu­la and copy it down. We can see that at the end every­thing is work­ing prop­er­ly because we have writ­ten the for­mu­la prop­er­ly (Fig. 6).

Proper results
Fig. 6 Prop­er results

We still have some more val­ues to count. Let’s take the aver­ages. Since I’m a lazy per­son, I will copy the whole for­mu­la in edit mode, then go to the Aver­age col­umn, paste it and change only the name of SUMIFS into AVERAGEIFS. After enter­ing it, we can see that only the sum range changed its name into aver­age range. The remain­ing argu­ment names stayed the same. It’s because the SUMIFS and the AVERAGEIFS for­mu­las have the same syn­tax, but they count sums and aver­ages cor­re­spond­ing­ly (Fig. 7). 

Argument name change
Fig. 7 Func­tion name change

Let’s put the for­mu­la into our cell and copy it down. We have the results (Fig 8).

SUMIFS reults
Fig. 8 SUMIFS reults

Let’s now count the num­ber of trans­ac­tions. We will use the COUNTIFS func­tion. This func­tion does­n’t have any range on which we could count sums, aver­ages and so on. It only needs cri­te­ria ranges and cri­te­ria. Our first cri­te­ria was the one with dates. So, let’s write A2, then press Ctrl + Shift + ↓, then the F4 key to lock it and a com­ma. I want to show you that the order of con­di­tions is not impor­tant. Let’s take val­ues small­er that or equal to the date in cell F2, com­ma, then the same cri­te­ria range, then the sec­ond cri­te­ria will be greater than or equal to the val­ue from cell E2. Those are all the argu­ments we need for a COUNTIFS func­tion (Fig. 9).

=COUNTIFS($A$2:$A$1193,”<=”&F2,$A$2:$A$1193,”>=”&E2)

COUNTIFS formula
Fig. 9 COUNTIFS formula

After copy­ing the for­mu­la down, we have the results. We can check if our aver­age is cor­rect just by look­ing at it because it is just the sum divid­ed by the num­ber of trans­ac­tions (Fig. 10).

Checking the results
Fig. 10 Check­ing the results

We can see that the num­bers are exact­ly the same as in the Aver­age col­umn (Fig. 11). 

The same numbers
Fig. 11 AVERAGEIFS and divide gives the same results

https://www.youtube.com/watch?v=oei27KKIRPk