fbpx
Search
Close this search box.

How to visualize multiple variables over several years? [Contest]

Share

Facebook
Twitter
LinkedIn

Kaushik, one of our forum members has an interesting problem.

Need to quickly visualize 3 variables ( Company, years, Financials) in a single […] chart.

Multiple variables over several years – How to chart?

Let’s take a look at the data first.

multiple-variable-data

Ways to visualize such data…

Whenever you want to visualize business data, the first thing to ask is,

  • What is the purpose of this visualization?

Related: How to create right chart from your data?

Since we don’t know why Kaushik wanted to visualize all 100 numbers in a single chart, let’s define some goals for the chart.

  • Yearly trends of financial metrics
  • How one company compares with another over years
  • How individual metrics contribute to overall revenue (all the 5 metrics add up to 100)
  • What are the most interesting numbers, drastic changes or alarming trends

One way to answer these questions is thru a panel chart. It shows massive data in a concise chart and allows for exploration of inter-relationships.

See below.

Absolute trends:

Made with sparklines.

panel-chart-yearly-trends-multivariable-data

Indexed Trends:

And of course you can use indexed charts technique to explore the numbers better.

panel-chart-indexed-trends-multiple-variables

Your challenge – How would you visualize this data?

So here is something fun for you. Visualize this multiple variable data and share your charts.

What’s in it for you?

This is a contest. The two most awesome charts will $100 Amazon Gift Cards.

How to submit your entries:

  • Simple. Download the data workbook.
  • Create your chart in a new tab.
  • Email your files to chandoo.d@gmail.com with the subject “Multiple variable challenge”
  • Send in your entries before July 4th, 2016 (Monday).
  • We will showcase all the entries and pick winners by July 11th.

Fine print:

  • You may submit multiple entries, but you can maximum of one gift card.
  • No VBA based solutions. Excel or Power BI charts are ok.
  • If you cannot accept Amazon gift card (because of where you live), you can opt for $100 cash thru PayPal.

Get busy charting…

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

15 Responses to “How to visualize multiple variables over several years? [Contest]”

  1. Chirayu says:

    I was the first to reply to the thread on the forum 🙂 Downloaded your workbook & thought it was a cool concept but 2007 can't see sparklines. Wondering if I can just submit the one I already answered in the forum. I'll probably add a bit more flashiness to it though

  2. Chandeep says:

    Hi Chandoo,
    For Ultratech Cement all values are not adding up-to 100. Check the total of the values in
    2011 (102)
    2013 (90)
    2014 (95)
    2015 (96)

  3. Blaz says:

    Cannot wait to see your attempts! :=)

  4. Santosh says:

    Looking forward for the results.

  5. Marcel says:

    Yeah... any news on this?

  6. Paul says:

    Chandoo, how about results ?

  7. Santosh Chaube says:

    Any updates , when we can get to see the contest results

  8. Blaz says:

    Chandoo, did you mean to publish results on July 11th 2017? 🙂

  9. Tobias says:

    I think a lot of people are keen to see the results that will be "showcased by July 11th".

  10. Arnaud says:

    Hi Chandoo,

    Can you give us a feedback about expected date to communicate the results of the contest ?
    Thank you Chandoo.

    Arnaud

  11. DME says:

    Hi Chandoo,

    Hope all is well. Will the contest winners ever be announced? I know we're all looking for an update, so hoping to hear something soon.

    Enjoy your weekend,

  12. Danish boy says:

    Hi Chandoo,

    Hope all is well also. I am sure you red the comments ; we are all looking for the results ... Or just an answer from you, in a first time. Almost 2 months and no word from your side.

    Enjoy NZ.

    • Chandoo says:

      Hi Danish Boy and other participants. I am deeply sorry for the delay in publishing the results. The move to NZ has thrown a wrench to my work and I am slowly getting back to regular schedule. I will be publishing the results next week as everything is already compiled and ready to go.

  13. Kelly says:

    Hi Chandoo,
    I'm keen to check out the results from this, has anything been published yet...?

    Thanks,

Leave a Reply