What's the count

". . . know what range is selected at any given time. You can look at the Name Box, but that only show the active cell."

Labels: Customize, Formulas, Macros, Reference, Shortcuts, Tips, VBA

A hard way to do an easy job

"Excel doesn't have an easy way to append multiple text files into one worksheet through the user interface. From time to time I hear customers asking how to do this in an easy way. Do you have to use Access or VB code to solve this problem? No. There's a way to do it in Excel by using a simple SQL statement in the connection string."

Labels: Formulas, Functions, Tips

Fill in the spaces

"If the datasheet data for the category axis contains date number formatting, Microsoft Graph automatically uses a special type of axis in your chart called a time-scale axis.
A time-scale axis shows a blank category for dates for which you have no data. If you do not want to see these gaps — for example, if you have data for 1-Jan, 15-Jan, 3-Feb, 12-Feb, and 2-Mar, and you want to plot the days next to each other - you can change the time-scale axis to a standard category axis.
On the Chart menu, click Chart Options, click the Axes tab, and then click Category under Category (X) axis."

Labels: Charts, Customize, Formats, Formulas, Tips

and More

Famous curves from Cal State at LA. All your favorites from Astroid to Witch of Agnesi.

Here is a collection of Functions relating to astronomy from Stargazing.net.

Can't tell who might be interested in the obliquity of the equator given date in days after J2000.0. See: Astro VBA

Other Curve stuff:

DelphiForFun.org: converting polar coordinates to Cartesian coordinates.

"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: Formulas, Graphics, Link

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

A very useful Excel feature

Labels: Formulas

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"

PacBell.net/beban/:
The file "Array Functions" contains 27 Function Procedures and 2 Sub Procedures for manipulating arrays (and worksheet ranges).
Excel Tools

Bob Ulmas:
Using Array Formulas in Excel

Daily Dose of Excel:
Anatomy of an Array Formula

Support.Microsoft.com:
Sample Visual Basic macros for working with arrays

Limitations for working with arrays in Excel

When to use a SUM(IF()) array formula



See all Topics
excel

Labels: Formulas, Tutorials

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

Circular solution

"In Microsoft Excel you can avoid circular references when you create a running total by storing the result in a non-calculating part of a worksheet. This article contains a sample Microsoft Visual Basic for Applications procedure that does this by storing a running total in a cell comment."

Microsoft Support:
Create a running total in a cell comment

The macro runs each time the value of a cell changes.
It adds the current value of the cell to the value of the cell comment. Then it stores the new total in the cell comment.

I'm sure someone can come up with other uses for this macro.



Also see:

Decision models.com:
Repetitive Calculation Features and Add-Ins

Daily Dose of Excel
Dick Kusleika
Circular References - The Good Kind



See all Topics
excel

Labels: Formulas, Macros, Tutorials, VBA

Highlight the best

Labels: Formats, Formulas

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

Display properties

Sub CommentThem()
 Dim cell As Range
 On Error Resume Next
 Selection.ClearComments
 On Error GoTo 0
 For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
   If cell.Formula <> "" Then
    cell.AddComment
    cell.Comment.Visible = False
    On Error Resume Next 'fails on invalid formula
    cell.Comment.Text Text:=" Address:  " & cell.Address(0, 0) & Chr(10) & _
      " Value:  " & cell.Value & Chr(10) & _
      " Format:  " & cell.NumberFormat & Chr(10) & _
      " Formula: " & cell.Formula
    On Error GoTo 0
   End If
 Next cell

End Sub

by David McRitchie

Also:
Show FORMULA of another cell in Excel

[Edited entry from 3/12/2005]



See all Topics
excel

Labels: Formulas, Tips, VBA

Only what you want

Contextures.com:
AutoFilter Tips

"Some tips and techniques for working with AutoFilters, and some workarounds for problems you may encounter."

Microsoft Assistance:
All about AutoFilters

Jay Walkenbach:
Displaying AutoFilter criteria

The University of North Carolina at Charlotte:
Using Custom Views with Autofilter

"If you frequently use Autofilter to view portions of your worksheets, you might find Custom Views to be a useful tool. Custom Views can be easily set up based on your Autofilter criteria. Once that is done, the worksheet views that you have created display in a drop down list so you (or others) can select them."

Labels: Formulas, Tips

Formatting slip

The formula is filled down the column as long as there is data in an adjacent column.

But wait! I don't see the value. I see the formulas!
In addition, the formulas are still in lower case and the relative references have not been updated.

Easy to fix, I hear you say. Just reformat the column as General.

Nothing happens.

To fix the problem, make sure the column is formatted as General.

Select the first cell. Click in the formula bar and hit the Enter key.

Now double click the Fill handle.

(You could also use Edit>Replace to replace = with =. However, Relative references will be incorrect and unless you have reformatted the whole column as General, any new formulas will still display as text.)

The reverse also causes a problem. In a column formatted as General, enter some formulas.

Now reformat the column as Text. The formulas still work, but if you edit one of them, it reverts to a text display.

Microsoft KB:
Cell Linked to Text-Formatted Cell Shows Formula Not Value

Formulas can, of course, be toggled using CTRL+~ (Tilde)
(Though it really should be called CTRL+` (Grave Accent), since the Shift key is not used.)



See all Topics
excel

Labels: Formulas, General, Troubleshooting

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

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

All(most) all you need to know

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

Labels: Formulas, General, 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

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

It's that year again

One Month and One Year perpetual calendars are available.

These Excel file calendars do not use macros and can be used in OpenOffice or other Excel compatible software.

• Perpetual calendars with Week starting on Sunday
  
  
• Perpetual calendars with Week starting on Monday
  
  
• A simple "universal" one month calendar that will update for any month and any year (after 1900) just by changing a date cell.
  
  
• A "universal" one year calendar that will show 12 months starting with a user defined Month and Year.
  Start dates can at the beginning of any year and any month.

Microsoft:
How to make calendars



See all Topics
excel

Labels: Formulas, Templates

If 4, show kumquat

Labels: Formulas

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