Excel Assessment – Businesspersons’ Benchmark

The first part of this formula is the Lookup value, which was already filled in – A1. This cell contains the Customers Name.

The second part is the Table_array, which in this case refers to the list of information in the image. This is the range address for the list that contains the information you want to look up. To allow for additions to this table, the best options here is to refer to the entire columns – so the formula would like this so far:

=VLOOKUP(A1,Data!A:D,_,_) – by the way – if you entered $A:$D, A1:D9 or $A$1:$D$9 as the answer, this question would still be marked as correct – these are all valid options for this question.

The third setting is the Col_index_num. The VLOOKUP formula looks for the Lookup Value in the first column of the table array. Using our example, the customer name is in cell A1, so it will look for this name in column A, which is the first column of the table array. Once it finds the value it looks for, then uses the Col index number to know what value you want the formula to display. In our example, we wanted to show the customers Payment Terms, which is in column C, which is the 4th column in the table array, so the Col_index_num setting is 4. The formula now looks like this:

=VLOOKUP(A1,Data!A:D,4,_)

The last part of the VLOOKUP formula is just as important as the rest – it’s the Range_Lookup.  This needs to be set to 0 or FALSE.  It’s important because if it’s left blank, the VLOOKUP will look for the closest match to the value you are looking up, rather than the exact match.  In some cases, the closest match is the desired result, but in this case, we want an exact match, so 0 or FALSE is the correct setting here.

The final formula should be: =VLOOKUP(A1,Data!A:D,4,0)  or =VLOOKUP(A1,Data!A:D,4,FALSE)

Password protect the sheet: This is done on the Review Tab

Set up the sheet so that when she scrolls the sheet towards to the Right, column A remains visible: This is the Freeze Panes function, on the View Tab

Add a drop down list so users can select payment terms from a list: This is done using Data Validation, on the Data Tab

Add a special format so that if two or more identical entries are found in the same column, it turns the fill of those cells to red: This is done using Conditional Formatting, on the Home Tab

This is a formatting problem – the cell needs to be formatted as a Date.

Excel stores dates as a number – the number represents the number of days since 1900-Jan-0 – so, the figure that shows up in Caroline’s sheet is a date – 41640 days from 1900-Jan-0 is the 1st of Jan 2014, so all she needs to do is tell Excel to display it as a date as we know them – this is done by formatting the cell as a date.

The solution to this problem is to format the cell using the [h]:mm:ss format.  Excel usually displays time like a 24 hour clock – once it gets to 24 hours, it starts counting again.  By formatting it this way, it tells Excel to keep counting on past 24hs.

This is found in the custom list of formats:

In this case a simple SUMIF formula will not work because there is more than one criteria to be applied – we need to sum only sales where Caroline is the salesperson, for the year 2014. In this case you can use a SUMIFS formula. The formula for this example would be: =SUMIFS(H:H,I:I,2014,D:D,”Caroline Wells”). Of course you could type Caroline’s name and the year into a cell each, and then refer to the cell addresses rather than the actual values – like this: =SUMIFS(H:H,I:I,A1,D:D,A2) – that would make it easier to change the formulas to look up another persons sales.

The answer to this on is she needs to set the gap width for the Data Series to 5%.

You do this by right clicking on one of the bars and selecting Format Data Series.  That then gives you the option to set the gap width as a percentage, in this case she used 5%.

This is a Sparkline chart and they are small charts that fit inside a cell. This particular one is a line chart, you can also insert a bar chart or what Excel calls a Win/Loss chart.

To use one, you need to click on the cell where you want it to appear, then go to the Insert Menu and locate the Sparkline charts. Click on the one you want to use, and it will ask you to specify the range of data you want to use. Simply select the data you want to display and then click OK.

This pie graph is not a true representation of the data because the data includes a negative number which a Pie graph cannot display – in fact it displays it as a positive number.

A better chart to use in this case would be a bar or line graph:

The quickest way to do this is to simply click on the chart, and then drag the selection handles on the data range across to include the extra data.

To replace formulas with their values, you need to copy the cells that contain the formulas, and then paste over them using Paste Special > Values.

This information is sorted by Salesperson and then by Customer Name.  The correct setting for this is:

She needs to use the Text to Columns tool.

The use of parenthesis tells Excel in what order the different calculations need to be calculated. This happens in maths as well.

Excel calculates the multiplication and division parts of a formula first, and then the addition and subtraction parts.

The result of the formula above is 1.33 when used this way, and 33.33 when used without parenthesis.  And here’s why:

With the parenthesis, the formula first calculates the amounts in parenthesis, so the calculation Excel does is actually =100/75, which is 1.33

Without the parenthesis, the formula Excel multiplies 15 by 5, which is 75, and then divides 5 by 75 – which gives us 1.67.  Lastly it multiplies this amount by 20 – giving us 33.33

The result of this formula depends on the value in cell A1.  If the value in cell A1 is less than 10, then the result of the formula will be “Below Target”, otherwise, the result will be 50.

The IF is a conditional formula, which means it has three parts to it – a condition which will either be TRUE or FALSE – in this example, the condition is “A1<10” – which means in words means “Is the value in cell A1 less than 10” – this will either be True or False.

The next part of the formula is what the formula should show is the condition is True – in this example, it will show “Belo Target.  The last part is what to show if the condition is False – and in the example we used a simple formula – 10*5, so the result of the formula will be 50 if the value of A1 is over 10.

The correct settings for this formula are: =MID(A2,8,10).

The MID formula allows you to extract a portion of text out of a larger text string.

To explain the formula in this example, A2 is the cell which contains the text.  8 is the starting point of the portion of text you want to extract, and 10 is how many characters you want to extract.

If you refer back to the image above, the first part of the date is the 8th character in the text, and the date has 10 characters, so the formula is =MID(A2,8,10)