NotTheBestTool
New Member
- Joined
- Mar 2, 2022
- Messages
- 16
- Office Version
- 2016
- Platform
- Windows
I need help with cell F109 and G109
Excel Exam - Reporting Analyst 1.xlsb | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
C | D | E | F | G | H | I | J | |||
79 | Table 4 contains a project's monthly statistics for the year 2018. | |||||||||
80 | ||||||||||
81 | a. Find the quarterly and annual (2018) AHT and ASA | |||||||||
82 | b. Determine how many months the target was achieved for AHT and ASA (targets indicated below on E106:F106) | |||||||||
83 | c. Highlight values meeting the target with GREEN and values not meeting the target with RED (it can be either cell fill or icons) | |||||||||
84 | d. Using the data in Table 4, create a chart similar to the one shown on the right; place your chart below the sample | |||||||||
85 | e. Enter the monthly percentage change in terms of calls handled in the column provided then highlight the highest growth with GREEN and the highest drop with RED | |||||||||
86 | ||||||||||
87 | ||||||||||
88 | Table 4. Monthly Project Statistics 2018 | |||||||||
89 | Month | Quarter | Calls Handled | AHT | ASA | Monthly % Change (Calls) | ||||
90 | Jan 2018 | Q1 | 1,023 | 0:07:03 | 0:00:32 | |||||
91 | Feb 2018 | Q1 | 923 | 0:05:37 | 0:00:43 | -9.78% | ||||
92 | Mar 2018 | Q1 | 978 | 0:05:53 | 0:00:38 | 5.96% | ||||
93 | Apr 2018 | Q2 | 863 | 0:06:59 | 0:00:50 | -11.76% | ||||
94 | May 2018 | Q2 | 781 | 0:07:18 | 0:00:56 | -9.50% | ||||
95 | Jun 2018 | Q2 | 698 | 0:07:12 | 0:01:47 | -10.63% | ||||
96 | Jul 2018 | Q3 | 720 | 0:08:07 | 0:01:43 | 3.15% | ||||
97 | Aug 2018 | Q3 | 631 | 0:08:45 | 0:01:57 | -12.36% | ||||
98 | Sep 2018 | Q3 | 786 | 0:09:01 | 0:02:00 | 24.56% | ||||
99 | Oct 2018 | Q4 | 771 | 0:08:42 | 0:01:26 | -1.91% | ||||
100 | Nov 2018 | Q4 | 660 | 0:08:40 | 0:01:52 | -14.40% | ||||
101 | Dec 2018 | Q4 | 614 | 0:09:27 | 0:00:38 | -6.97% | ||||
102 | ||||||||||
103 | Q1 | 2,924 | 0:06:11 | 0:00:38 | ||||||
104 | Q2 | 2,342 | 0:07:10 | 0:01:11 | ||||||
105 | Q3 | 2,137 | 0:08:38 | 0:01:53 | ||||||
106 | Q4 | 2,045 | 0:08:56 | 0:01:19 | ||||||
107 | OVERALL | 9,448 | 0:07:44 | 0:01:15 | ||||||
108 | TARGET ≤ | 0:08:00 | 0:01:30 | |||||||
109 | MONTHS PASSED TARGET | 0 | 0 | |||||||
Questionnaire |
Cell Formulas | ||
---|---|---|
Range | Formula | |
H91:H101 | H91 | =(E91-E90)/E90 |
E103:E106 | E103 | =SUMIFS(E$90:E$101, $D$90:$D$101, $D103) |
F103:F106 | F103 | =AVERAGEIFS(F90:F101,D90:D101,D103) |
G103:G106 | G103 | =AVERAGEIFS(G90:G101,D90:D101,D103) |
E107 | E107 | =SUM(E90:E101) |
F107:G107 | F107 | =AVERAGE(F103:F106) |
F109 | F109 | =COUNTIF(F103:F106,"<F108") |
G109 | G109 | =COUNTIF(G103:G107,"<G108") |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
H91:H101 | Cell Value | top 1 bottom values | text | NO |
H91:H101 | Cell Value | top 1 values | text | NO |