Who's counting?

"Under the International Organization for Standardization (ISO) standard 8601, a week always begins on a Monday, and ends on a Sunday. The first week of a year is that week which contains the first Thursday of the year, or, equivalently, contains Jan-4.

While this provides some standardization, it can lead to unexpected results - namely that the first few days of a year may not be in week 1 at all. Instead, they will be in week 52 of the preceding year! For example, the year 2000 began on Saturday. Under the ISO standard, weeks always begin on a Monday. In 2000, the first Thursday was Jan-6, so week 1 begins the preceding Monday, or Jan-3. Therefore, the first two days of 2000, Jan-1 and Jan-2, fall into week 52 of 1999.

An ISO week number may be between 1 and 53. Under the ISO standard, week 1 will always have at least 4 days. If 1-Jan falls on a Friday, Saturday, or Sunday, the first few days of the year are defined as being in the last (52nd or 53rd) week of the previous year.

Unlike absolute week numbers, not every year will have a week 53. For example, the year 2000 does not have a week 53. Week 52 begins on Monday, 25-Dec, and ends on Sunday, 31-Dec. But the year 2004 does have a week 53, from Monday, 27-Dec , through Friday, 31-Dec."

"The function WEEKNUM() in the Analysis Toolpack addin calculates the correct week number for a given date, if you are in the U.S. The user defined function shown here will calculate the correct week number depending on the national language settings on your computer."

Labels: Formulas, General, Reference, Shortcuts, Tips, Tutorials

The drunken cousin

=DATEDIF(EarliestDate,LatestDate,Interval)

=DATEDIF(A2,A1,"d")

"Many users are surprised to discover that Excel cannot work with dates prior to the year 1900. The Extended Date Functions add-in (XDate) corrects this deficiency, and allows you to work with dates in the years 0100 through 9999."

Labels: Addins, Formulas, General, Macros, Reference, Shortcuts, Tips, Tutorials

All(most) all you need to know

• Add numbers
  
• Subtract numbers
  
• Multiply numbers
  
• Divide numbers

Labels: Formulas, Functions, General, Reference, Tips, Tutorials

Basic tutorial

"This practical spreadsheet lesson offers easy answers to life's perplexing math problems like How much will my dream car really cost after financing?

Students will calculate the cost of purchasing their very own Lamborghini sports car and determine if the ultimate price tag is really worth the investment. "

Labels: Formulas, Functions, General, Reference, Shortcuts, Templates, Tips, Tutorials

Using the OFFSET function

"How to create a data list to manage transactions, add and delete rows from the list, and accurately calculate a running balance using the OFFSET function."

Labels: Formulas, Functions, General, Reference, Shortcuts, Tips, Tutorials

Functions and Macros

• Remove internal worksheet/workbook protection
  
• Why your sum is a penny off...
  
• Highlight row without losing color formatting
  
• Why use -- in SUMPRODUCT formulae
  
• Using SUMPRODUCT() to calculate variable rates and commissions
  
• Three-dimensional SUMIF()s
  
• Bitwise Logical Functions
  
• Sampling from a range
  
• Summing every Nth column or row
  
• Worksheet and Workbook names using the CELL() function

Labels: Formulas, Functions, General, Reference, Shortcuts, Tips, Tutorials

Point to the spot

Labels: Formats, Formulas, General, Reference, Tips, Tutorials

Format don't show

1. Select the range
   
   
2. Goto Format>Conditional Formatting
   
   
3. Select Formula Is
   
   
4. Enter
   =A2=A1
   
   
5. Click the Format button.
   
   
6. Select a font color to match the cell background color.
   
   
7. Click OK and OK

1. Select the range
   
   
2. Go to Format>Conditional Formatting
   
   
3. Choose Formula Is
   
   
4. Enter
   =COUNTIF($A$2:$A$100,A2)>1
   
   
5. Click the Format button.
   
   
6. Select a font or background color for highlighting.
   
   
7. Click OK and OK

Labels: Formulas, General, Reference, Shortcuts, Tips, Tutorials

If 4, show kumquat

Labels: Customize, Formulas, General, Graphics, Reference, Shortcuts, Tips, Tutorials

Formula construction

=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,31)

=CELL("filename",$A$1) 

=MID(text,start_num,num_chars) 

Labels: Formulas, General, Reference, Tips, Tutorials

Hail Tufte

Labels: Charts, Formats, Formulas, General, Reference, Tips, Tutorials

and More

"Students of analytic geometry, (the kind that combines algebra and geometry), often work in one of two coordinate systems: Cartesian or Polar - and frequently must convert from one to the other.

The Cartesian system locates points on a plane by measuring the horizontal and vertical distances from an arbitrary origin to a point. These are usually denoted as a pair of values (X, Y).

The Polar system locates the point by measuring the straight line distance, usually denoted by R, from the origin to the point and the angle of an imaginary line from the origin to the point, θ, (Greek letter Theta), measured counterclockwise from the positive X axis."

Labels: Charts, Formulas, General, Reference, Tips, Tutorials

Three is not always enough

Labels: Customize, Formats, Formulas, General, Reference, Shortcuts, Tips, Tutorials, VBA

I bid 3 Red

What if you would like to know the color name or to count or to sum cells by a fill color? There is no built-in function in Excel.

In this case you can make a User Defined Function (UDF).

Here is the sample UDF that you can use to analyze, count and sum the cells depending on their filled color.

These UDF function can be used in the same way as built-in functions that you can use in the worksheet.

• AnalyzeColor Returns the color name, the color index or color index in RGB.
  Syntax: AnalyzeColor(color range, optional; "text" or "index" or "rgb". When it is omitted "text" is used.)
  
  
  
• CountColor Counts the number of cells depending on their filled color.
  Syntax: CountColor(color range, target range)
  
  
  
• SumColor Adds all the numbers in a range of cells depending on their filled color.
  Syntax: SumColor(color range, target range)

Labels: Formulas, Functions, Reference, Tips, Tutorials, VBA

Things should to function

"Excel allows you to create custom functions, called "User Defined Functions" (UDF's) that can be used the same way you would use SUM(), VLOOKUP, or other built-in Excel functions.
The Excel user who wishes to use advanced mathematics or perform text manipulation is often seriously disappointed by Excel's limited library of formulas and functions. A UDF is simply a function that you create yourself with VBA.

The following is a sample that is a good candidate for a UDF:

Function CtoF(Centigrade)
CtoF = Centigrade * 9 / 5 + 32
End Function

In the Worksheet you would enter something like:
=CtoF(A1)

Labels: Formulas, Functions, General, Reference, Tips, Tutorials

A very useful Excel feature

Labels: Formulas, Functions, General, Reference, Tips, Tutorials

Show the star

1. Create an AutoShape on the Worksheet
   
2. With the shape selected, type an equal sign in the formula bar.
   
3. Enter the address of the linking cell (or click the cell)
   
4. Hit Enter

Labels: Customize, Formulas, General, Graphics, Link, Reference, Tips, Tutorials

Good orderly direction

• Each argument within an array must have the same amount of rows and columns.
• You must enter an array by pushing Ctrl+Shift+Enter.
• You cannot add the {} (braces) that surround an array yourself, pushing Ctrl+Shift+Enter will do this for you.
• You cannot use an array formula on an entire column.

"Have you ever sat in front of your monitor pulling your hair out trying to identify duplicate entries in a list? If so, you should learn about Microsoft Excel's array formulas. In fact, you can use array formulas to perform calculations that are otherwise impossible in Excel, and you can enhance the power of some of the program's existing functions."

"Array Formulas are formulas that work with arrays, instead of individual numbers, as arguments to the functions that make up the formula"

Labels: Formulas, General, Reference, Tips, Tutorials

Highlight entries

Labels: Formulas, General, Macros, Reference, Tips, Tutorials, VBA

What are the chances

"Excel is the widely used statistical package, which serves as a tool to understand statistical concepts and computation to check your hand-worked calculation in solving your homework problems. The site provides an introduction to understand the basics of and working with the Excel. Redoing the illustrated numerical examples in this site will help improving your familiarity and as a result increase the effectiveness and efficiency of your process in statistics."

• Descriptive Statistics
  
• Normal Distribution
  
• Confidence Interval for the Mean
  
• Test of Hypothesis Concerning the Population Mean
  
• Difference Between Mean of Two Populations
  
• ANOVA: Analysis of Variances
  
• Goodness-of-Fit Test for Discrete Random Variables
  
• Test of Independence: Contingency Tables
  
• Test Hypothesis Concerning the Variance of Two Populations
  
• Linear Correlation and Regression Analysis
  
• Moving Average and Exponential Smoothing

Labels: Formulas, Functions, General, Reference, Tips, Tutorials