Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Formulae

Built in Functions

KSpread has a huge range of built in mathematical and other functions that can be used in a formula cell. They can be seen and accessed by selecting a cell then choosing Function... from the Insert menu. This brings up the Function dialog box.

Select the function you want to use from the listbox at the left of the dialog box. The Help tab page will then display a description, the return type, Syntax, Parameters, and Examples for this function. In addition this page provides often links to Related Functions. Then press the button with the down arrow key symbol on it to paste it into the text edit box at the bottom of the dialog.

The Parameters tab page will then be displayed to let you enter the parameter(s) for the function you have just chosen. If you want to enter an actual value for a parameter, just type it into the appropriate text box in the Parameters page. To enter a cell reference rather than a value, left click on the appropriate text box in the Parameters page; then left click on the target cell in the spreadsheet.

Instead of using the Parameters page, cell references such as B6 can be entered by typing them directly into the edit box at the bottom of the Function dialog. If a function has more than one parameter separate them with a semi-colon (;).

Pressing the OK button will insert the function into the current cell and close the Function dialog.

You can of course do without the Function dialog and simply type the complete expression into the Formula toolbar's main edit box. Function names are not case sensitive. Do not forget that all expressions must start with an = symbol.

Logical Comparisons

Logical functions such as IF(), AND(), OR() take parameters which have the logical (boolean) values True or False. This type of value can be produced by other logical functions such as ISEVEN() or by the comparison of values in spreadsheet cells using the comparison expressions given in the following table.

Expression Description Example
= = Is equal to A2==B3 is True if the value in A2 is equal to the value in B3
! = Is not equal to A2!=B3 is True if the value in A2 is not equal to the value in B3
< > Is not equal to Same as A2!=B3
< Is less than A2<B3 is True if the value in A2 is less than the value in B3
< = Is less than or equal to A2<=B3 is True if the value in A2 is less than or equal to the value in B3
> Is greater than A2>B3 is True if the value in A2 is greater than the value in B3
> = Is greater than or equal to A2>=B3 is True if the value A2 is greater than or equal to the value in B3

Thus if you enter =IF(B3>B1;"BIGGER";"") into a cell it will display BIGGER if the value in B3 is greater than that in B1, otherwise the cell will show nothing.

Absolute Cell References

If a formula contains a cell reference that reference will normally be changed when the cell is copied to another part of the worksheet. To prevent this behavior put a $ symbol before the column letter, row number or both.

  • If A1 contains the formula =D5 then on copying the cell to B2 it will become =E6 (the normal behavior).

  • If A1 contains the formula =$D5 then on copying the cell to B2 it will become =D6 (column letter not changed).

  • If A1 contains the formula =D$5 then on copying the cell to B2 it will become =E5 (row number not changed).

  • If A1 contains the formula =$D$5 then on copying the cell to B2 it will remain as =D5 (neither the column letter nor the row number are changed).

When you are entering or editing a cell reference in a formula the shortcut key F4 can be used to step through these four possibilities.

Named cells can be used in a similar way to include a unchanging cell reference in a formula.




 
 
  Published under the terms of the GNU General Public License Design by Interspire