[MOS] Microsoft Office Specialist Exam 77-420: Excel 2013 Help!!

**k_mahajan** · 04-27-2015, 12:40 PM

Hi everyone,

Today i gave my MOS Exam 77-420 : Excel 2013. i scored 980/1000. overall the exam was easy. i got 100% in all objectives except 90% in 'Applying Formulas & Functions'. I know where i did wrong. But i still can't figure out how to solve that problem because of which i couldn't score a perfect 1000. I hope if anyone here could help me.

I know i did 2 functions wrong: CONCATENATE & RIGHT. Although it was a scenario based exam but still i remember the 2 questions. Here they are:

Please Login or Register  to view this content.

There were 2 worksheets. I was to use these functions in the second worksheet. The data which were required by the functions were on the first worksheet in a table. The table headers were: Number, Year, City, Name, Country code, Time. The data in the table was sorted in descending order of time.

Here's What I Did: I didn't used any look-up or reference function inside these functions. I directly used the Cell Name (eg. B4) for the reference data in the function. When i matched the answer with the Reference images they provided, it was correct, but i think that's not what they asked for...!!

**Note: Also i forgot to mention one thing. My friend also gave this exam and he also got the same document to create in the exam. He also scored 980/1000. He did the same thing wrong which i did. He also got 90% in 'Applying Formulas & Functions'.
But the thing is he did these 2 questions different from me. By using nested functions. He used LOOK-UP in one question and MATCH & INDEX in the other. His answer also matched the reference images. But still even this was not the correct solution.

I have searched all over and i can't figure out how would this be solved. we both got the same document to create and both got the same score. Both tried different ways to solve these questions. Both got the correct answer as matched with the reference JPEGs. But still both got it Wrong.
Please Help.

PS: On the second worksheet, where i was asked to use these functions. One thing which was available (i don't know whether it was of some use or not), was the time of the fastest and slowest runner which i was asked to find out using max and min function. And for the times of the runner, i got the data from the First Sheet. As i mentioned earlier, every data was available in the table on the first worksheet.

**Bernie Deitrick** · 04-27-2015, 03:12 PM

The fastest runner would have the minimum time, so
=MIN(TimeRange)
would return that. Finding the name of the fastest runner would first require identifying the row, which would require
=MATCH(MIN(TimeRange),TimeRange,False)
Finally, returning the name fo the fastest runner would require
=INDEX(NameRange, MATCH(MIN(TimeRange),TimeRange,False))

Then likewise for the country, and finally to add them together:

=CONCATENATE(INDEX(NameRange, MATCH(MIN(TimeRange),TimeRange,False))," - ",INDEX(CountryRange, MATCH(MIN(TimeRange),TimeRange,False)))

But nobody actually uses CONCATENATE: This is shorter, and requires fewer function calls.

=INDEX(NameRange, MATCH(MIN(TimeRange),TimeRange,False)) & " - " &INDEX(CountryRange, MATCH(MIN(TimeRange),TimeRange,False))

And for the family name of the slowest runner using RIGHT:

=RIGHT(INDEX(NameRange, MATCH(MAX(TimeRange),TimeRange,False)),5)

Though a better solution would find the space between the two names and return any length last name:

=MID(INDEX(NameRange, MATCH(MAX(TimeRange),TimeRange,False)),FIND(" ",INDEX(NameRange, MATCH(MAX(TimeRange),TimeRange,False)))+1,255)

But if they allow three or more names, then it would require a muh more complicated formula and VBA would be better.

**k_mahajan** · 04-30-2015, 10:24 AM

Thnx Bernie for your reply. My friend followed the same way you mentioned above but sadly he also got this question wrong.
Maybe this is also not what they want. What I am thinking is that since this is not an expert level exam , so the answer to this problem might be very simple. Its just that we can't figure it out.