If you create spreadsheet project with Excel or others spreadsheet application, maybe your users often to make mistake when they're inputing the data into it. Sure, it will make error result and make them irrelevant whereas if the data are in number format.
OK, just give it a prevent formula to make it safer. In this case, I will change the error result into number:0. So, you can to keep the relevant of data.
I combine IF formula and ISERROR formula to prevent the error result. For example if you want divide cell A1 with cell A2 to create percentage between those cells create the formula like this : =IF(ISERROR(A1/A2);0;A1/A2). So, if you input the error character such as a letter; normally, you'll get #VALUE result or another error result normally but if you use that formula you'll get result: 0 so you'll get a normal result if you calculate them.
That formula will detect the result from dividing between cell A1 and A2 and if the result is error so IF function will give you 0 number and if the result is normal so youl'll get result A1 divide to A2. You can look my attached picture to better visualization.
Microsoft Excel has a formula that could help you to capitalize every first letter on your word easily. I use it every I create a article title so that I don't forget to capitalize it or you can use it for another need. So, if you type "i love you" and then you'll get a result like this: "I Love You".
The name of formula is PROPER(). To using PROPER() formula is very easy, just type PROPER(cell_source). For example if you want to capitalize a sentence in cell A1, just create on empty cell: =PROPER(A1) and you'll get the result soon.
Proper can work on NeoOffice Calc too.
If you work with decimal number every day, I sure you often work with rounding numbers too. It happens usually because you want to create results that have a same decimal number format. So, you have to decide it first, you want to round it up or down or automatically by Excel.
In Excel you can round numbers with three functions. The functions are ROUND, ROUNDDOWN and ROUNDUP. To understand the difference between those functions you can see the picture that I’ve attached in this article.
Those functions have the same syntax. Their syntax is: ROUND(number_source_cell;number_digits) and you can apply the same syntax to ROUNDUP and ROUNDDOWN functions. The differences between those functions are ROUNDUP will round up your numbers, ROUNDDOWN will round down your numbers, toward zero and ROUND is similar with ROUNDUP.
You can take time to make some experiments with those functions to increase of understanding to these functions.
Sometimes, spaces are source of troubles on Microsoft Excel or NeoOffice Spreadsheet or another spreadsheet applications. Because if you have an calculate cell which calculate an cell or more, insert spaces accidentally can make some troubles in result cell. For example if you're create a result cell in cell A3 that contains formula to multiply between values in cell A1 and A2, then your user input a value and fill a space in cell A1 accidentally, so your result sheet will display an error and that will confused because space is invisible character.
So to prevent this case, just use TRIM function to remove the unneeded space(s) at left and right text in a cell. To use TRIM function, just type =TRIM(cell). For example, to clean the unwanted space on cell A1, type =TRIM(A1) and you'll get a "clean" text.
With this function you can reduce the error on your spreadsheet project.
If you're creating a project with Microsoft Excel which you don't want your users to input a same value on input area, with a little trick you can prevent it. Just combine COUNTIF function with Validation Data menu.
OK, open Microsoft Excel first then block your input area cells, in my case my input area cells are A1 until A20. So I blocked cells A1:A20. Then going to Data - Validation menu. In Settings tab, choose Custom in Allow combo box then in Formula textbox type =COUNTIF($A:$A;A1)=1. Then click Error Alert and type the message box error title and its content, type your own an error message in there. Click OK button.
Now try to type two same value in your input area.
When you will take a loan from bank maybe you want to calculate the monthly payment by yourself. Unfortunely, that's not an easy. But don't worry about it, Microsoft Excel has a function to calculate it easily.
Use the PMT function. PMT function has a syntax like this =PMT(rate,nper,pv,fv,type).
OK, we will try it now, try make data such as in my picture that I attached in this article. For example type the interest in cell B1, number of periods in months in cell B2, amount of loan in cell B3, type in cell B5 =PMT(B1/12;B2;B3) and then press Enter. Then your payment monthly will displayed in cell B5. The result is in negative value and if you want to make it in positive value just multiply it with -1 number or you can mix it with the formula like this : =PMT(B1/12;B2;B3) * -1.
If you're printing many pages from your word processor application and using a normal print order setting, you must rearrange your print out result later. That's caused computer will order to printer to print page 1 first and so on and the final result is first page in under order and last page in above order. And it's not be a problem if you only print 1-10 pages but how do if you print 50 or 100 pages at once ? I think that's not efficient way if you have to rearrange 50-100 pages. So every word processor (at least which I knew...) has ability to reverse the order printing.
I used NeoOffice Writer for Mac OS X, Microsoft Word XP and Microsoft Word 2007, so I want show you how to modify their configurations to make them reverse the print order.
1. NeoOffice Writer for Mac OS X, open NeoOffice - Preferences menu or you can press Command + , keys. Expands NeoOffice Writer configuration in Preferences window, click Print menu then check the Reversed option at Pages section. Or if you want change the configuration for your current opened document only, you can doing it in Print menu. When you will print it (by click File - Print menu), click Preview button in Print dialog and you'll brought to Printer Options and you can do the same way that I've explained above.
2. Microsoft Word XP/2002, click Tools - Options menu then click Print tab then check the Reverse print order option (in Printing Options section). Or you can also click Options... button in Print dialog when you're ready to print your document and you'll bring to Printing Options then check Reverse print order option in there.
3. Microsoft Word 2007, click Word button then click Word Options button, click Advanced tab then looking for Print section then check the Print pages in reserve order option, at last click OK button to save the Options changed. Or you can click Option.. button in Print dialog then doing the same way.
With that option you document will be printed from lastest page first and the first page will printed at last, so you'll get it with sequence order from first page to last page and it will save your times.
If you're often using Microsoft PowerPoint and share its document file, maybe you're often get complain that they got missing fonts or your layout be destroyed because the require font doesn't found in their computer. This happened caused your font that using by PowerPoint doesn't exist or installed in their computer. To prevent it just embed your used fonts in document file.
To embed your font into PowerPoint document, it's so easy. In Save dialog, click Tools menu/button then choose Save Options menu. In Save Options dialog and then check Embed TrueType fonts radiocheck and don't forget to click OK to save your options. And you'll brought to Save dialog back then just type your file name and click Save button to save your PowerPoint document.
Now, you don't need to worried to share your PowerPoint file again, although your fonts are not installed in your client/friend computer.
If you work with Microsoft Excel everyday, ensure, block or highlight rows and columns is your daily job. To block them usually, we'll click header column or row to block all of them. I want to share another way to block entire rows or columns.
Ok, I consider your Microsoft Excel has opened. I have 3 tricks to block your cells.
1. To block entire row, such as if your cursor at B1 cell and you want to block entire row 1, press Shift + Space keys.
2. To block entire column, sucs as if your cursor at B1 cell and you want to block entire column B, press Ctrl + Space keys.
3. To blok entire cell in your sheet, press Ctrl + Shift + Space keys.
I don't try it yet with another spreadsheet applications but I think at least 1 from 3 tricks above can apply on them too.
Try it .....
I am in the mood to write articles today, this is my 3rd articles today. I want write about how to combine 2 cells or more in spreadsheet application (Microsoft Excel, NeoOffice, OpenOffice). Yes, I know for Excel expert this's a basic lesson but on other hand, maybe you don't know yet about this.
OK for combining cell, create a formula like this : =A1 & " " & B2 & C2. Change the cells with yours. You can see at the pictures that I've included in this article. I want to create a result : I've visited 466 times a month. So you can put an updated value in a cell (in my example, I've put it on cell B2 for value : 466 because this number will updated everyday) and I put the result in cell B4 and I've write : ="I've visited " & B1 & " times " & B2 for the formula. Press Enter key to execute the formula, and you'll get your result.
Hi, this morning I suddenly want to write this topic. Yesterday, I make a report from existing report with Neo Office Spreadsheet that contains so many negative numbers inside it. And my new report will copy from original report but to positive number. Maybe this is not a trick, just a conventional way but maybe you don't know it yet.
Yup, if you want to make a positive number from negative number you can multiply it with -1, so you'd know if negative multiply with negative, it'll change to positive number. For example if you original number (negative one) in A1 cell that you fill B1 with -1 and in C1 contain formula : A1 * B1 so C1 will contains positive number, but this way is pretty annoying because you must multiply it column by column.
So, yesterday I think with feature in Paste Special dialog. Now, try with this way, type in A column with few negative numbers, then in B1 column (or in any column that you want) type -1 value, then copy B1 cell, then block all your negative numbers then right click to show context menu and choose Paste Special menu and in Paste Special dialog click Multiply option under Operations section then click OK button. And viola..... your negative numbers change to positive number. I think this way is shorter than first way.
I've try it with Neo Office but you can do the same action with Microsoft Excel and Open Office Spreadsheet application.
If you're have job to creating data and want make it be more representative, usually we add it with chart or image but sometimes chart will make our excel be slower, but Microsoft had been give us a great function to make simple chart to our data.
To demontrate it, open your Microsoft Excel first, and fill data in A column and fill it with more one row, then in B column make formula like this =REPT("|", A1) then press Enter and you will see the result like the picture at above. You can also make it more creative with change your Font type, for example try =REPT("n", A1) then press Enter then change your font at your formula's column to Wingding so you will get the other great result.
And I know this chart's not 'nice' like the real chart but I think if you want to presenting chart to simple data maybe you can choose it.
Happy trying....
