Book

Suggestions


Enter your email address:

Delivered by FeedBurner


Home Page

Bloglines

1906
CelebrateStadium
2006

OfficeZealot

Scobleizer

TechRepublic

AskWoody

SpyJournal









Subscribe here
Add to My Yahoo!
This page is powered by Blogger. Isn't yours?

Host your Web site with PureHost!


eXTReMe Tracker
  Web http://www.klippert.com


  Friday, April 18, 2008 – Permalink –

Excuse Me Your Formula's Showing

Formatting slip



Try this experiment. On a sample worksheet, enter some arbitrary data in say the A1:B5 range.
Select the C column and format it as Text.
(Right click choose Format Cells - on the Number tab choose Text)

In cell C1 enter a function, such as =sum(a1:b1)

With C1 still selected, double click the Fill handle
(the tiny box at the lower right corner of the cell.)


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

Labels: , ,


<Doug Klippert@ 7:31 AM

Comments: Post a Comment

  Tuesday, April 08, 2008 – Permalink –

Date and Time Entry

Month Day, Day Month



QDE An Excel Date Entry Add-In
Ron de Bruin

"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

Labels: , , ,


<Doug Klippert@ 6:18 AM

Comments: Post a Comment

  Tuesday, February 19, 2008 – Permalink –

Zero 0

Zero is nothing


If a zero isn't worth anything, why show it?

Here is a Microsoft tutorial about how to deal with zilch:


  • 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


Hide Zeros



See all Topics

Labels: , ,


<Doug Klippert@ 9:22 AM

Comments: Post a Comment

  Sunday, December 23, 2007 – Permalink –

Date Arithmetic

The drunken cousin


Working with dates has a few twists.

Excel believes that time began on January 1, 1900.

Each day since then is counted so that September 1, 2003 in Excel-speak would be → 37,865.
9/1/03 7:33 A.M. is a decimal → 37865.31458333333

When you subtract one date from another, for instance 9/1/2003 (A1)minus 7/4/2001 (A2),
Excel displays the odd answer of → 2/27/1902.

Excel formats the result of a formula with the same format as the source cells,
Right-click the formula cell (=A1-A2).
Select Format Cells ..., and then choose a Number format with zero decimals.

The correct number of days → 789 will now be displayed.

Another way is to use the rarely documented DATEDIF function. Chip Pearson calls it "the drunken cousin of the Function family."

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


MS Knowledge Base:
How To Use Dates and Times in Excel




See all Topics

Labels: , ,


<Doug Klippert@ 7:14 AM

Comments: Post a Comment

  Monday, November 12, 2007 – Permalink –

All the Basics

All(most) all you need to know


Office.Microsoft.com has a short demo that shows you the main things anyone needs to know about Excel.

There are many thousands of users who find that this is all they ever need.
  • Add numbers
  • Subtract numbers
  • Multiply numbers
  • Divide numbers

Use simple formulas to do the math



See all Topics

Labels: , ,


<Doug Klippert@ 8:00 AM

Comments: Post a Comment

  Thursday, August 30, 2007 – Permalink –

Calculate Running Total

Using the OFFSET function


Adding up a running balance can be frustrating when new data is added or old transactions are removed.


"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

Labels: ,


<Doug Klippert@ 8:15 AM

Comments: Post a Comment

  Sunday, June 17, 2007 – Permalink –

Tips and Formulae

Functions and Macros



I'm always looking for Excel sites. A fresh perspective can make the view more clear.
While he does approach from a Mac angle, the Excel world welcomes those of all persuasions.

J.E. McGimpsey's XL Pages

Here are some of the tips:


  • 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




See all Topics

Labels: ,


<Doug Klippert@ 8:07 AM

Comments: Post a Comment

  Wednesday, May 16, 2007 – Permalink –

Calendars - Perpetual

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.


DotXLS.com:
Perpetual calendars


Microsoft:
How to make calendars



See all Topics

Labels: ,


<Doug Klippert@ 6:13 AM

Comments: Post a Comment

  Tuesday, January 23, 2007 – Permalink –

Result is a Picture

If 4, show kumquat


Allen Wyatt has a cool procedure that will let you show a picture of an object on your spreadsheet depending on a value.

Maybe a snow suit when it's 29 or, say, a pair of bloomers when the computed temperature is 70.

The procedure does not use any VBA, just equations and bright thinking.

ExcelTips.VitalNews.com:

Display Images based on a Result



See all Topics

Labels:


<Doug Klippert@ 5:32 AM

Comments: Post a Comment

  Monday, January 15, 2007 – Permalink –

Worksheet Name

Formula construction


There may come a time when you need to display the name of a worksheet.

This formula will do the job:
=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,31)

=CELL("filename",$A$1)
returns the path, the Workbook name and the Worksheet name. (C:\Documents\[April.xls]\Costs)
=MID(text,start_num,num_chars)
selects the text that starts at a certain point and goes on for a certain number of characters.

The formula, as written, looks at the full path and selects the first time a closing bracket (]) is found.

It then moves 1 character to the right and displays the results up to 31 characters.
(A worksheet name cannot be more that 31 characters long.

You could include a reference to that cell on other worksheets.




See all Topics

Labels:


<Doug Klippert@ 8:15 AM

Comments: Post a Comment