kumara_faith
Well-known Member
- Joined
- Aug 19, 2006
- Messages
- 953
- Office Version
- 365
Hi,
I have the following table:
1/ The first table to the left is the average ticket for the month of January. It also shows the upper limit and the lower limit of the ticket for January.
2/ The second table in the middle shows the actual ticket volume for Nov and the daily SLA met by the respective branches.
3/ The third table to the right shows if the Nov daily ticket exceeded the upper limit of below the lower limit as compared to the month of January based on the date on cell P1
4/ In column S, I am trying to populate the remarks for each branch on daily basis.
5) The branch name is in column B. Daily SLA to be achieved is 90%.
The criteria are as follows:
a) Look at the date at cell P1 and match the data in column G:L and check the daily ticket for that day
b) Compare the daily ticket based on (a) and compare the daily ticket with the upper limit and lower limit in column D and E.
c) If the daily ticket in (b) is above the upper limit in column D, then return "<Branch Name> exceeded the upper limit volume by <upper limit % from column P>. SLA is above/below <from daily SLA%>"
d) If the daily ticket in (b) is below the lower limit in column E, then return "<Branch Name> ticket volume is below the lower limit volume by <lower limit % from column Q>. SLA is above/below <from daily SLA%>"
e) If the daily ticket in (b) is below the upper limit in column D, and higher than the lower limit in column E, and the daily SLA % is above 90%, then return "SLA is achieved at <SLA %>"
f) If the daily ticket in (b) is below the upper limit in column D, and higher than the lower limit in column E, and the daily SLA % is below 90%, then return "SLA is below KPI at <SLA %>"
The current formula in column S seems to be working. However for Nov 3, the daily ticket for Branch1 is 11 which is lower than the upper limit and higher than the lower limit. Based on the above crietria, the formula should return "SLA achieved at 91%". However, the formula returns "SLA is below KPI at 89%".
Appreciate any help on how to correct this.
I have the following table:
| QMS Anomaly Remarks.xlsx | ||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | |||
| 1 | 3-Nov | |||||||||||||||||||
| 2 | Branch | Jan Average | Jan Upper | Jan Lower | 1-Nov | 1 Nov SLA | 2-Nov | 2 Nov SLA | 3-Nov | 3 Nov SLA | MTD SLA | Upper Limit | Lower Limit | Daily Anomaly Remarks | ||||||
| 3 | Branch1 | 10 | 12 | 8 | 19 | 89% | 7 | 93% | 11 | 91% | 95% | 0% | 0% | SLA below KPI at 89%. | ||||||
| 4 | Branch2 | 12 | 14 | 10 | 21 | 87% | 21 | 87% | 21 | 85% | 95% | 50% | 0% | Branch2 exceeded the upper limit volume by 50%. SLA below KPI at 85%. | ||||||
| 5 | Branch3 | 14 | 16 | 12 | 15 | 98% | 15 | 98% | 15 | 98% | 89% | 0% | 0% | SLA achieved at 98%. | ||||||
| 6 | Branch4 | 12 | 14 | 10 | 19 | 95% | 19 | 95% | 19 | 95% | 95% | 36% | 0% | Branch4 exceeded the upper limit volume by 35.71%. SLA achieved at 95%. | ||||||
| 7 | Branch5 | 15 | 17 | 13 | 18 | 96% | 18 | 96% | 18 | 96% | 87% | 6% | 0% | Branch5 exceeded the upper limit volume by 5.88%. SLA achieved at 96%. | ||||||
| 8 | Branch6 | 18 | 20 | 16 | 10 | 98% | 10 | 98% | 10 | 98% | 95% | 0% | 38% | Branch6 ticket volume is below the lower limit by 37.5%. SLA achieved at 98%. | ||||||
| 9 | Branch7 | 19 | 21 | 17 | 18 | 98% | 18 | 98% | 18 | 98% | 95% | 0% | 0% | SLA achieved at 98%. | ||||||
| 10 | Branch8 | 20 | 22 | 18 | 19 | 98% | 19 | 98% | 19 | 98% | 91% | 0% | 0% | SLA achieved at 98%. | ||||||
Sheet1 | ||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| P3:P10 | P3 | =IF(ISNUMBER(MATCH($P$1, G$2:L$2, 0)), IF(INDEX(G3:L3, MATCH($P$1, G$2:L$2, 0)) > D3, ROUND((INDEX(G3:L3, MATCH($P$1, G$2:L$2, 0)) - D3) / D3, 4), 0), 0) |
| Q3:Q10 | Q3 | =IF(ISNUMBER(MATCH($P$1, G$2:L$2, 0)), IF(INDEX(G3:L3, MATCH($P$1, G$2:L$2, 0)) < E3, ROUND(ABS(INDEX(G3:L3, MATCH($P$1, G$2:L$2, 0)) - E3) / E3, 4), 0), 0) |
| S3:S10 | S3 | =IF(ISNUMBER(MATCH(TEXT($P$1, "d mmm") & " SLA", G$2:L$2, 0)), IF(AND(P3=0, Q3=0), IF(H3 >= 0.9, "SLA achieved at " & ROUND(H3*100, 2) & "%.", "SLA below KPI at " & ROUND(H3*100, 2) & "%." ), IF(AND(P3 > 0, Q3 > 0), B3 & " exceeded the upper limit volume by " & ROUND(P3*100, 2) & "% and ticket volume is below the lower limit by " & ROUND(Q3*100, 2) & "% respectively. " & IF(H3 >= 0.9, "SLA achieved at " & ROUND(H3*100, 2) & "%.", "SLA below KPI at " & ROUND(H3*100, 2) & "%." ), IF(P3 > 0, B3 & " exceeded the upper limit volume by " & ROUND(P3*100, 2) & "%. " & IF(INDEX(G3:L3, MATCH(TEXT($P$1, "d mmm") & " SLA", G$2:L$2, 0)) >= 0.9, "SLA achieved at " & ROUND(INDEX(G3:L3, MATCH(TEXT($P$1, "d mmm") & " SLA", G$2:L$2, 0))*100, 2) & "%.", "SLA below KPI at " & ROUND(INDEX(G3:L3, MATCH(TEXT($P$1, "d mmm") & " SLA", G$2:L$2, 0))*100, 2) & "%." ), IF(Q3 > 0, B3 & " ticket volume is below the lower limit by " & ROUND(Q3*100, 2) & "%. " & IF(INDEX(G3:L3, MATCH(TEXT($P$1, "d mmm") & " SLA", G$2:L$2, 0)) >= 0.9, "SLA achieved at " & ROUND(INDEX(G3:L3, MATCH(TEXT($P$1, "d mmm") & " SLA", G$2:L$2, 0))*100, 2) & "%.", "SLA below KPI at " & ROUND(INDEX(G3:L3, MATCH(TEXT($P$1, "d mmm") & " SLA", G$2:L$2, 0))*100, 2) & "%." ), IF(INDEX(G3:L3, MATCH(TEXT($P$1, "d mmm") & " SLA", G$2:L$2, 0)) >= 0.9, "SLA achieved at " & ROUND(INDEX(G3:L3, MATCH(TEXT($P$1, "d mmm") & " SLA", G$2:L$2, 0))*100, 2) & "%.", "SLA below KPI at " & ROUND(INDEX(G3:L3, MATCH(TEXT($P$1, "d mmm") & " SLA", G$2:L$2, 0))*100, 2) & "%." ) ) ) ) ), "No match in Date. Check again." ) |
1/ The first table to the left is the average ticket for the month of January. It also shows the upper limit and the lower limit of the ticket for January.
2/ The second table in the middle shows the actual ticket volume for Nov and the daily SLA met by the respective branches.
3/ The third table to the right shows if the Nov daily ticket exceeded the upper limit of below the lower limit as compared to the month of January based on the date on cell P1
4/ In column S, I am trying to populate the remarks for each branch on daily basis.
5) The branch name is in column B. Daily SLA to be achieved is 90%.
The criteria are as follows:
a) Look at the date at cell P1 and match the data in column G:L and check the daily ticket for that day
b) Compare the daily ticket based on (a) and compare the daily ticket with the upper limit and lower limit in column D and E.
c) If the daily ticket in (b) is above the upper limit in column D, then return "<Branch Name> exceeded the upper limit volume by <upper limit % from column P>. SLA is above/below <from daily SLA%>"
d) If the daily ticket in (b) is below the lower limit in column E, then return "<Branch Name> ticket volume is below the lower limit volume by <lower limit % from column Q>. SLA is above/below <from daily SLA%>"
e) If the daily ticket in (b) is below the upper limit in column D, and higher than the lower limit in column E, and the daily SLA % is above 90%, then return "SLA is achieved at <SLA %>"
f) If the daily ticket in (b) is below the upper limit in column D, and higher than the lower limit in column E, and the daily SLA % is below 90%, then return "SLA is below KPI at <SLA %>"
The current formula in column S seems to be working. However for Nov 3, the daily ticket for Branch1 is 11 which is lower than the upper limit and higher than the lower limit. Based on the above crietria, the formula should return "SLA achieved at 91%". However, the formula returns "SLA is below KPI at 89%".
Appreciate any help on how to correct this.
