Convert fractional Excel time to hours & minutes [Quick tip]


Convert fractional Excel time to hours & minutes [Quick tip]
Zum Original Blog Chandoo.org

Time for another quick Excel tip.

Convert fraction to hours & minutes in Excel - Tip

Lets say the park near your house rents tennis courts by hour. And they charge $10 per hour. At the end of an intense tennis playing week, Linda, the tennis court manager called you up and said you need to pay $78 as rent for that week.

How many hours did you play?

Of course 78/10 = 7.8 hours.

But we all know that 7.8 hours makes no sense.

We also know that 7.8 hours is really 7 hours 48 minutes.

So how to convert 7.8 hrs to 7:48 ?

That is our quick tip for the day.

Custom cell formatting to show hours & minutes in Excel from fractional timeSimple, assuming the fractional time is in cell A1,

Use below steps to convert it to hours & minutes:

What is the average speed of this road trip? [Solution & Discussion]

Backlinkseller

What is the average speed of this road trip? [Solution & Discussion]
Zum Original Blog Chandoo.org

Last week, we had an interesting homework problem – What is the average speed of this road trip?

We received more than 150 answers. But to my surprise, 57 of them are wrong. So today, lets learn how to calculate the average speed correct way.

Please click here to download solution workbook.

What is Speed?

Back in school days, we learned what speed is.

Speed = Distance / Time

Now lets look at the problem

Here is the data:

what-is-jacks-average-speed

Jack kept track for every 50 mile interval. And he did that for 12 intervals. So the total distance is 12×50 = 600 miles.

We just need to know how much time Jack took to cover the 600 miles to calculate the speed.

CP017: Top 10 non-Excel MS Office tips for you – Interview with Paul Woods – Office MVP & Blogger

Backlinkseller

CP017: Top 10 non-Excel MS Office tips for you – Interview with Paul Woods – Office MVP & Blogger
Zum Original Blog Chandoo.org

In the 17th session of Chandoo.org podcast, lets leave Excel aside and talk about other MS Office apps.

Thats right. We will be learning 10 tips on how to use Word, Power Point, Outlook etc. Ready?

CP017: Top 10 non-Excel MS Office tips for you - an interview with Paul Woods - Office 365 MVP

What is in this session?

A while ago, I got an email from Paul Woods. Paul is an Office 365 MVP, fellow blogger, father of twins and intrepreneur.  Since we have several coincidences in our lives (both of us are MVPs since 2009, dads to twins, bloggers & started businesses), I wanted to know more about him. I invited him to a podcast interview so that he can tell us his story and teach us how to use MS Office better.

Mapping relationships between people using interactive network chart

Backlinkseller

Mapping relationships between people using interactive network chart
Zum Original Blog Chandoo.org

Today, lets learn how to create an interesting chart. This, called as network chart helps us visualize relationships between various people.

Demo of interactive network chart in Excel

First take a look at what we are trying to build.

Network Relationships - Interactive Chart in Excel - Demo

Looks interesting? Then read on to learn how to create this.

Note: thanks to Hans whose email question inspired me to create this chart.

Tutorial to create interactive network chart in Excel

Note: This tutorial requires intermediate-to-advanced Excel knowledge. So if you are beginner, learn the basics & advanced concepts first and then comeback for this.

In order to create this chart in Excel, we need to first understand various ingredients of it.

As you can see, the chart contains these parts:

  1. A set of dots, each representing one stakeholder

What is the average speed of this road trip? [homework]


What is the average speed of this road trip? [homework]
Zum Original Blog Chandoo.org

Its homework time again.

This time, lets tackle an interesting & everyday problem.

Lets introduce our protagonist of the story – Jack.

Jack likes long road trips, smell of freshly brewed Colombian coffee, clicky-clack sound of his computer keyboard. He hates toll plazas (they slow him down) & Potassium permanganate.

And oh yes, Jack is obsessive about analyzing every little bit of data in his life.

That brings us to the recent road trip he took.

It was a total of 600 miles.

And Jack tracked the speed at which he was covering every 50 mile distance.

The data is neatly typed in to an Excel workbook (snapshot below), in the range A2:A13.

what-is-jacks-average-speed

CP016: 3 Must have books for aspiring analysts

CP016: 3 Must have books for aspiring analysts
Zum Original Blog Chandoo.org

In the 16th session of Chandoo.org podcast, lets review 3 very useful books for aspiring analysts.

CP016: 3 Must have books for aspiring analysts - Chandoo.org podcast - become awesome in data analysis, charting, dashboards & VBA using Excel

What is in this session?

Analytics is an increasingly popular area now. Every day, scores of fresh graduates are reporting to their first day of work as analysts. But to succeed as an analyst?

By learning & practicing of course.

And books play a vital role in opening new pathways for us. They can alter the way we think, shape our behavior and make us awesome, all in a few page turns.

So in this episode, let me share 3 must have books for (aspiring) analysts.

Note: these are not only 3 books you should read. But these 3 are the ones I am reading now and I think they will certainly help you.

A dashboard to visualize this FIFA worldcup [guest post]

A dashboard to visualize this FIFA worldcup [guest post]
Zum Original Blog Chandoo.org

This is a guest post by Krishna, a football lover & one of our readers. He is also a student of Chandoo.org online VBA classes.

The wait for lifting the most valued priced in football for Germans was finally over. For a football fan, world cup is best time that is scheduled every four years and that if your favorite team lifting the trophy is like your crush is going on a date with you. :)

A sneak-peek at the final dashboard

Here is the final dashboard (it has more functionality than depicted). Click on it to enlarge.

FIFA 2014 Worldcup Excel dashboard

Download the dashboard workbook

Click here to download the workbook. Refer to it while reading this article for most benefit.

Story of my first ever 200KM bike ride (plus an Excel dashboard with ride stats)

Story of my first ever 200KM bike ride (plus an Excel dashboard with ride stats)
Zum Original Blog Chandoo.org

Ok people. Let me tell you that this post is almost not about Excel. It is about how one Excel blogger’s (yours truly) dream of long distance cycling came true. So sit back, grab your favorite drink and read between sips.

PS: if you just want to see the dashboard, click here.

So what is this all about?

Last Sunday (27th July) & Monday (28th), I finished my first ever 200KM bicycle ride. I rode for a little more than 12 hours, burned 5,179 calories & rode 206 kilometers.

It is definitely one of the most memorable, tiresome & uplifting experiences in my life. So naturally, I want to share the story with you.

Thats me at the end of 118 km ride on Day 1 - near Annavaram village entrance

Become awesome in data analytics, charting & Power Pivot with Houston Masterclass [reminder]

Become awesome in data analytics, charting & Power Pivot with Houston Masterclass [reminder]
Zum Original Blog Chandoo.org

Hello friends,

Let me stop our broadcast of Excel tips to bring an announcement.

Houston, we are going to get awesome

AED 2014 - become awesome in Excel, data analytics, power pivot & dashboard reporitng

As you may know, I am doing 4 days of live masterclasses on Advanced Excel, Dashboards, Power Pivot & Data Analytics in Houston (Texas) this September.

  • 15 & 16 September (Mon & Tue) on Advanced Excel & Dashboard Reporting
  • 17 & 18 September (Wed & Thu) on Power Pivot & Data Analytics

This 4 day masterclass is aimed at managers, analysts, reporting professionals, executives in sales, marketing, customer service or anyone who wants to learn about Data Analytics, Advanced Excel, Dashboard reporting.

CP015: Handling big data, Controlling model railroad sets, Overcoming Excel obsession & more – ASK CHANDOO

CP015: Handling big data, Controlling model railroad sets, Overcoming Excel obsession & more – ASK CHANDOO
Zum Original Blog Chandoo.org

In the 15th session of Chandoo.org podcast, lets answer some of your burning Excel questions.

Handling big data, Controlling model railroad sets, Overcoming Excel obsession & More - ASK CHANDOO

What is in this session?

Around last week, I invited you to ask me anything. More than 150 people responded to this call and sent in their questions. Since answering all the questions is not possible, I handpicked roughly 10 questions to answer in this episode of Chandoo.org podcast.

In this podcast, you will learn,

  1. How to fill blank cells with data from above
  2. How to work with Big data in Excel
  3. How to combine data from multiple sources & analyze it in Excel
  4. How I am managing my life after starting Chandoo.org
  5. How to create and distribute stand-alone Excel products