Useful OpenOffice Calc Formulas and Related Tricks

First of all: if you want some great tips on using OpenOffice.org formulas, go to www.openofficetips.com or http://www.richhillsoftware.com/  (same site).

But here are some that I think are useful: formulas, related items, and calculations built into Edit > Paste Special.

FORMULA -- you can display the formula for any cell.   

=FORMULA(B6) shows you the formula from B6. 

To display formulas by other means, choose Tools > Options > OpenOffice.org Calc > View, and under the Display area, choose Formulas.

To copy and paste only cells that contain  formulas, copy, then choose Edit > Paste Special. Select only Formulas and click OK.

Paste Special, using the Operations section.

You can add, subtract, multiply, or divide values of one set of cells with another.

Here's an example of adding.
- You have two sets of cells.

- Copy a set of cells.

- Select the other set of cells and choose Paste Special.

- In the window that appears, choose All in the left column, then under Operations choose Add.

- Click OK.
- The cells you copied are added to the cells you pasted.

Here's what before and after for division look like. The second set of numbers, that you paste onto, are divided by the ones you copied.

Copy one set


The second set is divided by the first set

ISBLANK returns TRUE if a cell is blank.

This isn't as flashy, but ISBLANK(cellreference) or ISBLANK(cellrange) can be used for error checking or just seeing if you've got blanks where you shouldn't. For a cell range, it would be something like =ISBLANK(A5:A55).

INFO is a slightly obscure but perhaps useful formula.

Here are the possible values. Click the F(x) icon on the main toolbar, double-click the INFO formula and you'll see this window.

Here are some example values.

 

---

---

Comments

[JZA]

Interesting, althought I just went on my Linux OOo and did the INFO("osversion") and got Windows NT..

I wonder if this is defaulted on the compilation source or is just a bug.

[Dave]

Thanks for the recommendation. :)

Keep up the good work with your own site. You're an inspiration to the rest of us!

[h srinivas]

Hi All,

I am working on Openoffice.org - Calc. Can anybody help me to covert amount
in
numbers into Words for example : Rupees 4,00,00,000.50 to Rupees Four crores
& Paise fifty only? I need to print amount
amount in numbers and in words both in a customized report.
Shall remain ever greatfull to ur goodselves for rendring the help
Lot of thanks in Advance
Thanx & Regards
H Srinivas

[chandra]

write a c-program. That's a way too easier than trying to use the calc.