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

k_mahajan

New Member
Joined
Apr 27, 2015
Messages
6
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:
[HR][/HR]
Code:
[B]1)[/B] [U][B]INSERT THE NAME AND COUNTRY OF THE RUNNER WITH THE FASTEST TIME[/B][/U]

[I]CELL G3[/I]



[LIST]
[*]USE FUNCTION [B]CONCATENATE[/B] 
[*]TEXT1: name of gold medalist in 2012 London 
[*]TEXT2: " - " 
[*]TEXT3: country code of gold medalist in 2012 London 
[/LIST]


[B]2)[/B] [U][B]INSERT FAMILY NAME OF RUNNER WITH SLOWEST TIME.[/B][/U]

[I]CELL G4[/I]


[LIST]
[*]USE FUNCTION [B]RIGHT[/B] 
[*]TEXT: family name of runner with slowest Olympic time 
[*]NUM_CHARS: 5 
[/LIST]
[HR][/HR]

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. :confused:


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.
 

chicagocomputerclasses

Well-known Member
Joined
Mar 2, 2015
Messages
645
it seems like they are asking to simply use concatenate function to put things together. Hard to say without more context.
it doesn't seem like they want you to lookup the table and find the highest value.

Code:
=CONCATENATE(D2," - ",E2)
 
Last edited:

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
5,890
I would say
G3
=lookup(name of gold medalist in 2012 London)&" - "&lookup(country code of gold medalist in 2012 London)
G4
=min(
INSERT FAMILY NAME OF RUNNER WITH SLOWEST TIME)
 

k_mahajan

New Member
Joined
Apr 27, 2015
Messages
6
it seems like they are asking to simply use concatenate function to put things together. Hard to say without more context.
it doesn't seem like they want you to lookup the table and find the highest value.

Code:
=CONCATENATE(D2," - ",E2)
Thanks for your reply. But i did the same thing. So maybe they want to us to use lookup or we need to set up any criteria somehow.
 

k_mahajan

New Member
Joined
Apr 27, 2015
Messages
6
I would say
G3
=lookup(name of gold medalist in 2012 London)&" - "&lookup(country code of gold medalist in 2012 London)
G4
=min(
INSERT FAMILY NAME OF RUNNER WITH SLOWEST TIME)
They have specifically mentioned there to use Concatenate in G3 and Right in G4.
 

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
5,890
G3 Using & is concatenate
=lookup(name of gold medalist in 2012 London)&" - "&lookup(country code of gold medalist in 2012 London)

G4 without seeing more info, can't say how right would be used
 

chicagocomputerclasses

Well-known Member
Joined
Mar 2, 2015
Messages
645
There are too many possible way to look this up, that's why I doubt it would be that. There must be some way of referencing they want. Maybe they want you to name those cells or something.

For instance you could do something like this
Code:
=CONCATENATE(INDEX(namerange,MATCH(MAX(timerange),timerange,0))," - ",INDEX(countryrange,MATCH(MAX(timerange),timerange,0)))
 

k_mahajan

New Member
Joined
Apr 27, 2015
Messages
6
G3 Using & is concatenate
=lookup(name of gold medalist in 2012 London)&" - "&lookup(country code of gold medalist in 2012 London)

G4 without seeing more info, can't say how right would be used
Thanks for your answer to the first problem. I hope is the correct answer to what they are asking.
And for the second one. As I told earlier in my post, one the first worksheet , in the table under the header 'Names' , the names of the players are given in the format '[first name] [last name]'. And under the header 'country codes', Country codes are given eg 'USA' for america, '***' for japan etc.
 

k_mahajan

New Member
Joined
Apr 27, 2015
Messages
6
There are too many possible way to look this up, that's why I doubt it would be that. There must be some way of referencing they want. Maybe they want you to name those cells or something.

For instance you could do something like this
Code:
=CONCATENATE(INDEX(namerange,MATCH(MAX(timerange),timerange,0))," - ",INDEX(countryrange,MATCH(MAX(timerange),timerange,0)))

Yes your right, there are many ways to do so. And my friend tried the same thing you mentioned above, and he also got it wrong. The same score as mine.
I am also wondering what might be the correct way of referencing.
What I think is that since this is not an expert level exam the answer to this might be something very simple. Its just that we can't figure it out.
 

Forum statistics

Threads
1,086,122
Messages
5,387,973
Members
402,091
Latest member
thomastsiakis

Some videos you may like

This Week's Hot Topics

Top