Author: webadmin

We are recruiting for a virtual customer service assistant

The Excel Experts

Thank you very much for showing interest in this post. This current vacancy has now been filled…

As a growing business, we are looking for a freelance customer service assistant to help us answer our calls from prospective clients. 

We are a dynamic (virtual) team of spreadsheet experts providing business-to-business IT solutions and we need somebody to answer the phone for our 0800 number. You can see our website here www.theexcelexperts.com

We make software solutions for our customers, tools that will make their lives easier. That’s why they will be calling you. Your first responsibility will be to make them feel listened to. You will then report back to us, so we can find the best expert to fulfill their needs.

We need someone friendly but serious, and a good listener. You will take caller details and record them in our online database. The database will then be distributed to our team of experts, to find the best match for that client.

When people call they generally have a problem, and they’re looking for someone who can listen and understand their problem. You don’t have to be an Excel expert, but any experience in these areas will be beneficial. You do need to have some experience with how offices work (especially ‘work from home’ offices), and how individuals are working and feeling in these difficult times.

To this end, one of the essential responsibilities will be to write a short brief on what the visitor is actually looking for, and a few details about their project. This will give our consultants something to start with, and the caller will almost certainly have told you something about it by the time they hang up!

We are looking for someone with excellent customer service, to work on a self-employed, freelance basis. You will need to have a computer and an internet connection, we will provide a mobile phone. You will need to be available to answer calls during office hours, with flexibility, if needed, for things like school runs, as being family-friendly is very important for us.

We do not know at present how many phone calls we will be receiving but on past (pre-Covid) experience it will likely be 3-4 a day. As this number increases there will be opportunities to re-negotiate rates and hours.

This is a great opportunity to play a vital role in an innovative, family-friendly workplace with future growth potential. This is an interesting position for someone with interests in technology, especially Microsoft technology, and for someone who enjoys talking a bit ‘technical’ on the phone.

Please apply by emailing your CV and a covering letter. In your letter, you should outline why you would like to join us in this role. Please also add the rate you would happily charge per call to fulfill this task for us. We want to pay a fair and competitive price for the right person.

Please get in touch if you have any questions.

Email: recruitment@theexcelexperts.com

Getting to grips with the new LAMBDA function in Excel

LAMBDA formula to create user defined functions in excel

Microsoft have recently introduced a very exciting new function for Excel users.  This function enables Excel users to define their own re-usable functions without resorting to using the macro programming language (VBA).

This opens up enormous possibilities for Excel users who are non programmers.  When Excel functions are used within a worksheet, they can often get hugely complicated especially if they use nested functions.  If the formula has to be used several times within the workbook, it may become difficult to replicate.  You could use copy and paste, but this methodology may cause unexpected errors within the workbook.

LAMBDA provides a solution to this. Once the user has perfected the formula, it can be converted into a custom re-usable function that can be used exactly like the native Excel functions.  It can accept parameters as defined by the LAMBDA function, and the user does not need to worry about whether they used the correct formula, as this becomes a tried and tested function that can be used over and over again within the workbook.

 

Requirements

To use the LAMBDA function in Excel you need to have a Microsoft Office 365 subscription and the latest version of Microsoft Office installed. You also need the latest Microsoft updates installed (including Beta updates). Currently, you also need to join Microsoft Office Insiders in order to receive Beta updates.

 

Syntax

=LAMBDA([parameter1, parameter2, …,] calculation)

The parameter argument defines the values that you want to pass to the calculation.  This could be a cell reference, a text string, or a numeric value.

You can define up to 253 parameters, but this limit is unlikely to be reached!

The parameter argument is optional. Parameter names are defined as a text string e.g. x, number, myPar, etc.

The calculation argument defines the calculation that will be performed using the defined parameters. It must return a result, and it is not optional.

 

Example

=LAMBDA(x,x*10)(1)

This will return the result of 10 in the cell. A single parameter (x) is defined and the calculation argument multiplies that parameter by 10. The value of (1) after the first part of the function defines the value for the x parameter. This is only required if you are using the LAMBDA function directly into a cell instead of using it to create a re-usable function.

You can use standard Excel functions within the calculation argument.

=LAMBDA(x, y, SQRT((x^2+y^2)))

This LAMBDA function will use Pythagoras to calculate the hypotenuse of a right-angled triangle.  The parameters x and y represent the horizontal and vertical sides of the triangle. The calculation argument squares both the parameters and adds them together and then uses the Excel SQRT function to get the value of the hypotenuse.

 

Creating a re-usable function with LAMBDA

You can easily create your own custom function which can be used exactly like the native Excel functions within your workbook.

To do this, you need to define a new name. Click on Formulas in the Excel ribbon, and then click on Define Name in the Defined Names group of the ribbon.

Enter a new name into the Name field of the form e.g. MyNewFunction.

Enter your LAMBDA formula in the Refers To field.

In this example, the LAMBDA Pythagoras example is used as the formula in the Refers To field.

Click OK, and your new function is now defined and can be used as a normal Excel function.

To call the function from anywhere within the workbook, you use the following syntax:

=MyNewFunction(3,4)

This passes the x (3) and y (4) parameters to your function and produces the result of 5 (classic 3,4,5 triangle).

You can use your new function from anywhere in the workbook in exactly the same way as if was a native Excel function.

 

Points to watch

More than 253 parameters give a #VALUE error.

Passing the incorrect number of arguments will give a #VALUE error.

If your LAMBDA function creates a circular formula, the this will give a #NUM error.

Lambda names and parameters follow the Excel syntax rules. Also, you must not use a full stop (.) within a parameter name.

 

Recursion

Using LAMBDA, you have the ability to make a function work recursively until a specific condition is met.  Previously, it was only possible to do this using a custom function defined as a macro in VBA.

For example, you could have a text string in a cell where you want to remove certain illegal characters from it.  Previously, you would have to use a function to remove the illegal characters one at a time.  This could get very complicated because you would not always have the same number of illegal characters to be removed in each string, and it would need careful use of functions to prevent errors occurring.

Microsoft has provided this sample LAMBDA function to show how this can now be done:

=LAMBDA(textString, illegalChars,

IF(illegalChars=””, textstring,

REPLACECHARS(

SUBSTITUTE(textString, LEFT(illegalChars, 1), “”),

RIGHT(illegalChars, LEN(illegalChars)-1)

)))

 

The parameter textString is the original string of characters which contains some illegal characters, and the parameter illegalChars contains a string of the characters to be removed from textString.

The function as shown above is turned into a function called REPLACECHARS by using the methodology described above to create a function with a new name.

The function keeps calling itself until the IF statement is true (the illegalChars parameter is empty). It substitutes the left most character in the illegalChars string with nothing, and then uses the RIGHT function to delete the left most character from the illegalChars string.

As the process iterates through the illegalChars string, the illegalChars string gets shorter and shorter until it is empty, at which point the process exits.