Preparing a RAG report

To track status of a project or tasks, RAG (Red, Amber, Green) report is commonly used. Given below is a sample RAG report as on 04-Jun-09 The following logic is used for creating the report   For Open tasks:  Red: SCD – today()+1 is -ve Amber: SCD – today()+1 is 0 Green: SCD – today()+1 [...]

Read Full Post »

10 tips for great formatting

Here is a list of 10 formatting tips that would help make your excel worksheet look great Toggle grid – After you have done your analysis, make the gridlines disappear from your worksheet. It gives a much cleaner look and is very eye friendly. Here is how you do it – Add Form toolbar – [...]

Read Full Post »

Cows and Bulls

Here is how you can play the game Cows and Bulls in excel The objective of the game is to guess the secret number (which is hidden from the player). The player can guess the number as many times. If the matching digits are on their right positions, they are “bulls”, if on different positions, [...]

Read Full Post »

Identifying duplicates in a list

Tips for identifying duplicates in a list Lets say the list is Tip#1: Conditional formatting Go to conditional formatting box and enter “Formula is” =COUNTIF($A$1:$A$8,A1)>1 Format the box with a different colour (yellow in above) Format paint for all cells A1 to A8 with ‘format painter’ brush Tip#2: Array formula Enter an array formula {=IF(MAX(COUNTIF(A1:A8,A1:A8))>1,”Duplicates”,”No [...]

Read Full Post »