--==>>OFFICE FOR MERE MORTALS <<==--
Creative Certificates with Word - our new ebook
http://shop.office-watch.com/cert - OUT NOW!
26 September 2006 Vol 7 No 21
>>> Recover damaged Microsoft Works files at http://ref.OfficeRecovery.com/works/?wmf <<<
Got unreadable Works document, spreadsheet or database? Try Recovery for Works! Three great utilities available bot
h standalone and as a convenient suite. Easy to set up and use. Free demo available.
*** Click http://ref.OfficeRecovery.com/works/?wmf ***
1. Random Numbers in Excel
2. Creative Certificates with Word
3. Rand()
4. Ok... We Really Mean "Pseudo-Random" Numbers
5. Building on The rand() function
6. Whole Numbers only
7. RandBetween()
8. The #Name Error and Fix
9. Negative Random Numbers in Excel
10. Keep Mere Mortals Alive and Free
14 GREAT OUTLOOK ADD-INS FROM RSOUTLOOK.COM
Best selling add-ins include: Delete Duplicated Email/Contacts; Hide Fax Numbers; Reminders by E-mail; Reminders in
Anniversary; Rsoutlook Disclaimer
Most are $10. Free updates. 100% money-back guarantee.
Tap here and get Remind Me Attachments add-in absolutely free! Code OFF001
http://www.rsoutlook.com/us/prods/prod06.html
______
1. Random Numbers in Excel
In a previous edition of Office for Mere Mortals Ina previous edition of Office for Mere Mortals http://www.office-watch.com/wowmm/archtemplate.asp?v7-n19, we looke
d at what the =rand() function does in Microsoft Word and briefly touched on its behavior in Excel. , we looked at what the =rand() function does in Microsoft Word and briefly touched on its behavior in Excel.
Following that issue we had a surprising number of people asking about random numbers in Excel ... and we're happy to oblige ...
2. CREATIVE CERTIFICATES WITH WORD
Now Available!
Peter Deegan and Maryjane Almer are delighted to announce the release of their latest ebook 'Creative Certificates with Word' with easy step-by-step instructions, examples ready for to use and low price.
You've got Microsoft Word already, we'll show you how to use it to create gorgeous certificates for home or office just using Microsoft Word. You'vegot Microsoft Word already, we'll show you how to use it to create gorgeous certificates for home or office j
ust using Microsoft Word. http://shop.office-watch.com/cert
* Step-by-step guide to making an effective certificate from blank page to finished product with plenty of color screen shots to guide you.
* You discover some of the tricks that Word experts use to make text and images appear exactly where they should.
* Fonts - suggested combinations of fonts.
* Tips for advanced users. Easy access to common symbols (including many you probably didn't know were there), choosing page orientation, even tips on choosing the paper.
This isn't just another computer how-to book, the tips on using Word is combined with practical design advice from a professional graphic designer, Maryjane Almer.
Premium Edition
You want more? Our Premium Edition has even more example certificates and advice:
* Step-by-step instructions on printing many certificates from a list of recipients (in Excel, Access or other source). How to convert raw data (like a grade) into something that looks good, printing out only some certificates (top students) and sorting.
* Extra examples -- more than 20 extra professionally designed certificates. Word documents ready for you to use.
* More resources - links to sites with Maryjane's personal choices of suitable backgrounds and fonts
* Page borders. A full, easy-to-read list, of all 164 page borders in Word 2003.
Go to http://shop.office-watch.com/cert/fasttrack.asp for the Premium edition.
New! If you'd like to try Google's new Checkout service you can do that buying our new ebook
http://shop.office-watch.com/cert/fasttrack_google.asp
'Creative Certificates with Word' works for Word 97, through Word 2000, Word 2002 (XP) Word 2003 and even Word 2007. The Certificate Gallery of ready-to-use examples are Word documents compatible with Word 97 through Word 2007. The versions of Word also include Microsoft Works packages that include a full version of Microsoft Word.
The first few pages of the ebook, covers and comparison between the two editions are available at Thefirst few pages of the ebook, covers and comparison between the two editions are available at http://shop.office
-watch.com/cert
3. Rand()
Typing the =rand() function into a cell in an Excel spreadsheet will return a random number that is greater than or equal to 0 and strictly less than 1, with up to 9 numbers after the decimal place (e.g. 0.037595172 or 0.999999999).
If you then press F9, a new random number will take the place of the old one. This is because F9 re-calculates the formulas on all open worksheets based on the current data, including all random numbers.
_______
Outlook add-ins save time and help you be more productive:
* New! Mobile Email Redirect - get your Outlook email on your mobile
* Vcard Converter - easily transfer contacts to your iPod and back
* Auto Print - automatically print incoming emails and attachments
* Now with 32 Outlook add-ins
http://www.sperrysoftware.com
_______
4. Ok... We Really Mean "Pseudo-Random" Numbers
The =rand() function in Excel cannot be easily analyzed to produce a predictable pattern, but it is not a truly random process. To be perfectly correct, the numbers generated are in fact approximations of actual random numbers known as "pseudo-random" numbers.
Computers are by definition "deterministic" - when given a particular input, they will always produce the same correct output. This is an inherent problem when it comes to producing a purely random output, and so algorithms have been developed to simulate random behavior.
For people who don't rely on a heavy use of random numbers, the =rand() function in Excel 2003 should be more than enough.
In some heavy-use situations however, it won't. If you are serious about your need for a very good random number generator, then you may want to look at a number of third-party alternatives.
Note that the =rand() function does not try to avoid repeating a number.
With that said, Excel 2003 has an upgraded random number generator over the previous versions of Excel (whose random number generators were simply not sufficiently random).
Naturally, this change introduced a bug that allowed for negative random numbers! We will talk about this bug below.
_______
The Office BACKUP Handbook shows you innovative ways to keep your Office documents safe without spending a fortune. A practical guide with step-by-step guides and plenty of tips from the Office Watch team. Buy now and save $5 TheOffice BACKUP Handbook shows you innovative ways to keep your Office documents safe without spending a fortune.
A practical guide with step-by-step guides and plenty of tips from the Office Watch team. Buy now and save $5 http://shop.office-watch.com/obh/
_______
5. Building on The rand() function
So far we know about random numbers between 0 and 1, but in many cases we want larger random numbers. If you want to increase the range to find numbers between 0 and 50, use the =rand()*50 formula (where the * character represents the multiplication sign). This acts to "scale" the random numbers between a range of 0 and 49.999999999.
What if you want a random number in a certain range - say between 20 and 70? To do this, =rand()*(y-x)+x where x is the smallest number in the range and y is the largest number in the range. In our example, x=20 and y=70.
When you plug in the values to our formula it becomes =rand()*(70-20)+20, which in turn becomes =rand()*(50) + 20. We know from above that =rand()*50 will produce a random number between 0 and 50. If we then add 20 to the result, all we are doing is "shifting" the range of possible numbers by 20. Hence, it produces a random number between 20 and 70 (or 20 and 69.999999999 if you want to be more precise).
6. WHOLE NUMBERS ONLY
If you only want whole numbers (integers), then you can combine the rand() and int() functions to obtain only the integer part of the random number. For example, typing in =int(rand()*100) would generate a random integer between 0 and 99 inclusive.
Similarly if you wanted whole numbers between 1 and 100 inclusive, then simply shift the range by adding 1 as follows: =int(rand()*100) + 1.
To emulate the roll of a dice use =int(rand()*6) + 1
If you want some assurance that your formula is working correctly, copy and paste it into a number of cells and press F9 a number of times to observe that the results are always within the desired range.
_______
Organize Outlook Email - tips from the experts on managing all your email in Outlook. OrganizeOutlook Email - tips from the experts on managing all your email in Outlook. http://shop.office-watch.com/
ooe
_______
7. RandBetween()
Mathematics I hear you say? I don't want to have to know mathematics! After all, it's easy to make a mistake trying to scale and shift your random numbers.
Luckily there is an easier way of creating a random whole-number within a certain range in Excel 2003. This is done by using the =randbetween(bottom,top) function where "bottom" is the smallest integer the function will return, and "top" is the largest integer the function will return. For example, =randbetween(1,100) will produce random integers between 1 and 100 inclusive.
Similarly if you wanted a range of positive and negative values, you could use something like =randbetween(-100,100).
Note: unlike RAND() - RANDBETWEEN() returns integer values only with no fraction. Some documentation is unclear on this point, talking about 'numbers' instead of 'integers'
If you want a random number with fractions in a certain range you need to use RAND() in a formula.
8. The #Name Error and Fix
If you try to test the =randbetween(bottom,top) function in Excel 2003 and it is not available, it will return a #NAME? error.
To fix this you need to install and load the "Analysis ToolPak" add-in, by first navigating to "Tools | Add-Ins" and then selecting the "Analysis ToolPak" box from the list provided. Finally clicking "OK" to install the add-in.
In Excel 2007 this somewhat pointless distinction between functions is removed - any functions from the old 'Analysis Toolpak' are included in Excel 2007 by default.
9. Negative Random Numbers in Excel
As mentioned above, Microsoft's attempt to improve the =rand() function for Excel 2003 was originally flawed as it unwittingly introduced occasional negative random numbers.
Office Watch broke the news of this problem at the time
http://office-watch.com/office2003/archtemplate.asp?1-n11.I love this quote from that issue "The company says
that 'The new generator passes all standard tests of randomness.' which is great but a pity it wasn't tested for plain and simple accuracy." Ouch ... . I love this quote from that issue "The company says that 'The new generator passes all standard tests of randomness.' which is great but a pity it wasn't tested for plain and simple accuracy." Ouch ...
This problem was fixed in the Microsoft Excel 2003 Hotfix Package ( Thisproblem was fixed in the Microsoft Excel 2003 Hotfix Package ( http://support.microsoft.com/kb/833855/) over 2
years ago, so unless you are running an un-patched version of Excel 2003, you can be assured that your random numbers will always be positive. ) over 2 years ago, so unless you are running an un-patched version of Excel 2003, you can be assured that your random numbers will always be positive.
A simple way to test this is to type the =rand() function into one cell, then copy and paste it into a number of other cells until you have a large amount of random numbers. Pressing F9 will "recalculate" all of these random numbers. If you have 50 or so random numbers and you press F9 up to 20 times (quickly checking each time for any negative results), you can easily put your mind at rest.
The only way to resolve the negative random number problem is to obtain the latest service pack for Microsoft Office 2003 using either Microsoft Update or Office Update. This is generally a good idea anyway!
_______
10. KEEP MERE MORTALS ALIVE AND FREE
If you like the no-nonsense style you see in this newsletter get the straight scoop, whether Microsoft likes it or not, dished out in a way that won't put you to sleep with these books and e-books:
OFFICE WATCH guides
"Eye-Catching Signs with Word" http://shop.office-watch.com/esw/
"Office Extras Handbook " http://shop.office-watch.com/oeh/
"The Desktop Search Handbook" http://shop.office-watch.com/dsh/
"Special Edition Using Microsoft Office 2003"
http://office-watch.com/l.asp?0789729555
"Windows XP Timesaving Techniques For Dummies"
http://office-watch.com/l.asp?0764537482
"Windows XP All-In-One Desk Reference For Dummies, 2nd Edition",
http://office-watch.com/l.asp?0764574639
ADMINISTRIVIA
We've started a gradual change from our woodyswatch.com domain to office-watch.com and you'll see links changing over time. If you want to prepare for the future we ask that you add office-watch.com to the 'white' or 'safe senders' list in your anti-spam filter.
ADVERTIZING
You, too, can reach the largest group of influential Office users on the planet for a mere pittance... send a message to Jan mailto:ads@office-watch.com and our ad folks will send you details.
This newsletter happily uses Dundee Internet for all web & list hosting http://www.dundee.net/isp/default.asp
OFFICE for Mere Mortals
Copyright 2006 Office Watch. All rights reserved. ISSN 1443-7252.
|
