You can perform a simple calculation (division, multiplication, addition, or subtraction) on a list of numbers without the need to write formulas. Is this possible?!

With Paste Special tool in Excel, you can copy-paste any value to one or more values using a mathematical operation. Hence, the outcome in the cell is the result of the operation.

In this list of packages, the weights are all in LB. You just need to convert each package’s weight to KG. Well, there are many techniques to do that, however, to me, the simplest one is by following these steps:

Write the conversion factor (LB to KG, 0.4536) somewhere in the sheet.

Copy the value you typed, select the range of values you need to convert, and then right-click to select Paste Special.

In the ‘Operation’ group, select ‘Multiply’ (as you need to multiply each package’s weight value by the conversion factor to convert it into KG). Hit OK

You’re done. The weights are all converted into KG without the need to write any formula on the sheet, and without using extra columns to perform the calculation process.

Suppose that you manage a set of suppliers’ agreements which start and expire on specific dates. You need to be warned on the expiry of any agreement, say, 10 days ahead of the deadline. How can you use Microsoft Excel to do that?

Fortunately, the latest versions of Excel (2007 & 2010) allow for what’s called Conditional Formatting. This feature gives you the ability to change formatting of cells and ranges based on criteria you set. Let’s see how we can manage our agreements’ deadlines with this feature.

A scenario of three agreements having different Expiry dates, 101 (expires in less than 10 days), 102 (expires in nearly 3 months), and 103 (already expired). Notice that the current date is March 8th, 2012.

We need to use Conditional Formatting to highlight the expiry date in green background whenever the agreement is expired or expires in less than 10 days as of the current date.

From Home tab, Styles, Conditional Formatting select Manage Rules.

Hit the New Rule button.

From Select a Rule Type, select the “Use a formula to determine…” option. Then write the formula shown in the figure above. (D3 is the reference cell for the Expiry Date of the first agreement. Make sure you remove the dollar sign ($) from $D$3 using the F4 button).

Select the formatting into which the approached Expiry Date cell will turn into once the condition is met.

Hit OK buttons. The first agreement’s expiry date is highlighted in this example as it expires in less than 10 days.

You need to apply the formatting to other agreements by dragging the first cell then selecting the Fill Formatting Only option from the Paste Tip.

You’re done. Agreement 102 is not formatted as its Expiry Date is still far, whereas 103 is already expired and must be highlighted.

Test your work by changing 103’s expiry date to 25-Apr-2012, it turns un-formatted.

With this dashboard, your life is much easier now, I believe, in managing agreements’ renewal deadlines. Every time you open the workbook, Excel will give you the status of your agreements with warning lights on the ones approaching expiry.

From time to time, people approach me with an Excel problem that looks easy to calculate while, unfortunately, there’s no straightforward solution to it. The problem is calculating the difference (in hours) between two date-time formatted cells.

Example:

Cell A1: 2/15/2012  10:00:00 AM

Cell B1: 2/18/2012  08:00:00 PM

What is the elapsed time between both dates (in hours)?

You can get the answer by using the below formula in cell C1:

= INT(B1-A1)*24+(((B1-A1)-INT(B1-A1))/0.04166666)

The result will be: 82 HRS

 

 

 

 

 

 

 

Formula rationale:

Excel stores all dates as integers and all times as decimal fractions. With this system, Excel can add, subtract, or compare dates and times just like any other numbers. Times are stored as decimal numbers between .0 and .99999, where .0 is 00:00:00 and .99999 is 23:59:59. So, an hour is about 0.041666 of the 0.99999.

Hence, with multiplying the integer part of the date serial number by 24 (to get number of hours for the whole elapsed days) and adding the result to the number of hours represented in the fraction part (the fraction part divided by 0.014666) you get the total number of elapsed hours between both dates.

In this part of “Interrelationship Digraph: Demystifying Complex Relationships” video I explain how we can use the Interrelationship Matrix to reflect results of the Interrelationship Digraph. I also show how we can construct this matrix using Microsoft Office Excel through Conditional Formatting and the COUNTIF function.

Interrelationship Digraph or Relations Diagram is one of the seven Management and Planning Tools that helps us analyze the cause-and-effect relationships among different issues in a complex situation. It also helps us focus on vital few issues with highest priorities which makes this tool echo Pareto principle or the 80/20 rule in concept.

In this part of “Interrelationship Digraph: Demystifying Complex Relationships” video I explain the tool, its uses, and how to construct it using Microsoft Office Visio.

Prioritization Matrix is a decision making tool and is one of the seven management and planning tools used in Six Sigma and Quality Management in general. It is used to determine the best option to select amongst several ones based on specific criteria using numerical values.

Watch this video to know how to create the Prioritization Matrix with Microsoft Office Excel.

During the Define step of a Six Sigma project the project team needs to identify the problem or the opportunity that the project is intended to solve. Usually, there would be more than one potential problem that can be addressed in a Six Sigma project; however, not all of them would have significant improvement. So, the project team needs to filter out those problems that if resolved will result in huge improvement and positive impact on the bottom line, and this is what Pareto analysis is used for. 

Watch this video to know more about using Microsoft Office Excel to create a Pareto chart in a Six Sigma project.