Section 1 of 3 - Prev - Next All sections - 1 - 2 - 3
Archive-name: spreadsheets/faq
Author: casfaq@locutus.ofB.ORG (Russell Schulz)
Comp-apps-spreadsheets-archive-name: faq
Posting-Frequency: biweekly
Last-modified: 2002-Mar-28
Posting-Via: news.demon.net (mail2news)
Not-Posting-Via: my connectivity provider who doesn't do news for uucp now
Not-Posting-Via-The-Cable-Modem-Because: I don't want to
comp.apps.spreadsheets == cas
Frequently Asked Questions == FAQ
The FAQ list for comp.apps.spreadsheets can be found on the Internet:
========================================================================
Contents:
1. Disclaimer
2. Spam
3. Can you answer my spreadsheet question?
4. How can I add a FAQ and its answer to the FAQ list?
5. Should I post a spreadsheet utility I just created/found?
6. Where can I learn about spreadsheets?
7. General spreadsheet issues
8. For the masses: Lotus 1-2-3
9. For the masses: Quattro
10. For the masses: Excel
11. Spreadsheet runtimes/compilers
12. Optimizers (Solvers)
13. Errors, Checkers, Auditors
14. Obscure: File formats
15. Obscure: Free spreadsheets, and source code
16. Obscure: Commercial spreadsheets for UNIX
17. Obscure: Legal issues
18. Miscellaneous
99. Credits
========================================================================
1. Disclaimer
Read at your own risk. The current, previous, or original authors
make no claim as to fitness for any purpose or absence of any errors,
and offer no warranty. Do not eat.
========================================================================
2. Spam
you wouldn't believe how much spam I get to this address.
========================================================================
3. Can you answer my spreadsheet question?
Probably not. Please don't mail it to me. There is no reason to assume
I, personally, have ever even USED the spreadsheet you're wondering about.
Read the manuals, and the FAQ, and the materials pointed to by it. If
you can't find an answer there, by all means post to the newsgroup.
If you need help posting, see among others.
A FAQ list is intended to reduce traffic on a newsgroup, not eliminate it.
The newsgroup readers can be very helpful. Don't be shy in asking
questions. But do make sure that you:
- show you've put in effort beforehand (including reading the manual
for your software), and aren't just being lazy and abusing the
newsgroup participants
- give enough details -- some people don't even mention what
spreadsheet they're using!
- use a descriptive Subject: header (not just `Excel help' or
`Two Lotus Questions' or `Quattro problem' or `Formula needed'
or, probably worst of all, `Help me!!!!!!1')
- clearly ask your question. your message will be travelling
to thousands of computers around the world, so it's courteous
to spend a few minutes reading it over and making it perfect.
- give an example if you have a complex formula request (sometimes
a small example will say more than a lot of text. if you want a
particular result from 5 numbers, give the numbers and the result
you'd expect. or give 3 sets of 5 numbers and 3 results.)
- don't post a large attachment (these are almost ALWAYS a waste of
space, which is very rude to people whose net access is expensive;
a small example is good; a large attachment, including the example
and an extra 100k of data and file format overhead, is bad.)
These observations and suggestions hold for most newsgroups.
My personal interest is in obscure, technical and/or historical trivia,
like source code, free spreadsheets, and file formats, but not with how
to do things which are documented in the manuals. I assume that future
maintainers will rectify this imbalance.
In the mean time, the whole of section 6 lists pointers to other places
you can look which will probably have what you want.
========================================================================
4. How can I add a FAQ and its answer to the FAQ list?
Mail BOTH of them to me. Then I can add them to the FAQ and it should
help people who have that same question later, as well as everyone who
reads the group, because they won't see it asked and answered so often.
Do not just post the message and hope that I will see it -- I don't
read every message in the group.
I do not work on this FAQ every day, but I will try to get updates
incorporated in a timely manner.
Of course, don't mail me my entire FAQ! I already have a copy! There
are copies available all over the web that I could use if I lost mine!
I pay for my access; don't you?
========================================================================
5. Should I post a spreadsheet utility I just created/found?
Please, no. While it's nice that you want to share, posting files
is not the way to do it. Not everyone will want the file, and for
many people net access is too expensive to waste it this way.
If you have a web page, please put it on the web page, and post an
announcement to the group telling us where it is. Then not only
will people reading the group today have it, but people searching
archives of the group next month will be able to find it, too.
And only people who actually want it will pay for transmitting it.
If you don't have a web page, you may want to use one of the many
free web page services. Or possibly post a `space wanted' message
to the group, asking people who have web pages (e.g., collections
of spreadsheet utilities) if you can send it (once!) to them, and
they will let us know where to find it.
========================================================================
6. Where can I learn about spreadsheets?
More entries are always helpful!
Have any moved? Please let me know.
Here are some places to look on the Internet I've seen suggested:
History of Spreadsheets
http://manuel.lit.tas.edu.au/buss/sssw/history3.htm [error 1999/Feb/14]
http://libwww.stfx.ca/courses/CSCI130-13/lectures/spreadsheets/intro.html
http://dss.cba.uni.edu/dss/sshistory.html
http://www.un.org/Depts/unsd/softproj/papers/nidi_pg.htm
http://www.as.edu.au/www/CompSt/GenHSC1.html [error 1999/Apr/10]
John Walkenbach's "The Spreadsheet Page"
http://www.j-walk.com/ss/
it's more than a page -- it's a large site with information on
many spreadsheets and spreadsheet-related material, including
his own advanced books
IUPUI's FAQs for many spreadsheets
http://www-lib.iupui.edu/toolbox/spreadsheets.shtml [no more FAQs?!]
Microsoft Excel 97 and VBA
http://www.100folhas.pt/software/excel.html [error 1998/Sep/08]
Rick Desrosiers
http://homepages.together.net/~rickd/
Writing Excel Macros, by Steven Roman
http://www.oreilly.com/catalog/exlmacro/noframes.html
spreadsheets in mathematics, statistics, and science education
http://sunsite.univie.ac.at/Spreadsite/
spreadsheets and statistics
http://www.man.deakin.edu.au/rodneyc/
spreadsheets and differential equations
http://www.sci.usq.edu.au/staff/robertsa/home.html
(look for spreadsheet.sea.hqx)
Richard Warr's spreadsheet page
http://www.warr.cc/
Excel (in German)
http://www.vwa.ba-stuttgart.de/~mballweg/Excel/inhalt.htm
[error 1998/Dec/01]
Excel (in German)
http://www.herber.de/ [frames-only! boo hiss!]
http://www.herber.de/main_left.htm
http://www.herber.de/links.htm
Excel (in Russian)
http://www.fido.nnov.ru/win/Excel/FAQ.html
Excel utilities
http://www.fido.nnov.ru/win/Excel/AmountToText.html
The Byg Utilities Lite (including Number to Text)
http://www.bygsoftware.com/bygutils/bu_lite.htm
University of British Columbia Excel Tips
http://www.science.ubc.ca/~geol302/excel/
Frank Isaacs' VBA Pro
http://www.vbapro.com/
Arun Ubale's spreadsheet written in Java
http://sunrise.cs.olemiss.edu/~cmubale/ [error 1998/Sep/08]
http://www.cs.olemiss.edu/techrpt/tr1996.html
Spreadsheet written in Java
http://java.sun.com/applets/jdk/1.1/demo/SpreadSheet/SpreadSheet.java
Christopher B. Browne's spreadsheet history and Linux implementations
http://www.hex.net/~cbbrowne/spreadsheets.html
Linux spreadsheets
http://www.linuxlinks.com/Software/Spreadsheets/
Linux Software (including spreadsheets)
http://members.aa.net/~swear/pedia/science-and-math.html
Excel page with lots of examples; also, finding and removing links
http://www.bmsltd.co.uk/
Joseph Moosman's Excel (including permutations and combinations)
http://www.geocities.com/SiliconValley/7043/
Excel and VBA examples
http://www.mindspring.com/~tflynn/excelvba.html
Alan's Excel pages
http://www.barasch.com/excel/
Chip's Excel pages
http://home.gvi.net/~cpearson/excel.htm
[error 2000/Jan/29]
Laurent Longre's XLLs
http://longre.free.fr/
http://longre.free.fr/english/
NerdWorld Spreadsheet Links
http://www.nerdworld.com/nw547.html
MIT Excel User Group
http://web.mit.edu/xlug/
Excel Companion -- introduction to mathematical modeling
http://faculty.babson.edu/aieta/exclcmpn/
Byte accolades for Improv (an MS-Windows port of the NeXT product)
http://www.byte.com/art/9401/sec6/art1.htm
Ture Magnusson Excel Pages
http://www.turedate.se/excel/
http://www.turedate.se/excel/menu.htm
[error 2000/Jan/29]
Sam L. Savage -- Spreadsheets in analytical modeling
http://www.stanford.edu/~savage/
http://lionhrtpub.com/orms/orms-2-97/savage.html
Use of spreadsheets to analyze political data
http://ps.polisci.wisc.edu/~kritzer/ps551/syllabus.htm
[error 2000/Jan/29]
Tutorial on adding custom functions to Excel;
includes least-squares fitting for data with uncertainty
http://wham.ph.utexas.edu/ModPhy/DataAnalysis/ExcelFunctions/ExcelFunctions.html
[error 2001/Apr/18]
Generalized Linear Models on a spreadsheet
http://www.StatisticalEngineering.com/glm.htm
Inside Microsoft Excel (formerly the Cobb Group, Ziff Davis)
http://www.elementkjournals.com/ime/
Village Software -- commercial spreadsheets for Excel, Lotus, Quattro
http://www.villagesoft.com/
John Lacher -- spreadsheet application development
http://www.lacher.com/
Baarns Publishing
http://www.baarns.com/
http://archive.baarns.com/DevOnly/pages/maindfaq.asp
http://archive.baarns.com/Office97/pages/main97xl.asp
http://archive.baarns.com/Excel/free/excelexp.asp
http://archive.baarns.com/pages/faqgen.asp
PC World spreadsheets
http://www.pcworld.com/hereshow/
http://www.pcworld.com/hereshow/browse.asp?cat=212
http://www.pcworld.com/resource/printable/article/0,aid,15489,00.asp
Spreadsheet Shareware Utility CD
http://www.emsys.com/ssutil.htm
[error 2001/Apr/18]
Excel Solutions Letter (with VBA tips)
http://www.beyondtechnology.com/ [totally frames-dependent! boo hiss! ]
http://www.beyondtechnology.com/tips.htm
OzGrid
Excel (and VBA) training, spreadsheets, add-ins, tips and tricks
http://www.ozgrid.com/
c|net
http://www.help.com/cgi-perl/search.pl?query=spreadsheets
Search Engines
Official product web pages:
Lotus
http://www.lotus.com/
[yay! they fixed their no-frames brokenness!]
Lotus information
ftp://ftp.support.lotus.com/pub/desktop/
... Improv/technotes/next/technotes.zip
... Improv/technotes/windows/technotes.zip
... Symphony/technotes/standalone/technotes.zip
... Symphony/technotes/server_edition/technotes.zip
... SmartSuite/technotes/windows/standalone/technotes.zip
... SmartSuite/technotes/windows/server_edition/technotes.zip
... 123/technotes/dos/standalone/technotes.zip
... 123/technotes/dos/server_edition/technotes.zip
... 123/technotes/windows/server_edition/technotes.zip
... 123/technotes/windows/standalone/technotes.zip
Lotus 123 user discussion area
http://www.lotus.com/free123.nsf?opendatabase
http://www.lotus.com/maindisc.nsf/123byDate
Quattro
http://www.corel.ca/products/wordperfect/cqp/index.htm
http://www.corel.ca/products/wordperfect/cqp7/index.htm
http://www.corel.ca/products/wordperfect/cqp8/index.htm
http://www.corel.ca/products/wordperfect/cqp/manual/
http://www.corel.ca/products/wordperfect/cwps8/macros/wptoc.htm
http://www.corel.ca/products/wordperfect/cqp8/support.htm
http://www.corel.ca/support/
Microsoft Excel
http://www.microsoft.com/office/excel/
http://www.microsoft.com/officedev/articles/Opg/toc/pgtoc.htm (VBA guide)
http://www.microsoft.com/office/ork/ (Office Resource Kit)
http://support.microsoft.com/support/excel/content/faq/
Microsoft Knowledge Base
http://support.microsoft.com/support/search/
[back to being accessible without cookies! finally!]
ftp://ftp.microsoft.com/deskapps/excel/kb/
ftp://ftp.microsoft.com/deskapps/excel/kb/index.txt
[not searchable except through index]
Wingz
http://wingz-us.com/
http://wingz.iisckc.com/ [error 2000/Jan/29]
http://www.iisc.co.uk/wingz/
Xess
http://www.ais.com/
http://www.ais.com/faq.html
http://www.ais.com/xess4-help.html
NExS
http://www.nexs.com/
http://www.nexs.com/faq.html
http://www.nexs.com/manuals.html
VistaSource Applixware and Anyware
http://www.vistasource.com/
http://www.vistasource.com/products/anyware/spreadsheets
http://www.vistasource.com/services/support/FAQ
Star Office (includes spreadsheet)
http://www.stardiv.de/
http://www.stardivision.com/
http://www.stardivision.com/body.html
ftp://ftp.gwdg.de/pub/linux/staroffice/
MacroCALC
http://www.freakout.de/#mc
XQuad (Axene Office)
http://www.axene.com/english/xquad.html
Spreadsheet 2000 (formerly Let's Keep It Simple)
http://www.emer.com/s2k/ [error 2001/Apr/18]
http://www.casadyg.com/ [error 2001/Apr/18]
Improv
ftp://ftp.support.lotus.com/pub/desktop/Improv/index.html
Mesa
http://www.plsys.co.uk/products/mesa/
http://www.sundialsystems.com/mesa/
Quantrix
http://www.lighthouse.com/ProductInfo/Quantrix.html [error 1997/Jun/01]
http://www.lighthouse.com/ftp.html [error 1999/Apr/10]
ftp://ftp.lighthouse.com/pub/products/Quantrix/ [error 1999/Apr/10]
ParaSheet
http://www.lighthouse.com/ProductInfo/ParaSheet.html [error 1997/Jun/01]
http://www.lighthouse.com/ftp.html [error 1999/Apr/10]
ftp://ftp.lighthouse.com/pub/products/ParaSheet/ [error 1999/Apr/10]
As Easy As and Alite
http://www.triusinc.com/
GS-Calc
http://www.jps-development.com/gs-calc.htm
Lucid 3-D
http://www.lucidcorp.com/products/l3ddos_text.html [error 2001/Apr/18]
http://www.lucidcorp.com/products/l3dw_text.html [error 2001/Apr/18]
Sum*It (for Mac and Be)
http://www.hekkelman.com/
http://www.hekkelman.com/SumIt.html
Sphygmic Spreadsheet
http://www.sphygmic.com/sssheet.htm
Advance by APS
http://www.lighten.com/compare/improv_index.html [error 2001/Apr/18]
@Risk
http://www.palisade.com/
http://www.palisade.com/html/risk.html
KMT Software; developers of templates, add-ons and developer tools
http://www.kmt.com/
XRT/table widgets
http://www.klg.com/software/xrt/xrttable.html
JClass LiveTable
http://www.klg.com/software/jclass/jclasslivetable.html
xlSTAT (a data analysis toolbox for Excel)
http://www.xlstat.com/
Bob Flanagan's `Microsoft Visual Basic Macros Made Easy' online book
http://www.add-ins.com/vbhelp.htm
[error 2000/Jan/29]
Bob Flanagan's `Home of Great Excel Add-ins'
http://www.add-ins.com/
Interval Solver for Microsoft Excel
http://www.delisoft.fi/ExcelProducts/IntervalSolver/
[error 2001/Apr/18]
OMDI Add-ins for Microsoft Excel
http://www.octavian.com/scieng.html
http://www.octavian.com/mathdesc.html Linear Programming
http://www.octavian.com/omdiimag.html Image Tools
http://www.octavian.com/descunit.html Unit Tools
http://www.octavian.com/descelem.html Element Tools
http://www.octavian.com/descpure.html Pure Component Tools
Analyse-it statistics add-in for Microsoft Excel
http://www.analyse-it.com/
Concept Data
http://www.conceptdata.com/
FarPoint Spread (VBX)
http://www.fpoint.com/
http://www.fpoint.com/products/spread/spread.html
Forms Processing Engine (JavaScript spreadsheet)
http://www.c-sw.com/
Athena's Integer Spreadsheet in Java
http://www.integer.athena.com/
[error 2001/Apr/18]
ExcelGrid for Delphi
http://www.topsoft.com/main/2025/14912663.asp
Enhance23 add-in for Lotus123
http://www.lapsoft.com/
DPS Small Business Solutions -- examples in Excel, 123, MS Works, ...
http://songdavis.hypermart.net
Other:
Mailing list: Excel General
EXCEL-G
for information, send mail with the line
INFO EXCEL-G
to LISTSERV@PEACH.EASE.LSOFT.COM
to subscribe to the list, send mail with the line
SUB EXCEL-G user@domain.name Firstname Lastname
to LISTSERV@PEACH.EASE.LSOFT.COM
Mailing list: Excel Developer
EXCEL-L
for information, send mail with the line
INFO EXCEL-L
to LISTSERV@PEACH.EASE.LSOFT.COM
to subscribe to the list, send mail with the line
SUB EXCEL-L user@domain.name Firstname Lastname
to LISTSERV@PEACH.EASE.LSOFT.COM
Newsgroups: Quattro
access via NNTP at news://cnews.corel.ca
note that this is not a legitimate RFC 1036bis name, so
it may not work with your particular newsreader
corel.support.wordperfect8suite.quattropro
Newsgroups: Microsoft Excel
access via NNTP at news://msnews.microsoft.com
note that many of these are not legitimate RFC 1036bis names, so
they may not work with your particular newsreader
microsoft.public.excel.123quattro
Support for converting 1-2-3 or Quattro sheets
into Microsoft Excel sheets.
microsoft.public.excel.charting
Support for building charts with Microsoft Excel
microsoft.public.excel.crashesGPFs
Post here for help with crashes or General Protection Faults.
microsoft.public.excel.datamap
Support for the Data Map feature
microsoft.public.excel.interopoledde
Assistance with OLE - DDE and other cross application issues.
microsoft.public.excel.links
Support for using links in Excel.
microsoft.public.excel.macintosh
Support for Excel issues on the Macintosh Operating System Platform
microsoft.public.excel.misc
Post general questions or questions that do not fit
one of the other categores.
microsoft.public.excel.printing
Support for printing spreadsheets with Microsoft Excel
microsoft.public.excel.programming
Programming Excel using VBA or XLM.
microsoft.public.excel.queryDAO
Support of the Microsoft Query Tool and
using Data Access Objects (DAO) in Excel.
microsoft.public.excel.setup
Setup and Installing Microsoft Excel.
microsoft.public.excel.templates
Assistance with the Village templates and other XLT files.
microsoft.public.excel.worksheetfunctions
Support for Worksheet functions only like =Lookup(), or =Linest().
microsoft.public.de.excel
Deutsche Newsgroup fuer EXCEL
========================================================================
7. General spreadsheet issues
7.0 Introductions
http://www.drake.edu/artsci/econ/study/spread.html
[error 2000/Jan/29]
7.1 Why does my spreadsheet say 0.1+0.1+0.1+0.1+0.1-0.5 isn't 0?
if any computer package uses binary floating point numbers (and
the vast majority do), then it can not represent the value 1/10
exactly (just like it can't represent the value 1/3 exactly).
so, in each 0.1 there's a little roundoff error, and when you get
enough of those errors together, you can sometimes see them.
this is why you cannot reliably use simple equality tests on a
binary-arithmetic computer (including in a spreadsheet) unless it
is designed to represent such quantities exactly (for instance,
by using integer hundredths, or using Binary Coded Decimal form).
so, instead of testing for equality like
=IF(Credits=Debits)
you have to do it something like
=IF(ABS(Credits-Debits)<0.0000001)
because the first method MAY FAIL when you encounter this type
of roundoff error. the numbers will _look_ the same on the
screen, but they won't _be_ the same, when examined to the full
precision the computer has used.
frustratingly, Microsoft Excel _does_ store values as integer
hundredths to make the files smaller, but it does _not_ use these
precise values in its calculations, as that perfect accuracy
would make it slower!
if you're using dollars and cents, testing if the values agree
to within 1 cent (0.01) usually makes more sense than 0.0000001 .
even though later versions of Microsoft Excel do have an option
`use values as displayed', it does not wholly work around this
problem, as the calculations are still done internally in binary.
however, the final test against `0' _could_ work, if placed in
separate cells: the displayed value for 0.1+0.1+0.1+0.1+0.1-0.5
(when forced to show at most 2 decimal places) is exactly 0.
but if you were testing `0.1+0.1-0.1' against `0.1', all bets
are off -- it may work, or it may not, depending on how careful
the different parts of the spreadsheet are in being consistent
with rounding to impossible-to-store-exactly values like `0.1'.
7.2 How can I round to the nearest multiple of 3? or the nearest nickel?
if you have a ROUND() function, simply divide by your grouping,
round your value, and multiply by your grouping.
e.g., to round to the nearest use
3 ROUND(value/3)*3
0.05 ROUND(value/0.05)*0.05
0.05 ROUND(value*20)/20 [equivalent]
these results will still be subject to computer accuracy -- if you
round 0.15 to the nearest 0.3, you might get 0 or you might get 0.3
or you might get 0.29999999999.
7.3 How can I save-as HTML, or otherwise view foreign spreadsheets?
7.3.1 built-in save-as HTML
some spreadsheets have this built-in now. contact your vendor
to see if there is a patch or add-on which will work for you.
7.3.2 dbQuickPage (HTML)
dbQuickPage supports the following [...]
Lotus 1-2-3 wk1, wk3, wk4
Excel 3.0, 4.0, 5.0, 8.0
http://www.hallogram.com/dbquickpage/
7.3.3 XTML (Excel to HTML)
http://members.aol.com/ksayward/XTML/
7.3.4 perl source code
(source code!)
(doesn't require MS-Windows!)
(free!)
7.3.4.1 Herbert, a free platform independent MS Excel to HTML convertress
http://user.cs.tu-berlin.de/~schwartz/perl/
7.3.4.2 Spreadsheet::ParseExcel
http://www.perl.com/CPAN-local//modules/by-module/Spreadsheet/
7.3.5 Quick View
http://www.inso.com/qvp/index.htm
[error 2001/Apr/18]
add-in for Netscape Navigator
views many spreadsheet file types
7.3.6 Corel Quattro Viewer Plugin
http://www.corel.com/products/wordperfect/cwps8/plugin.htm
7.3.7 Drag and View
http://www.canyonsw.com/dnv.htm
7.3.8 ReadWk
ftp://ftp.simtel.net/pub/simtelnet/msdos/spredsht/readw308.zip
WKS,WK1
7.3.9 QView
ftp://ftp.simtel.net/pub/simtelnet/msdos/spredsht/qtview11.zip
WKQ,WQ1,WQ2,WB1,WKS,WK1,WRK,WR1
7.3.10 xl2html
http://www710.gsfc.nasa.gov/704/dgd/xl2html.xls [hard to reach]
http://www.gwdg.de/samba/winapps/xl2html.zip [unofficial mirror]
7.4 How many columns or rows does my spreadsheet allow?
look in your manual! why do so many people post this question
world-wide?
7.4.1 Can I get more columns or rows than that?
no -- did you think the manual was joking?
a. perhaps there is another spreadsheet package which has
enough columns or rows for your needs.
b. if you cannot switch packages, try to rotate the data
(since most spreadsheets allow many more rows than columns).
c. if none of these work, split the data over multiple sheets,
if your package has this capability.
7.5 Spreadsheets in Science and Engineering
7.5.1 Science and Engineering
book/CD:
Spreadsheets in Science and Engineering ed. W. G. Filby
ISBN 3-540-61253-X
7.5.2 Chemistry
book:
Excel for Chemists: A Comprehensive Guide, by Dr. E. Joseph Billo
(Wiley-VCH, 1997, 480 pp, $49.95)
7.5.3 MoreVision Layout
http://www.morevision.com/
7.6 How do I deal with dates and times in my spreadsheet?
this depends on your spreadsheet.
the most common method is to ignore dates entirely -- just
represent the dates as numbers, and use them like any other numbers.
in particular, every number is treated as the number of whole
days starting with some particular date long ago (it is called
the epoch, and usually has a value of 1). so a value of 2 would
be the day after the epoch, 3.5 would be noon (12 hours=0.5 days)
2 days after the epoch, and 12.25 would be 6 am (6 hours=0.25 days)
11 days after the epoch.
(Unix keeps its time in a similar way, except it only uses integers;
it counts the number of seconds since the start of 1970. this is
why many Unix systems are in danger of severe clock problems in 2038 --
the signed 32-bit counter will overflow.)
when you format the numbers as dates or times, a lot of things just
magically work. for instance, you can add 7 to a date, and it will
always be a week later, even if it wrapped around a month or year or
century -- it's the formatting that does all the hard work of deciding
what date (and time) a given number actually represents.
there are two big problems:
1. there was no February 29 in 1900:
by the 4/100/400 rule, 1900 was not a leap year (and 2000 was).
but for backward compatibility with an error in an early
spreadsheet that used the epoch of 1900/Jan/01, many spreadsheets
treat 1900 as if it was (and claim that `60' should be treated as
the nonexistent 1900/Feb/29). this means any date calculations
that use dates before 1900/Mar/01 are suspect.
2. the epoch can change:
perhaps to avoid that problem, the Macintosh system has used
an epoch of 1904/Jan/01. but since the dates are stored only
as numbers, you can suddenly find your dates shifting by about
4 years when opening spreadsheets on different computers
(notably, Microsoft Excel allows you to switch between the
`1900 date system' and the `1904 date system' interpretations
on the same computer).
there are 3 small problems:
1. the difference between two dates can't be formatted as a date:
if you want to show the difference between two dates (e.g., age)
you simply subtract the two numbers -- but do NOT format them
as dates (since a difference of 3 days might show up as the
confusing 1900/Jan/04). always treat (and format) the difference
as a number of days, possibly dividing by
7 to show the number of weeks;
365[.2422...] to show an approximate number of years;
30[.416...] to show an approximate number of months.
2. times over 24 hours may be hard to format:
if you add 18 hours (0.75) and 12 hours (0.5), you may not get
the 30 hours (1.25) you expect, but 1 day with 6 hours (0.25)
left over. you may need to format the result as a number, in days,
or multiply by 24 to show the time in hours. many newer versions
have a special `overflow' format to treat a number as time only,
without using the integer part as a day count.
3. two digits for the year were never enough (past 1900, anyway):
if you enter data with 2-digit years, shame on you. but if you
insist, your software may be trying to outsmart you -- some packages
treat any two-digit year from 00-29 as 2000-2029. if this happens,
and you format the date with a 2-digit year (shame on you!), you may
never notice this switch until the day you accidentally discover that
your `average age' calculation is just a little too low, because of a
few negative ages you used!
========================================================================
8. For the masses: Lotus 1-2-3
8.0 1-2-3 history
[
I've been mailed a as-I-remember-it history, but I'd love to have
an official one to point to here
]
8.1 What file extensions do different versions use?
WKS : release 1A
WK1 : release 2
FMT : format of release 2 (wysiwyg)
WK3 : release 3
FM3 : format of release 3 (wysiwyg)
WK4 : release 4 and 5 (includes format (wysiwyg))
123 : release 97
8.2 How can I recover a corrupted file?
see http://www.geocities.com/CapitolHill/5186/ [removed? 1998/Jan/23]
8.3 Opening Quattro Files
see ftp://ftp.support.lotus.com/pub/desktop/123/win3x/utils/qpwtrans.exe
========================================================================
9. For the masses: Quattro
9.0 Quattro history
1-2-3 was the dominant spreadsheet on PCs for a long, long time.
`quattro' is similar to the Latin and Spanish words for `4'. Get it?
According to the 1994 Computer Bowl, the code name for Quattro was
`Buddha', since it was intended to assume the Lotus position.
9.1 Quattro file extensions
Felix Miata wrote:
> All versions of Quattro Pro I have used can read and write the
> Lotus WKS and WK1 file formats. A file saved as both a WK1 file
> and a WQ1 file results in the WQ1 file being the larger. Maximum
> cell contents length in the Lotus formats is 240 characters. For
> the QPro formats I have used (WQ1 & WQ2) the limit is 255
> characters. The special WQ! compressed format available to QPro
> DOS users reduces the contents length to a 245 character maximum.
> So, something is obviously different about the formats.
> The original Quattro used the WKQ format. Quattro Pro introduced
> the WQ1 format. The WQ2 format was introduced to offer multipage
> spreadsheet "notebooks" with version 5.0. The Windoze version
> readable by the DOS program is WB1.
========================================================================
10. For the masses: Excel
10.0 Excel history
http://www.microsoft.com/msexcel/productinfo/macintosh/xlhistry.htm
[error 1999/Apr/10]
http://support.microsoft.com/support/kb/articles/q66/9/13.asp
ftp://ftp.microsoft.com/deskapps/excel/kb/q66/9/13.txt
Section 1 of 3 - Prev - Next All sections - 1 - 2 - 3
