Home

#ExcelChampINTL

Highlighted

Re: #ExcelChampINTL

This competition has forced me to really address my chart presentation and up my game.

 

Thank you Microsoft for running it, as well as all the competitors who have raised the bar.

 

 

 Round3 Submission.PNG

Re: #ExcelChampINTL

Could I throw in another question? Can we submit multiple versions of the formula wrestling piece?

I ask because I have a few quite different ways to do it, and I'm not sure how each would be rated for the subjective evaluation. For example, the one that I think is the best (because it can easily generalize for, e.g., a change in the starting or ending point, or a change in the grid layout) also uses the biggest number of different functions, while another one that is much less flexible only uses a few. So if 'fewer functions' were the only metric, I would submit the weaker solution, but if there's a more holistic assessment I'd submit the one I think is really better.

If we can't submit multiple ones, it would be great to get some guidance on how they will be assessed (beyond whether they give the right answer or not).

Just to be clear, I'm not trying to hedge my bets on the actual answers - I'm sure all the approaches will give correct answers (maybe not exactly the same ones if there are multiple fastest paths), and I'd be happy to be marked wrong on a grid if any of my approaches got it wrong.

Sorry for the slightly pedantic question - just want to make sure I put my best foot forward... and thanks for running this!

Re: #ExcelChampINTL

Hi Diarmuid,

If I understood it correctly, they expect correct answers (even if input changes) and using minimum number of functions and operations. I was in same situations where on one side I had scalable and/or easy to understand solution and on other "dirtier" with much less functions/operations.
I submitted "dirtier" one because it's closer to task's instructions and because that approach is easily to evaluate (I guess they're using automated evaluation).

Maybe I'm wrong. In some cases instructions are confusing and I wasn't sure what they exactly want. Nobody answered my earlier questions and I was on my own. :)

Good luck.

Re: #ExcelChampINTL

According to the question, the starting/end point wont ever change, nor will the grid layout (I had to ask on the last page as I didn't read it properly!)

As for scoring I agree with Ivan, I'm sticking to whats written in the instructions which is least formulas/mathematical operations, which still produces the correct result. But that's my guess only!

Good Luck!

Re: #ExcelChampINTL

Thanks guys. I guess you're probably right, although it seems like a shame if we're not submitting our best work...

Re: #ExcelChampINTL

Dear Microsoft Admin:

 

I'm having a problem creating a Pivot Table in the response section of Data Relays - Part 2.

Excel is only allowing me to insert the Pivot Table on a new sheet.

 

Can I submit my response on a new sheet? Or will that be the wrong response?

 

Thank you and best regards,

Hicham

 

Note: I'm using Excel 2013.

Please see Attached photos for error messages.

Here below is th error message:

 

============================
Error Message:
============================

The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_INVALIDARG))
----------------------------
Exception has been thrown by the target of an invocation.

============================
Call Stack:
============================


----------------------------
at System.RuntimeType.InvokeDispMethod(String name, BindingFlags invokeAttr, Object target, Object[] args, Boolean[] byrefModifiers, Int32 culture, String[] namedParameters)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.Office.PowerPivot.ExcelAddIn.ExcelInterOpUtil.CreatePivotTableAction(PivotCache pivotCache, String pivotLocation, Object pivotVersion)
at Microsoft.Office.PowerPivot.ExcelAddIn.GeminiRibbon.CreatePivotTable(Worksheet workSheet, WorkbookConnection workbookConnection, Object pivotVersion, Range targetLocation)
at Microsoft.Office.PowerPivot.ExcelAddIn.GeminiRibbon.InsertPivot(String sandboxConnectionString, String location, String cubeName, MultiObjectsReportType reportType)
at Microsoft.Office.PowerPivot.ExcelAddIn.GeminiRibbon.InsertPivot(String connectionString, String cubeName, MultiObjectsReportType reportType)

============================

 

Insert Pivot.JPG

Exception.JPG

Re: #ExcelChampINTL

Hi Hicham,

Did you try create PT in new worksheet and then cut/paste it to desired location? It should work, I tested it now on my PC.

Re: #ExcelChampINTL

Also getting this error..

 

I guess as long as we all get the error it's fine!:smileyvery-happy:

 

 

EDIT: Just tried your solution Ivan, this works, thanks!

Re: #ExcelChampINTL

Did you try create from Power Pivot Window? If Yes, try create normal pivot table from Insert and choose Data Model connection, should works i think.

Re: #ExcelChampINTL

I am honored to be in the semi-finals. I have had to really up my game to keep up so I hope meets top 20 standard :P. Unfortunately, I had to zoom out in order to screen capture my submission so it's a little more difficult to read :(

 

Good luck to everyone!

 

Round3_Question2.PNG

Re: #ExcelChampINTL

Hi Ivan:

 

I did Cut and Paste the Pivot table from the new sheet into the response section on sheet 01-02.

However, are you guys having trouble refreshing the PTs?

 

I'm getting an error message when refresfing the PT from the PowerPivot>Tables>Update All

"The command cannot be completed. No tables were detected in the active Excel workbook that are linked to PowerPivot tables."

 

And I'm getting another error message when refreshing the PT from the PowerQuerry Pane

"Sorry, PowerPivot can't open the Data Model because there was a COM exception while opening. You might be opening a workbook on a corrupt installation of Excel. Click Detail for more information."

 

============================
Error Message:
============================

Attempted to read or write protected memory. This is often an indication that other memory is corrupt.

============================
Call Stack:
============================

at Microsoft.Office.Interop.Excel.WorkbookClass.get_Model()
at Microsoft.Office.PowerPivot.ExcelAddIn.InProcServer.LoadSandboxAfterConnection(String errorCache)
at Microsoft.Office.PowerPivot.ExcelAddIn.InProcServer.LoadSafeSandboxAfterConnection(String errorCache)
at Microsoft.Office.PowerPivot.ExcelAddIn.InProcServer.LoadOLEDBConnection(Boolean raiseCompleteEvent, String errorCache)

============================PQ Refresh.JPGRefresh PT.JPG

 

Re: #ExcelChampINTL

Hi Hicham,

I'm using O365 and don't have problem with refresh. Maybe your Office installation is corrupt. Can you try it on other PC? Let's hope it's working on other computers and you can submit it without worry that it won't work. Other way is to do it from scratch on other computer or find some trial version of newer version of Excel. Good luck with that.

Re: #ExcelChampINTL

Hi Hicham,

I had the same problem with creating the pivot table, on two different computers (both running 2013). I also used the workaround Ivan described of creating elsewhere and cut / pasting back to the original sheet, but it doesn't seem to have caused the refresh error you're getting for me.

Good luck getting it sorted out!

Diarmuid

Re: #ExcelChampINTL

Same error encountered.
What I did is to Insert a regular Pivot Table using the connection created...
Hope it works for you.

Re: #ExcelChampINTL

Hi!

Can we leave Athlete Number column in 01-01's solution? There's no strict "No other information should appear in your Pivot Table" instruction, which we have in 01-02.

Earlier, in some other situation, I could sort with some column and after remove but, but here it doesn't work. Here I tried different step reordering and combination, but it didn't help. If I remove it, I always get this error:

Expression.Error: The field 'Athlete Number' of the record wasn't found.
Details:
Race Number=2
Country=JAM
Name=JACKSON Shericka
Result Time (s)=49,83
t0_0=TRUE
t1_0=22,0807666

Thanks.

Re: #ExcelChampINTL

Just facts - Let every viewer tell his or her own storyJust facts - Let every viewer tell his or her own story

 

Unlike round 3, the previous two rounds were good candidates for storytelling.

 

For example, in round 1 the message was that The Olympics has gone through major changes since the first modern games debuted in Athens in 1896. But still more is needed to be done to reach gender equality in participation.

 

In round 2 the message was that the IOC managed to consistently increase revenue over time by reaching wider audiences. But ticket sales revenue remains IOC's main challenge.

 

Round 3, however, has so many stories to tell. Should we talk about the victory of Japan? Or should we analyze why China came 3rd? Or maybe we should focus on why Ukraine lagged behind?

 

That pushed me to change strategy and just state the facts as they are and leave it to the reader to create his or her own story. Let the Japanese celebrate their own victory, or the British celebrate the achievement of their compatriot in the Pommel Horse.

 

The graph reads from left to right starting with the round score and the medalists. Since the scores are very close to each other, I opted for the dot plot to avoid zero-based scales--a must for bar charts.

 

The heat map shows the event scores by country where I also added another dimension at the bottom indicating the maximum athlete score achieved by event.

 

The third column shows the wins by country followed by the rank of each country by event--with emphasis on the gold, silver and bronze medalists.

 

The last graph shows the distribution of all athlete scores by country. We can visually see the maximum score, minimum score and the range. The color concentration--overlapping dots--gives an idea about how close the athletes were to each other.

 

Finally, to honor the athletes, I've listed the names of the best performers who scored highest by event.

 

Throughout the design, I followed few Edward Tufte's concepts such as maximizing the data-ink ratio, increasing dimensionality--by showing 3 variables or more, showing comparisons and increasing data density. As ET says "Clutter is a failure of design, not an attribute of information."

 

Hope you'll like it and goodluck to all.

 

Regards,

Re: #ExcelChampINTL

Your chart is AWESOME! It feels like I'm reading an infographic released and published by the marketing staff of the event. 

Re: #ExcelChampINTL

@Microsoft Excel, I have 2 doubts about Formula Wrestling section:

1) The "clear" sections of roads have time until 10 minutes. We must disregard in formula or apply data validation if a user put a negative time? If yes, 0 (zero) is allowed?

2) About 3nd question, as the answers are in minutes, we must round the results? If yes, what's the criteria? round to down or normal round (eg: 5,7  = 5 or 6)?

 

Wait for your answer.

 

Thanks.

Re: #ExcelChampINTL

Hi Marcio,

1) I think that data validation is overkill and that they don't expect from us to do it. They'll probably test using regular values.
2) Minutes can have decimal part. If they wanted rounding, they'll probably instruct us to do it.

That's my opinion and what I did. :)

Re: #ExcelChampINTL

I'm inclined to agree with Ivan. Probably reasonable to allow for zeros, since passing through the venues has zero travel time, but I imagine whatever you do for positives would work for zeros without adjustment...

Re: #ExcelChampINTL

Hi everybody, hope you'll like my chart :smileyhappy:

Good luck to everyone!

 

RND3Chart2.JPG

Re: #ExcelChampINTL

I love it! Really interesting display of info.

Re: #ExcelChampINTL

Thanks @Efren Peria! I was aiming for the infographic look :P so your comment made the effort worth it :D

Re: #ExcelChampINTL

My analysis of the Men's Gymnastics Team Final in Rio 2016.

161031_Charts_Round3_HES.PNG

 

 

Re: #ExcelChampINTL

Part of me hates to say it, since I'm a competitor, but I have to agree - I think this one is the stand-out entry of this round to me.

 

Although I still encourage people to go back and look at the submissions on page 11! : )

<<<<<

Re: #ExcelChampINTL

This is Excel World Champ, maybe someone knows answer to this VBA question:
https://techcommunity.microsoft.com/t5/Excel/Can-we-somehow-use-this-unlisted-selection-change-event...

Thanks. :)

Re: #ExcelChampINTL

Thank you Tiara :smileyhappy:

Re: #ExcelChampINTL

Hi,

 

First of all, I would like to mention that I'm very proud to be in the semifinals, it really wasn't expected. I had never used power query before so this competition has made me investigate a lot about it. In question 1, I find it very easy to get the results through excel formulas, but very difficult, at least for me, to get it done through power query editor. Does anyone have this problem too?

 

Well, as far as I understand, part of the idea of going to seattle is to give some feedback about Excel. I don't really think I will get there, so I will leave some feedback here: I think it would be very useful if it was the software the one that transformed an excel formula to M code. It probably may sound not logical at all and almost impossible (almost, nothing is impossible) to accomplish, but this would save a lot of time for users and would make it more "Excel user friendly". 

 

I have learned a lot in this competition, thank you very much for organizing it and I'm looking forward to compete again in the future. 

 

Kind regards,

Cristián Juárez

 

 

Re: #ExcelChampINTL

Stay with what you think is the "minimal" response.  We'll open up the floor to some discussion on more robust solutions following the competition.

Re: #ExcelChampINTL

Hi Ivan,

 

It's ok to leave the additional field in - you will not be penalized on this question for leaving it in.

Re: #ExcelChampINTL

Hi Hicham,

 

Did Ivan's suggested follow up work for you?  

Someone will follow up with you separately to investigate the error more deeply, but the workaround suffices for a contest submission.

 

Thank you

 

Re: #ExcelChampINTL

Hi Marcio

1) The "clear" sections of roads have time until 10 minutes. We must disregard in formula or apply data validation if a user put a negative time? If yes, 0 (zero) is allowed?

>>> Assume only positive times are possible

 

2) About 3nd question, as the answers are in minutes, we must round the results? If yes, what's the criteria? round to down or normal round (eg: 5,7  = 5 or 6)?

>>> Do not worry about rounding. Use exact amounts

Re: #ExcelChampINTL

Hi,

More a question. Can i use a available formula in Office 365 (2016 installed version) that's not available in Office Professional Plus 2016? Or I must do a matricial formula to ensure compatibility?

Regards.

Re: #ExcelChampINTL

@Microsoft Excel Can you please also confirm if for the formula wrestling there will always be at least one "open path" ?

E.g. should we account for the possibility if all roads are closed?

Re: #ExcelChampINTL

Your exam will be evaluated using the most updated version of Office 2016 (including O365 monthly updates), so new formulas are allowed

Re: #ExcelChampINTL

Yes there will always be an open path

Re: #ExcelChampINTL

Sorry, one more question from me too (last one, I promise!). I'm a little torn on how to interpret 'fewer functions'. Do you mean this in the sense of COUNT (i.e. how many times do you call any function) or on the sense of COUNTDISTINCT (i.e. how many different functions do you call in total)?

Sorry if that seems pedantic - I built my solution thinking it meant countdistinct, but just realized I can do much better if it's interpreted as a straight count...

Thank you!

Re: #ExcelChampINTL

Straight Count....

=SUM(SUM(1,2),3) would count as 2 functions

 

Re: #ExcelChampINTL

Phew - glad I asked... (I actually found it a much more interesting challenge with the right interpretation.)

 

Thanks for the quick response!

Re: #ExcelChampINTL

Gymnastics Team Final.png

Re: #ExcelChampINTL

Boo, I could have saved myself a mathematical operation if I'd waited for this response!

 

Are re-submissions allowed? :smileytongue:

Re: #ExcelChampINTL

Hi Excel,

Thanks for help earlier. I have last question. During this round I send four mails to submission e-mail address. First two were related to problem that I couldn't reply to this topic first couple days of this round. In third one I mistakenly sent earlier draft of solution and in last one solution I intended to submit in the first place. Can you take fourth mail as my submission? I know it's resubmission and maybe you don't accept them, but you owe me one favour because I had problem with replying to this topic and I couldn't post my chart earlier. :)

Thanks.

Re: #ExcelChampINTL

Dear Microsoft Admin,
I am very confused about the question 01-02 because there is many possibilities to interprete the question in my opinion.
1) Built a relationship between the 2 table: OK, No doubt.
2) Create a measure that calculates Average Result Time: Here it's possible create average "by athlete across all rounds" or average "by countries in all rounds", also it's possible to create the "average of average of the countries in all rounds"... so many possibilities...

Following is required to create a pivot table with all countries and the average result time across all rounds for runners from that country: Here depending of the structure of the query there is many possibilities...

Can you clarify more about this question? What you desire as answer?

eg:

Country with 3 runners (3 in rounds1, 2 in round 2, 1 in round 3 = 6 results), what you want as result:

1) Country Average: (runner1 results + runner2 results + runner3 results) / 6 ?

or

2) Country Average: (Average of all runners in round 1 + average of all runners in round2 + runner result in round 3) / 3 ?

or

3) Country Average: Average of average for each runner in all rounds ?

Here's a image of hypothetical example to illustrate my doubt.

My first language is not english, so excuse me if I am not clean in my explanation.

Question 01-02.jpg

 

Sincerely,

 

Rafael Issamu

Re: #ExcelChampINTL

"Create a measure that calculates Average Result Time."

 

'The PivotTable should list all countries that participated and the average result time across all rounds for runners from that country."

 

so.....IMO, it's the simplest average of all results - just average, that's what i did ;)

Re: #ExcelChampINTL

Hi Rafael,

 

For what it's worth, I understood this to mean the average across every individual race time, i.e. the sum of all 6 times in your example divided by 6, i.e. your option 1 (although I'll be frantically trying to resubmit if that's wrong!).

 

Good luck!

Re: #ExcelChampINTL

 

Guys, where is a fair-play rule ? :)

 

all accounts created only to give 1 like for 1 chart (fake accs)... hope that likes has the least importance, cause it's not the only one example

.

@Microsoft Excel, what's the multiplier of likes ? :)

 

LikesScreenshot.PNG

Re: #ExcelChampINTL

Rafael-

  For what it's worth, I also used a straight average of the results as described in your example #1. Hopefully this was correct, and I'm glad to see that @Grzegorz Chojnacki and @Diarmuid Early took the same approach.

 

Good luck!

-Sarah

Re: #ExcelChampINTL

Hi Rafael and others,

I also did straight average. IMHO, it's the most correct approach.

Re: #ExcelChampINTL

Hi Grzegorz,

According to rules of competition, social score is 30% of grade. For me, that's too much. It should be max. 5% and that only competitor likes count. Ideal case is where expert is judging all what we did.

With my 8 likes I long time ago said goodbye to finals. I learned a lot and met some nice persons and that's win for me. :)

Re: #ExcelChampINTL

I wont comment on a specific formula in this forum.  Your pivot should display a straight average of all result times for a given country.

Related Conversations
EXCEL DO NOT SHOW GRAPH MAP CHART
Mark 777 in Excel on
45 Replies
Excel Compare Data
Arnold Lopez in Excel on
12 Replies
Excel filter issues
P DS in Excel on
13 Replies
Missing Excel 2013 worksheet tabs
Mike Hill in Excel on
7 Replies
Setting Print Areas Not Working in Excel 2016
Scott Cox in Excel on
4 Replies
Graphs in excel
Martin Kotuľák in Excel on
5 Replies