Fractions of Seconds

Fractions of Seconds

Over on Swimming Splits, Olly asks:

I am a rowing coach and am putting the data that my crew gives me from their training sessions into a spreadsheet in the mm:ss.00 format. However, if I make a mistake inputing the data e.g type 1:23.03 when it should be 1:23.30 something weird happens.

When I click in the cell, the value rounds up or down depending so 1:23.30 will round to 1:23.00. This will have a massive impact on the results that I am looking at. Is this a common occurrence in excel or is there something that I am doing wrong or should be pressing?

Good question. I'm not sure that I know this answer definitively, but I'll tell you what I think based on what I see.

Dates and times are strange beasts in Excel. I should say that they are a strange beast because they are the same thing. As you probably already know, dates are stored as the number of days from a particular date, usually 31-Dec-1899. That means that 01-Jan-1900 is stored as 1 and 04-March-2007 is stored as 39,145. Similarly, times are stored as fractions of a day. While dates are integers, times of day are the fractions between the integers. Today may be 39,145, but today at 8:00AM is 39145.3333333. It's only 8:00AM and already a third of the day is gone.

This method is great for adding, subtracting, and generally doing other math operations on dates. What it's not good for is understandability by most humans. Excel attempts to bridge that gap by displaying dates and times as dates and times rather than these cryptic numbers. Note that when you enter 0:01.03 in a cell, the formula bar displays 12:00:01 AM. Usually the formula bar will tell you the truth regardless of how you've formatted the cells, but not in the case of dates and times. Like an over-protective mother, it shields you from the harsh realities. The noteworthy aspect of this example is that there are no hundredths in the formula bar. The hundredths are still stored, but they aren't displayed in the formula bar.

If you were to edit the cell, say, by pressing F2 and Enter you lose the hundredths. Pressing Enter is the same as entering 12:00:01 AM into the cell, which contains no hundredths. To change the cell from 3 hundredths to 3 tenths, follow these steps: F2 to edit the cell, backspace three times to remove the AM and the preceding space, type .3, press Enter. I don't know of any way to get the hundredths to display in the formula bar.

It seems rather easy for Microsoft to have done this differently. If a user enters fractions of seconds, display fractions of seconds. Otherwise display it as it is now. There doesn't seem to be a trade-off here that I can see (other than coding time by developers).

Calendar for 178 Years

Calendar_for_178_years
Calendar_for_178_years

Click above to download

World cup Cricket Tracker in Excel

Track the world cup from this Excel Sheet.
It is Unique and Rarely available.


Schedular_cum_Result_Tracker
Schedular_cum_Result tracker

Magic Spread Sheet

magicspreadsheet
Magic SpreadSheet

This Magic Spread Sheet contains following MAGICS:
1)Disappearing the Card
2)Secret Number
3)Magic Eight Ball

Its Amazing.

To convert numbers into words(Rs.)

Numbers+in+Words(Rs)
Numbers+in+Words

This worksheet helps to convert numbers into words.

MS Excel Keyboard Shortcuts

Navigating in Excel Shortcut

Switch between Worksheets CTRL-PageUp/CTRL-PageDown

Switch between Workbooks CTRL-Tab

Move one character up, down, left, or right. Arrow keys

Go to end of a contiguous range CTRL-Arrow Keys

Select a cell range SHIFT+Arrow keys

Highlight a contiguous range SHIFT-CTRL-Arrow Keys

Select entire worksheet CTRL+A

Move to the beginning of the line. HOME

Go To F5

Move a Sheet/Copy a Sheet Alt-E-M

Change Zoom Sizing Alt-V-Z

Entering and editing data Shortcut

Complete a cell entry and select…

...the cell below. ENTER

...the previous cell above. SHIFT+ENTER

...the next cell to the right. TAB

...the previous cell to the left. SHIFT+TAB

Delete cell and then get inside the cell BACKSPACE

Delete cell/selection. DELETE

Edit inside a cell (edit cell mode) F2

Once inside edit cell mode (F2)…

...Start a new line in the same cell. ALT+ENTER

...Highlight individual characters within cells SHIFT+Arrow keys

...Highlight contiguous string within cells SHIFT+CTRL+Arrow keys

...Delete the preceding character. BACKSPACE

...Delete the character to the right of the insertion point. DELETE

...Cancel a cell entry. ESC

Spell Check. F7

Insert a comment. SHIFT+F2

Fill down. CTRL+D

Fill to the right. CTRL+R

Undo the last action. CTRL+Z

Redo the last action. F4 or CTRL+Y

Hiding / Unhiding Rows and Columns Shortcut

Hide the selected rows. CTRL+9

Unhide any hidden rows within the selection. CTRL+SHIFT+( (opening parenthesis)

Hide the selected columns. CTRL+0 (zero)

Unhide any hidden columns within the selection. CTRL+SHIFT+) (closing parenthesis)

Selecting, grouping, inserting, and deleting cells Shortcut

Highlight Entire Row SHIFT+SPACEBAR

Highlight Entire Column CTRL+SPACEBAR

Group Rows or Columns SHIFT+ALT+RIGHT ARROW KEY

Ungroup Rows or Columns SHIFT+ALT+LEFT ARROW KEY

Clear the contents of the selected cells. DELETE

Delete the selected cells. CTRL+MINUS SIGN

Insert blank cells. CTRL+SHIFT+PLUS SIGN

Pivot Table Introduction

XLPivotTableIntro06-07
Pivot Table Introduction

Pivot tables are used to summarize huge data in Excel.
You can rotate rows & column to view different reports.

Download above file to learn Pivot Tables.

Notes for Advance Filter in Excel

AdvancedFilterNotes
Advanced Filter Notes

Complete info on how to use Advance Filter in MS Excel

Excel Keyboard Shortcuts

Excel Keyboard Shortcuts
Excel Keyboard Shortcuts

Excel keyboard Shortcuts to help you work quickly.

Can you solve this puzzle??

puzzle
Puzzle

Click above to download the puzzle.

Its a challenge

VBA Macros ebook

It is interactive ebook which will help you in learning & developing Macros.

Click below to download VBA Macros ebook


VBA Macro ebook
VBA Macro ebook