0321 Using Average to get Designation Wise Experience result

Advanced Excel Dashboard Crash Course Section 3: 2nd Dashboard using Functions
3 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€92.30
List Price:  €129.22
You save:  €36.92
£79.14
List Price:  £110.81
You save:  £31.66
CA$135.92
List Price:  CA$190.30
You save:  CA$54.37
A$153.17
List Price:  A$214.44
You save:  A$61.27
S$134.71
List Price:  S$188.59
You save:  S$53.88
HK$782.26
List Price:  HK$1,095.19
You save:  HK$312.93
CHF 90.56
List Price:  CHF 126.79
You save:  CHF 36.22
NOK kr1,076.34
List Price:  NOK kr1,506.92
You save:  NOK kr430.58
DKK kr688.47
List Price:  DKK kr963.88
You save:  DKK kr275.41
NZ$166.49
List Price:  NZ$233.09
You save:  NZ$66.60
د.إ367.19
List Price:  د.إ514.08
You save:  د.إ146.89
৳11,000.23
List Price:  ৳15,400.77
You save:  ৳4,400.53
₹8,335.05
List Price:  ₹11,669.40
You save:  ₹3,334.35
RM473.25
List Price:  RM662.57
You save:  RM189.32
₦139,656.03
List Price:  ₦195,524.03
You save:  ₦55,868
₨27,872.43
List Price:  ₨39,022.53
You save:  ₨11,150.09
฿3,637.73
List Price:  ฿5,092.97
You save:  ฿1,455.24
₺3,222.80
List Price:  ₺4,512.05
You save:  ₺1,289.25
B$497.67
List Price:  B$696.75
You save:  B$199.08
R1,888.37
List Price:  R2,643.79
You save:  R755.42
Лв180.67
List Price:  Лв252.95
You save:  Лв72.27
₩134,942
List Price:  ₩188,924.20
You save:  ₩53,982.20
₪366.06
List Price:  ₪512.51
You save:  ₪146.44
₱5,625.18
List Price:  ₱7,875.48
You save:  ₱2,250.29
¥15,118.78
List Price:  ¥21,166.90
You save:  ¥6,048.12
MX$1,663.06
List Price:  MX$2,328.35
You save:  MX$665.29
QR365.44
List Price:  QR511.63
You save:  QR146.19
P1,373.02
List Price:  P1,922.28
You save:  P549.26
KSh13,148.68
List Price:  KSh18,408.68
You save:  KSh5,260
E£4,723.53
List Price:  E£6,613.14
You save:  E£1,889.60
ብር5,694.53
List Price:  ብር7,972.57
You save:  ብር2,278.04
Kz83,612.73
List Price:  Kz117,061.17
You save:  Kz33,448.44
CLP$98,024.19
List Price:  CLP$137,237.79
You save:  CLP$39,213.60
CN¥722.49
List Price:  CN¥1,011.52
You save:  CN¥289.02
RD$5,928.03
List Price:  RD$8,299.48
You save:  RD$2,371.45
DA13,469.95
List Price:  DA18,858.47
You save:  DA5,388.52
FJ$225.25
List Price:  FJ$315.36
You save:  FJ$90.11
Q781.84
List Price:  Q1,094.62
You save:  Q312.77
GY$20,987.69
List Price:  GY$29,383.60
You save:  GY$8,395.91
ISK kr13,817.61
List Price:  ISK kr19,345.21
You save:  ISK kr5,527.60
DH1,010.57
List Price:  DH1,414.84
You save:  DH404.26
L1,763.81
List Price:  L2,469.40
You save:  L705.59
ден5,677.01
List Price:  ден7,948.05
You save:  ден2,271.03
MOP$807.66
List Price:  MOP$1,130.76
You save:  MOP$323.09
N$1,895.98
List Price:  N$2,654.45
You save:  N$758.46
C$3,689.21
List Price:  C$5,165.04
You save:  C$1,475.83
रु13,357.88
List Price:  रु18,701.57
You save:  रु5,343.68
S/371.02
List Price:  S/519.45
You save:  S/148.42
K383.60
List Price:  K537.05
You save:  K153.45
SAR375.02
List Price:  SAR525.04
You save:  SAR150.02
ZK2,548.47
List Price:  ZK3,567.96
You save:  ZK1,019.49
L458.83
List Price:  L642.38
You save:  L183.55
Kč2,334.38
List Price:  Kč3,268.22
You save:  Kč933.84
Ft36,425.52
List Price:  Ft50,997.18
You save:  Ft14,571.66
SEK kr1,059.50
List Price:  SEK kr1,483.35
You save:  SEK kr423.84
ARS$85,666.43
List Price:  ARS$119,936.42
You save:  ARS$34,269.99
Bs695.09
List Price:  Bs973.16
You save:  Bs278.06
COP$389,673.99
List Price:  COP$545,559.18
You save:  COP$155,885.18
₡50,509.65
List Price:  ₡70,715.54
You save:  ₡20,205.88
L2,474.33
List Price:  L3,464.17
You save:  L989.83
₲736,638.54
List Price:  ₲1,031,323.43
You save:  ₲294,684.88
$U3,781
List Price:  $U5,293.55
You save:  $U1,512.55
zł398.17
List Price:  zł557.46
You save:  zł159.28
Already have an account? Log In

Transcript

Hello everybody, welcome to the second dashboard. In this video we will see how to get data for each division in yearly experience slabs. So what is the salary paid out, the first thing will keep the cursor on j five now put in the formula equals average if over here as we have multiple parameters, that is one, this is the division and the other one is experience. So we'll go with average ifs. But the problem we might have is that when we put in the formula, it will overlap the reference or the cells which will be difficult to select. So the best option is to first select the range where the formula has to be put in.

Rather than writing the formula into the cell directly just write the formula into the formula bar which helps us with overlapping cells. Something like these equals two average ifs, I'll press the Tab key Complete the formula. The first range that is what is the average range in actual and will that is converted salary from the data selected by the user. Next criteria range, the criteria range is also designation from the data. Now, these are all something that I have created, the same method is being used as we did for the countries that converted salaries and so on TAB key that will complete the formula. Now, from the designation which is selected by the user, I'll be looking out for the admin as of now, where the I column will be constant because I'm trying to cross where the I column has to be constant.

That's for the first criteria and the criteria range. Now comes the second criteria range. second criteria range is experience so experienced group from the data, the same as I've done for converted salary designation from the data. So the same logic is applicable for experience group from a data, just keep it simple and say it is from data common experience from the top 00 to one zero. Here the fourth row will be constant because all the experiences mentioned in the fourth row. Once the selection is done, close the bracket.

Now completely fill up this particular formula in the selected area, the shortcut we can use is Ctrl. Enter Ctrl Enter will directly fill the data in the selected reference. To change it into a number format we'll be using Ctrl Shift exclamation mark and that will convert it to a number format with two decimals. Now comes the fun part. So as we can see, there are some errors because there is no data provided in the actual data for example, the IT field under the year experience of 31 and 40. There are no employees yet assigned.

So to remove these errors, we will be using it Oops, sorry for that, again, we will go into the formula bar will say if error and then at the extreme end we'll put in a comma that is value for error. We cannot put zero because zero stands that average itself is zero, and we cannot give the wrong information. So we'll keep it as blank. To do so just put in a double quotation, and a double quotation once again without putting any spaces or characters into the blanks. Close the bracket again Ctrl, enter and there it is, we have the average results to the 31st. In the next video, we'll see the number of total salaries and the number of employees using the same concept.

Till then keep practicing. See you in the next video. Thank you

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.