This is Robert's Typepad Profile.
Join Typepad and start following Robert's activity
Join Now!
Already a member? Sign In
Robert
Greater Munich Metropolitan Area, Germany
I am helping people to turn data into smart decisions
Interests: Data Analysis, Data Visualization, Strategic Analysis, Financial Analysis, Strategic Planning, Financial Planning, Simulation Models, Operations Research, Optimization Algorithms
Recent Activity
Julie, the command button is the grey box in cells C1:E2 of the worksheet [Words]. The label of the command button says "Split text from a text file into words".
1 reply
Kallun, many thanks for your comment. I hear you. I think we are two of a kind here. Have a look at the workbooks at your own speed and let me know, if you have any questions.
1 reply
Image
The workbooks used in my presentation on “Analytical and Interactive Dashboards in Excel” at the London Excel Meetup, September 3, 2020 Continue reading
Posted Sep 3, 2020 at Clearly and Simply
Dove, have a look at this workbook: Tooltips on xy scatter charts only first series.xlsm
1 reply
Dove, please have a look at my reply to Ben on March 23, 2017 regarding a chart with more than one data series. My reply also provides a workbook for download.
1 reply
anilayyar, the blurriness of the images comes from formatting them with the predefined picture style "Reflected Bevel, White". If you want to see clearer images on the carousel, you need to stay with the original linked images without any Picture Style Formatting. That being said, this solution is designed to be an interactive navigator. The user is not supposed to really view the charts on the navigator, but to select one and then easily jump to the worksheet with the selected view. Thus, the resolution of the images on the navigator sheet does not have to be perfect. The navigator only indicates what kind of visualizations are offered and provides an easy option to jump to what the user decides to have a closer look at. By the way: I edited your comment by taking out your email-address from the author's name in order to keep spam away from your inbox. I hope I acted in your best interest.
1 reply
Image
A Carousel of Charts as a visual and interactive Navigation Control for Microsoft Excel Workbooks Continue reading
Posted Aug 23, 2020 at Clearly and Simply
Ron, it was great to see your comment. My apologies for this late reply, I have been pretty busy the last few days. I fully agree with you. Solving a TSP in the Euclidean Space (i.e. as the crow flies), isn't of great practical use. This wasn’t my intention, though. I simply wanted to visualize how a Neural Network is working and the TSP is a perfect example for this. Even if it is not "true-to-life" as you wrote with good reason. Your remarks about your work on traffic congestion and geographic detour ratios sound intriguing. I would love to hear more about that.
1 reply
Image
Artificial Intelligence in Microsoft Excel: watch a Neural Network at work while solving a Travelling Salesman Problem Continue reading
Posted Aug 7, 2020 at Clearly and Simply
Hilman, firstly, please note that this post is more than 6 years old and all formulas were made with Tableau version 6 (if I remember that correctly). With version 9, Tableau introduced the function FINDNTH, which returns the nth occurrence of a substring (or character) within a string. FINDNTH inside a LEFT function should work for you.
1 reply
Sam, many thanks for your comments. I am sorry, but I do not remember your solution over at EHA. I will look for it and check it out later today. Regarding your reply to Andy: you are right regarding the dynamic arrays in Excel 365 when you are using them on cell ranges. However, how would that solve the problem described above with the Named Formula in a Chart? You can't simply set the INDEX parameter tab_data[Image] into curly brackets, can you? I am using Excel 365 and I tried, but this does not work for me.
1 reply
Carlos, many thanks for your comment. Using Excel 365's Dynamic Arrays is an interesting alternative I haven't thought of. However, your approach does not really solve the issue, because you still need the two helper columns on the [Data] worksheet to calculate the Dynamic Arrays. The basic idea was to transfer all calculations to Named Formulas in order to get rid of the calculated helper columns.
1 reply
Hui, many thanks for your comment. Valid points and sage advice, but I do not think this is the issue here. The Named Formulas contain the reserved word "Plot", but "myXPlotSimple" works and "myXPlotFull" doesn't. Also, I am referencing to the full workbook name, since the names are defined on workbook level, so this shouldn't be the problem either.
1 reply
Image
With Named Formulas as the Data Source of a Chart Series, Excel does not always plot the values correctly Continue reading
Posted Aug 2, 2020 at Clearly and Simply
10
Damian, many thanks for your message. Sure, I am very interested. Please post your solution somewhere for download, on Tableau Public or send it to me by email (email-link see the top of the blog). Thanks!
Toggle Commented Jul 22, 2020 on S-Shaped Function in Tableau at Clearly and Simply
1 reply
Michal, I am sorry, but I do not have code available to write data from Outlook back to Project. Also, I have stopped working on this particular field (exchanging data between Project and Outlook) a long time ago and moved on to other topics. I do not understand the code you posted, and except for the definition of a variable mspTask at the beginning, there is no reference to a Project task in the rest of the code. I do not understand what the code is supposed. I would recommend posting your questions on the site you downloaded the code from and ask the author directly. Sorry, I can’t help here.
1 reply
Philip, many thanks for your comment and nice words. I am following The Reds, too, especially since a compatriot of mine is managing them. I love what John Oliver said about Juergen Klopp, "historically, charismatic Germans need to be treated with care, but this one is amazing…": https://www.youtube.com/watch?v=oLdaWGe6OvU at 1:40 Regarding your statement about the missing smooth transition of bars in Excel: this reminds me of a great post Daniel Ferry published 10 years ago on this Excel Hero blog. Daniel is doing the animation by VBA and I suppose, this could improve the look and feel of Bar Chart Races in Excel. If you are interested, here is the link: http://www.excelhero.com/blog/2010/09/animated-business-chart-3.html
1 reply
Sudhansu, I do not have the time to write the code for you, but here are the steps you would have to implement in the code: You would first import all existing appointments in Outlook into a VBA array. Inside the For Each myTask loop of the existing code, you would loop through all elements of this array and check if the appointment name already exists. If this is not the case, you would create a new appointment just like the existing code does for all tasks. If the appointment already exists, you would either update Start and End date and all other properties you are exporting, or you would delete the existing appointment and create a new one with the updated values. I hope this helps a little bit.
1 reply
Sudhansu, many thanks for your comment. This is a very old post (almost 11 years) and I am not doing any updates on this piece of code anymore. I am sorry. I hope for your understanding.
1 reply
AJ, I am glad to see that you apparently like the workbook and visualization. However, I am sorry to tell you that I do not have the time to create 60 to 80 workbooks or 4 workbooks covering 15 to 20 seasons for 4 football leagues. My intention was to demonstrate an alternative to the Bar Chart Race. Football was only publicly available data to demonstrate the technique. Expanding that to the other three big European Leagues for the current season wasn’t much of an effort, so I made this available in this post. That being said, you can do this by yourself. Here is a short tutorial what you have to change: 1. Delete the entries in the data columns (blue column headers) on worksheet [Data] and insert the results of the required season 2. Update the team names in section B26:B45 on worksheet [Calculation] 3. Insert the team names and the logos on worksheet [Logos] to replace the relegated with the new teams 4. Change the markers of the Race Chart on the [Chart] sheet for the teams which were relegated I have not done that, but I would assume, this would take less than an hour, including getting the data and finding the team logos. I hope for your understanding, but I rather invest my free time in posting something else than spending 80 hours to create the same visualization for all leagues for 20 seasons. The templates are available, so feel free to fill them with the seasons/data you want to visualize.
1 reply
Carlos, the parameter "Delay in milliseconds" shall provide the option of defining how slow or fast the animation will be, i.e. the milliseconds the code waits between two matchdays. This applies only to the normal forward and backward buttons, though. If you press fast forward or fast backward (the ones with the two triangles) there is also a small delay hardcoded in VBA (50 milliseconds) in order to avoid the effect you are describing. On very fast machines, this may be not long enough. You can increase the constant C_MIN_DELAY = 50 in the code or you can simply use the normal forward and backward button and control the speed with the value in the cell defining the delay in milliseconds. It has nothing to do with the version of Excel. I guess you have a very fast machine, or it may have to do with your monitor.
1 reply
Image
Complementing the previous post: Animated Races of the 4 big Football Leagues in Europe: Premier League, Serie A, La Liga and Bundesliga Continue reading
Posted Jul 11, 2020 at Clearly and Simply
Image
An alternative solution to the controversial animated Bar Chart Races in Microsoft Excel Continue reading
Posted Jul 7, 2020 at Clearly and Simply
Jo, please have a look at this example workbook: Download customizable tooltips on more excel charts (71.8K) Also, you are commenting on a very old post (from 2010), so I would recommend to also have a look at this article: Customizable Tooltips on Excel Charts
1 reply
Jeremy, from a technical perspective, a monthly view would be possible, too. You would have the week numbers in the rows and all appointments of one day would be shown one after the other without the entire hourly timeline of a day, just like the monthly calendar view in Outlook. This would require a more complicated formula than the weekly view and you would probably be better off by implementing this in VBA instead of using formulas. That being said, I do not really see the value of a monthly view in a Project Calendar. The months would only differ in non-regular appointments (other than daily, weekly, monthly) and I do not think that this would be worth the effort. Anyway, if you want to try, you can use the weekly template posted above as a starting point.
1 reply