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: Functions, VBA

How to use names in Excel

• How To Define Range Names
  
  
• How To Use Range Names
  
  
• Absolute And Relative Addressing
  
  
• The Context Of Names
  
  
• Special Names
  
  
• A Step Further: A Formula In A Defined Name
  
  
• Dynamic Names
  
  
• Passing Arguments To A Defined Name Formula
  
  
• Bugs in Excel's Name Object

Labels: Formulas, Functions, Tips

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, Tips

Simple obfuscation

Labels: Functions, Tips, Tutorials

Who, What, Where

Current directory

=INFO("directory")

Available bytes of memory

=INFO("memavail")

Memory in use

=INFO("memused")

Total bytes of memory

=INFO("totmem")

Number of active worksheets

=INFO("numfile")

Cell currently in the top left of the window

=INFO("origin")

Operating system

=INFO("osversion")

Recalculation mode

=INFO("recalc")

Excel version

=INFO("release")

Name of system. (PC or Mac)

=INFO("system")

More:

Excel-VBA.com:
Excel Spreadsheet Information Functions



See all Topics
excel

Labels: Functions, Tips

No formulas

Example:

In the sheet there are two lists: List 1 is in column A (in cells A2:A10), and List 2 is in column C (in cells C2:C10).

1. In Cell B1 type "List number".
   
2. In Cells B2:B10, enter the number 1.
   
3. In Cells D2:D10, enter the number 2.
   
4. Cut Cells C2:D10 and paste them into Cell A11.
   
5. Press Ctrl+*, then press Ctrl+F3, and enter a name for the list (such as Compare).
   
6. Select cell D4 or another worksheet.
   
7. From the Data menu, select Consolidate.
   
8. Select Count as the Function.
   
9. In the Reference box, press F3 and paste the Name you defined for the list.
   
10. Click Add.
    
11. Select both Top row and Left column "Use labels in" checkboxes.
    
12. Click OK.

Labels: Formulas, Functions, Tips, Tutorials

VLOOKUP

"Excel will adjust cell references in formulas when you insert or delete rows or columns.

For example, if the cell C1 contains the formula =A1/B1 and you insert a column to the left of column A; the formula will change to =A1/C1.

The problem then occurs with VLOOKUP. Its column index number argument is a simple number, not a reference.

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup

For Example:

1. Choose a blank worksheet
   
2. In cells A1 and A2, enter the values 1 and 2.
   
3. In B1 and B2, enter Jan and Feb.
   
4. Select all four cells and drag the fill handle at the bottom right-hand corner of the selection downward to row 12.

You should now have the numbers 1 through 12 in column A and the months Jan through Dec in column B.

In cell D1 enter the formula

=VLOOKUP(C1,A1:B12,2).

Now enter any number from 1 to 12 in cell C1. The formula will select the corresponding month name.

To demonstrate the problem, right-click on the heading of column B and choose Insert.

The formula changes to =VLOOKUP (D1,A1:C12,2), which returns 0.

Excel correctly changed the cell reference from C1 to D1 and expanded the lookup range to include the inserted column, but it cannot change the column index number.

Press Ctrl-Z to undo the column insertion.

The solution is to modify the formula so that the column index number is not hard-coded but instead is calculated from cell references.

You could use the COLUMN() function that returns the column number of the reference and, as in this example, compute the number of columns between the first and last columns:

=VLOOKUP (C1,A1:B12,COLUMN(B1)-COLUMN(A1)+1).

A more esthetically pleasing, or sophisticated, function might be:

COLUMNS(array)

This returns the number of columns in an array or reference.

The modified lookup function looks like this:

=VLOOKUP (C1,A1:B12,COLUMNS(A1:B1)).


Either way, now if a column is inserted in the middle of the range, the column index will be adjusted."

From a PC Magazine article
By Neil J. Rubenking

Also:

Allan Wyatt's ExcelTips:
Using the Column Function

OzGrid:
Copy Rows
(Scroll down to about the middle of the page)



See all Topics
excel

Labels: Formulas, Functions, Tips, Tutorials, VBA

Month Day, Day Month

"QDE is a fully-functional Excel Add-in that provides quick input of dates, in all international formats. It handles quick data entry interpretation and reflects the three interacting issues of Date System, Day, Month Year ordering, and number of digits used in the quick date entry. With QDE you enter just as many digits as needed to clearly identify the date, QDE will do the rest."

Also see:

Chip Pearson:
Date and Time Entry

MathTools.net:
Time and Date

And:
Date Arithmetic




See all Topics
excel

Labels: Formulas, Functions, Tips, Tutorials

Who's counting?

2004 has 53 weeks. January 1 is the only day in the first week of 2005. Week 2 starts on Sunday 1/2/2005.

Chip Pearson is the Date and Time guy:
Week Numbers In Excel

"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 first week of 2005 should start on January 3. The first and second would be part of week 53 of 2004.

Wikipedia:
Week Dates

If your week starts on a different day, you can use the Analysis ToolPac function:
=WEEKNUM(A1, 2) for a week that starts on Monday, =WEEKNUM(A1) if it starts on Sunday.

Also this from ExcelTip.com:
Weeknumbers using VBA in Microsoft Excel

"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."

In Access:
DatePart Function

If your work week is always Saturday through Friday then

datepart("ww",[DateField],7,1)

will return 1 for 1/1/2005 through 1/7/2005, 2 for January 8-14/2005, etc.
Otherwise use 1 for Sunday through 7 for Saturday.

The last number sets these parameters:

1, Start with week in which January 1 occurs (default).
2, Start with the first week that has at least four days in the new year.
3, Start with first full week of the year.



See all Topics
excel

Labels: Formats, Functions, Tips

Zero is nothing

• Display or hide all zero values on a worksheet
  
  
• Use a number format to hide zero values in selected cells
  
  
• Use a conditional format to hide zero values returned by a formula
  
  
• Use a formula to display zeros as a blanks or dashes
  
  
• Hide zero values in a PivotTable report

Labels: Formulas, Functions, Tips

The drunken cousin

=DATEDIF(EarliestDate,LatestDate,Interval)

=DATEDIF(A2,A1,"d")

Here's THE source for date math:
Chip Pearson:
All About Dates

Also:

John Walenbach:
Extended Date Functions Add-In

"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: Formulas, Functions, Tips

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. "

Dream Car

Also:

Loan templates
Basic Financial Calculations



See all Topics
excel

Labels: Functions, 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."

Cash flow using OFFSET

Office.microsoft.com:
Calculate a running total

Using Auto Calculate for Running Totals



See all Topics
excel

Labels: Formulas, Functions

Cardinal numbers

From the Microsoft Knowledgebase collection:


How to Convert a Numeric Value into English Words - 213360

This support article contains the VBA code needed to create a User Defined Function, You can, then, change 32.50 into "Thirty Two Dollars and Fifty Cents" by entering something like the following formula into a cell:

=SpellNumber(32.50)


Also:

From John Walkenbach:

BAHTTEXT?
If you use Excel 2002+, you may have discovered the new BAHTTEXT worksheet function. This function converts a number to Thai text, and adds a suffix of "Baht".
This is the only built in function that translates numbers to words and then only in Thai.

For Access see:
Access- Numbers to Words


To create cardinal numbers in Word, see:
Word - Numbers to Words



See all Topics
excel

Labels: Functions, VBA

Info source

"What is the Excel Wiki?

That is really up to the community! Right now, the message board is a pull technology. Someone asks a question and then someone answers it. My idea... allow the community to write articles on their favorite tips about Excel. In theory, we would eventually have an article about every Excel function and every Excel command.

Currently, there are sections for Commands, Functions, VBA, and Tips. If you think there should be a new section, feel free to edit the ExcelWiki and add a link to a new section."

Labels: Functions, Tips

Definitions and list

SKEW(number1,number2,...)

Returns the skewness of a distribution. Skewness characterizes the degree of asymmetry of a distribution around its mean.

Positive skewness indicates a distribution with an asymmetric tail extending toward more positive values.

Negative skewness indicates a distribution with an asymmetric tail extending toward more negative values.

Labels: Functions