## Forum Posts

Chris Gage

Nov 05, 2020

In Excel Formulas

4 Formulas to Calculate Ratio in Excel Simple Divide Method GCD Function SUBSTITUTE and TEXT Using Round Function 1. Calculate Ratio by using Simple Divide Method We can use this method when the larger value is divisible with the smaller value. In the below example, we have value 10 and 2, where 10 is divisible with 2. 📷 So you can use this method here. Insert the below formula into the cell and hit enter. =A2/B2&”:”&”1″ 📷 here's how this formula works... In this formula, we have divided 10 with 2 which gives you 5 in return. So now we have 5 instead of 10 by dividing with 2. And on the other side, we have used 1 instead of 2. 📷 PROs: #1. Simple to apply. #2. Easy to understand. CONs: #1. Limited use. #2. Not applicable if the larger value is not divisible with the smaller value. 2. GCD Function to Calculate Ratio in Excel As I said, there is no single function in excel to calculate the ratio for you. But, GCD function is close enough. It can help you to get common denomination for both of the values and then by using little concatenation, you can calculate the ratio. 📷 And to calculate the ratio insert below formula into the cell and hit enter. =A2/GCD(A2,B2)&”:”&B2/GCD(A2,B2) 📷 here's how this formula works... Maybe it looks complex to you but it’s simple in real. Let me explain you this in two parts. 📷 As I said, GCD can give you a common denominator for both of the values. So here you have to use GCD in both of the parts of the formula to get a common denominator. After that, you have to divide both of the values with that common denominator. And, in the end, a little concatenation to join both of the values using a colon. PROs: #1. It's a dynamic formula, it doesn’t matter even if you change the values. #2 Easy to apply. CONs: #1. GCD function only works with integers and if you have decimals in your values, it will not work. 3. SUBSTITUTE and TEXT for Ratio Calculation A combination of two awesome functions. Yes, you can use SUBSTITUTE and TEXT function to calculate the ratio. This method works like a charm just like GCD function. Here we have below values to calculate the ratio. 📷 In a cell, insert the below formula and hit enter. =SUBSTITUTE(TEXT(A2/B2,”#####/#####”),”/”,”:”) 📷 here's how this formula works... This formula also works in two different work parts with TEXT and SUBSTITUTE. 📷 First, you have to use text function to divide both of the numbers, and after that format the returning value in a fractional format. Second, by dividing both of the values you have got 10 and when you convert this value into a fractional part you will get 10/1. Third, replace forward slash with a colon. And, we have substitute function for that. PROs: #1. A dynamic formula, it doesn’t matter even if you change the values. #2. Simple to use and understand. CONs: #1. If you have simple values to calculate a ratio, it’s not good to use this method. 4. Calculate Ratio with Round Function Using round function to calculate a ratio is also a useful method. This is especially useful when you want to calculate ratio with decimals for accurate comparison. Here we have values in which higher value is not divisible with the smaller value. So, in this situation instead of using them as they are you can divide them and show the final ratio with decimals. 📷 Just insert the below formula into the cell and hit enter. =ROUND(A2/B2,1)&”:”&1 📷 And, here you have a ratio with decimals. here's how this formula works... You can split this formula into two different parts to understand it. First of all, you have to use round function to divide larger value with the small value and get the result with one decimal. Second, you have to use a colon and “1” in the end. PROs: #1. Useful when you want to get results with decimals. #2. Final value will more accurate. CONs: #1. Not applicable in all the situations From <https://excelchamps.com/blog/ratio/>

0

0

5

Chris Gage

Nov 05, 2020

In General Discussions

A ACD – Automatic Call Distributor ACW – After Call Work AHT – Average Handle Time ANI – Automatic Number Identification API – Application Program Interface ASA – Average Speed of Answer ASP – Application Service Provider ASR – Automatic Speech Recognition ATA – Average Time of Abandonment B BPO – Business Process Outsourcing BC/DR – Business Continuity/Disaster Recovery BCP – Business Continuity Planning BI – Business Intelligence BIC – Best-in-Class C CC – Call Centre CCR – Customer Controlled Routing CDR – Call Detail Recording CED – Caller Entered Digits CEM – Customer Experience Management CIS – Customer Information System CLI – Calling Line Identity CMS – Call Management System CRM – Customer Relationship Management CSR – Customer Service Representative CTI – Computer Telephony Integration D DID – Direct Inward Dialling DNIS – Dialled Number Identification Service DSL – Digital Subscriber Line E EAI – Enterprise Application Integration ERMS – Email Response Management System ERP – Enterprise Resource Planning EWT – Expected Wait Time F FCR – First Call Resolution FCRR – First Call Resolution Rate FTE – Full Time Equivalent G GOS – Grade of Service I INS – Intelligent Network Service ISDN – Integrated Services Digital Network ISP – Internet Service Provider IVR – Interactive Voice Response IXC – Interexchange Carrier K KB – Knowledge Base KM – Knowledge Management KPI – Key Performance Indicator L LOA – Least Occupied Agent LAN – Local Area Network M MIA – Most Idle Agent MMR – Multi Media Routing N NSP – Network Service Provider O OAM – Operations, Administration and Maintenance OJT – On the Job Training OPA – Off Phone Activity OSI – Open System Interconnector P PABX – Private Automatic Branch Exchange PBX – Private Branch Exchange PCP – Post Call Processing PDA – Personal Digital Assistant PQQ - Pre-Qualification Questionnaire PSTN – Public Switched Telephone Network Q QA – Quality Assurance QM – Quality Monitoring QoS – Quality of Service R RFI – Request for Information RFP – Request for Proposal RNA – Ring No Answer ROI – Return on Investment S SaaS – Software as a Service SA – Speech Analytics SBR – Skill-Based Routing SDP - Skills Development Programme SFA – Salesforce Automation SIP – Session Initiation Protocol SL – Service Level SLA – Service Level Agreement T TAPI – Telephony Applications Programming Interface TCO – Total Cost of Ownership TTS – Text to Speech U UC – Unified Communications UCD – Uniform Call Distributor V VoIP – Voice Over Internet Protocol VPN – Virtual Private Network VRU – Voice Response Unit W WAN – Wide Area Network WFM – Workforce Management WFO – Workforce Optimization From <https://www.talkdesk.com/blog/call-center-acronyms/>

1

0

7

Chris Gage

Nov 05, 2020

In Capacity and Resource

Planning for capacity breaks down into three simple elements: determining your targets (KPI/Service Levels), understanding current capacity, and of course planning ahead. Targets In this step, your business should break down work into categories with a full list of internal targets and hopefully produce documentation quantifying users expectations of how work gets completed. (if this is not done in your organisation far more preparatory work will be required) Businesses usually choose to organise workloads by either who is doing the work, the type of work performed, or the work process. A "service level agreement" lays out the acceptable parameters between the provider and the consumer and is often the go-to for 'targets'. Understanding Current Capacity Organisations should always know the amount of staff they have employed. However, this information is often kept within HR or Finance and often their particulars of measurements do not match the need for operational capacity clarity. Always ensure the amount of time each person spends somewhere/doing is well understood by all involved in capacity planning. Planning Ahead This is the easy one! Once you have put together your capacity plan from a) understanding your targets and b) understanding current capacity you will have created an output usually in the form of FTE and financials. However, it is exceedingly important that you don't just leave it there. It is very important to organise Capacity Plan reviews, have an ongoing strategy to review 'current capacity methodology and ensure you are aligned with the strategic corporate objectives for your targets/KPI. If this is met in the most appropriate method for your organisation you will

1

0

3

Chris Gage

Nov 05, 2020

In General Discussions

Looking for some help on an issue related to Planning, Insight or Contact Centres please comment here!

1

0

2

Chris Gage

Nov 05, 2020

In Concepts

I have really broken down the wiki into fairly rudimentary categories, with Concepts being overall principles or guidance. e.g. the foundations of forecasting or basic scheduling rules

0

0

1

Chris Gage

Nov 05, 2020

In Forecast

Basic principles to remember when thinking planning and forecasting. If these are not all met its likely your forecast will not be fully accurate and indeed its difficult for accountability as its unrealistic to assume accuracy when you do not have access to all of these. 📷 Historic The easy one, make sure you have all the appropriate historic information. Always ensure you understand not only the volumetrics but also the equivalent FTE output for each period as its never apples to apples. Operational Accountability This is really important and it can be a massive issue in some centres though equally it may not be necessary to take into account. Really this is about ensuring targets are being met by Operations, e.g. Occupancy, Wrap, Conformance etc. If they are not, any root cause analysis is likely to show them as the main cause for Forecast inaccuracy. Business Insight Very often lacking, knowing in advance where appropriate business changes that may impact you, could be a CEO talking about a particular service, a company update etc. These are usually quite hard to define and are easiest to obtain from the C-Suite or Senior Directors. Marketing Insight An obvious one, make sure you are working in collaboration with Marketing (or equivalent if Public Sector) to understand changes, upcoming promotions etc.

0

0

7

Chris Gage

Nov 05, 2020

In Macros

Highlight Top 10 Values Just select a range and run this macro and it will highlight top 10 values with the green colour. Sub TopTen() Selection.FormatConditions.AddTop10 Selection.FormatConditions(Selection.FormatConditions.Count).S tFirstPriority With Selection.FormatConditions(1) .TopBottom = xlTop10Top .Rank = 10 .Percent = False End With With Selection.FormatConditions(1).Font .Color = -16752384 .TintAndShade = 0 End With With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13561798 .TintAndShade = 0 End With Selection.FormatConditions(1).StopIfTrue = False End Sub From <https://excelchamps.com/blog/useful-macro-codes-for-vba-newcomers/>

0

0

11

Chris Gage

Nov 05, 2020

In Macros

Highlight the Active Row and Column Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim strRange As String strRange = Target.Cells.Address & "," Target.Cells.EntireColumn.Address & "," & _ Target.Cells.EntireRow.Address Range(strRange).Select End Sub Here are the quick steps to apply this code. Open VBE (ALT + F11). Go to Project Explorer (Ctrl + R, If hidden). Select your workbook & double click on the name of a particular worksheet in which you want to activate the macro. Paste the code into it and select the “BeforeDoubleClick” from event drop down menu. Close VBE and you are done. Remember that, by applying this macro you will not able to edit the cell by double click. https://excelchamps.com/blog/useful-macro-codes-for-vba-newcomers/

0

0

2

Chris Gage

Nov 05, 2020

In Macros

Highlight Duplicates from Selection This macro will check each cell of your selection and highlight the duplicate values. You can also change the colour from the code. Sub HighlightDuplicateValues() Dim myRange As Range Dim myCell As Range Set myRange = Selection For Each myCell In myRange If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then myCell.Interior.ColorIndex = 36 End If Next myCell End Sub From <https://excelchamps.com/blog/useful-macro-codes-for-vba-newcomers/>

0

0

2

Chris Gage

Nov 05, 2020

In Macros

You can use this code to auto-fit all the rows in a worksheet. When you run this code it will select all the cells in your worksheet and instantly auto-fit all the row. Sub AutoFitRows() Cells.Select Cells.EntireRow.AutoFit End Sub

0

0

2

Chris Gage

Nov 05, 2020

In Macros

Quickly auto fit all the columns in your worksheet. This macro code will select all the cells in your worksheet and instantly auto-fit all the columns. Sub AutoFitColumns() Cells.Select Cells.EntireColumn.AutoFit End Sub

0

0

4

Chris Gage

Nov 05, 2020

In Macros

Send Email and/or/extra workbook Example The following subroutine sends the last saved version of the active workbook in an e-mail message. Change the mail address and subject in the macro before you run it. Sub Mail_workbook_Outlook_1() 'Working in Excel 2000-2016 'This example send the last saved version of the Activeworkbook 'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm Dim OutApp As Object Dim OutMail As Object Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .to = "ron@debruin.nl" .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = "Hi there" .Attachments.Add ActiveWorkbook.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Send 'or use .Display End With On Error GoTo 0 Set OutMail = Nothing Set OutApp = Nothing End Sub

0

0

5

Chris Gage

Nov 05, 2020

In Excel Formulas

Use & numbers and texts formulas. ="The average time taken on "&B13&" is "&TEXT(L18,"0.0")&" hours, with a cost of "&TEXT(L20,"£0,000") & ". With the longest session taking, " &TEXT(H13,"0.0") & " hours." The average time taken on Model Simulations is 119.1 hours, with a cost of £9,999. With the longest session taking, 400.3 hours. B13 = Model Simulations L20 = £9,999 H13 = 400.3

0

0

1

Chris Gage

Nov 05, 2020

In Excel Formulas

INDEX MATCH Formula: =INDEX(C3:E9,MATCH(B13,C3:C9,0),MATCH(B14,C3:E3,0)) This is an advanced alternative to the VLOOKUP or HLOOKUP formulas (which have several drawbacks and limitations). INDEX MATCH is a powerful combination of Excel formulas that will take your financial analysis and financial modelling to the next level. INDEX returns the value of a cell in a table based on the column and row number. MATCH returns the position of a cell in a row or column. Here is an example of the INDEX and MATCH formulas combined together. In this example, we look up and return a person’s height based on their name. Since name and height are both variables in the formula, we can change both of them! 📷 From <https://corporatefinanceinstitute.com/resources/excel/study/advanced-excel-formulas-must-know/>

0

0

3

Chris Gage

Nov 05, 2020

In Excel Formulas

Using Excel COUNTIF function with dates If you want to count cells with dates that are greater than, less than or equal to the date you specify or date in another cell, you proceed in the already familiar way using formulas similar to the ones we discussed a moment ago. All of the above formulas work for dates as well as for numbers. Let me give you just a few examples: Criteria Formula Example Description Count dates equal to the specified date. =COUNTIF(B2:B10,"6/1/2014") Counts the number of cells in the range B2:B10 with the date 1-Jun-2014. Count dates greater than or equal to another date. =COUNTIF(B2:B10,">=6/1/2014") Count the number of cells in the range B2:B10 with a date greater than or equal to 6/1/2014. Count dates greater than or equal to a date in another cell, minus x days. =COUNTIF(B2:B10,">="&B2-"7") Count the number of cells in the range B2:B10 with a date greater than or equal to the date in B2 minus 7 days. Apart from these common usages, you can utilize the COUNTIF function in conjunction with specific Excel Date and Time functions such as TODAY() to count cells based on the current date. Criteria Formula Example Count dates equal to the current date. =COUNTIF(A2:A10,TODAY()) Count dates prior to the current date, i.e. less than today. =COUNTIF(A2:A10,"<"&TODAY()) Count dates after the current date, i.e. greater than today. =COUNTIF(A2:A10,">"&TODAY()) Count dates that are due in a week. =COUNTIF(A2:A10,"="&TODAY()+7) Count dates in a specific date range. =COUNTIF(B2:B10, ">=6/7/2014")-COUNTIF(B2:B10, ">6/1/2014")

0

0

4

Chris Gage

Nov 05, 2020

In Excel Formulas

IF combined with AND / OR Formula: =IF(AND(C2>=C4,C2<=C5),C6,C7) Anyone who’s spent a great deal of time doing various types of financial models knows that nested IF formulas can be a nightmare. Combining IF with the AND or the OR function can be a great way to keep formulas easier to audit and easier for other users to understand. In the example below, you will see how we used the individual functions in combination to create a more advanced formula. For a detailed breakdown of how to perform this function in Excel please see this free guide on how to use IF with AND / OR. 📷 From <https://corporatefinanceinstitute.com/resources/excel/study/advanced-excel-formulas-must-know/>

0

0

4

Chris Gage

Nov 05, 2020

In Excel Formulas

Sums for days of the week =SUM(IF(WEEKDAY(C1:C14)=2,D1:D14))
After typing this, press CTRL+SHIFT+ENTER. Change the red 2 above to any number from 1-7 for each day of the week (1 = Sunday, 7 = Saturday)
You can also use a non-array formula, like:
=SUMPRODUCT((WEEKDAY(C1:C14)=2)*(D1:D14))
You don't need to press CTRL+SHIFT+ENTER after that formula, just ENTER.

0

0

3

C

# Chris Gage

Admin

More actions