Category: Advanced Spreadsheets

Use financial functions: fv, pv, pmt.

In the same wizard, under the ‘Financial’ category, we have the ‘FV’ function. This function returns the future value of an investment based on regular payments and a constant rate of interest.

This function takes up five values, ‘Rate’, ‘NPER’, ‘PMT’, ‘PV’ and ‘Type’. ‘Rate’ is the rate of interest per period. ‘NPER’ is the payment period, the total number of periods in which the annuity is paid. ‘PMT’ is the regular payments, the constant annuity to be paid in each period. ‘PV’ is the present value, the current value of a series of payments.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-financial-functions]
[the_ad id=”12356″]

Let us take an example for the ‘FV’ function. Say the annual rate of interest, ‘Rate’, is ‘0.06’. The number of payments, ‘NPER’, is ‘10’. The amount of regular payments, ‘PMT’, is ‘-200’. The present value, ‘PV’, is ‘-500’. ‘Type’ defines whether the payment for each period is due at the beginning of the period or at the end of the period. ‘1’ denotes due at the beginning and ‘0’ denotes due at the end. Here we will take the ‘Type’ as 1. So in this case the payments are due at the start of each period.

After providing all the inputs, when we click on ‘OK’, the value returned by the ‘FY’ function will be ‘3689.75’, as has been shown in the illustration. This value is achieved by using the appropriate financial calculation methodology. This is how the ‘FV’ function works.

Similarly, we have the ‘PV’ function. This function calculates the present value of an investment. This function takes up similar five arguments, ‘Rate’, ‘NPER’, ‘PMT’, ‘FV’ and ‘Type’. ‘Rate’ is the rate of interest per period. ‘NPER’ is the payment period, the total number of periods in which the annuity is paid. ‘PMT’ is the regular payments, the constant annuity to be paid in each period. ‘FV’ is the future value, the final value to be attained after the last payment. ‘Type’ defines whether the payment for each period is due at the beginning of the period or at the end of the period. ‘1’ denotes due at the beginning and ‘0’ denotes due at the end.

We have already seen an example for the ‘FV’ function. The ‘PV’ function works in a similar manner. The only difference is that here, instead of ‘PV’, we have ‘FV’ as one of the arguments.

We also have a ‘PMT’ function. This function returns the periodic payment of an annuity, based on regular payments and a fixed periodic interest rate. This function takes up similar five arguments, ‘Rate’, ‘NPER’, ‘FV’, ‘PV’ and ‘Type’. ‘Rate’ is the rate of interest per period. ‘NPER’ is the payment period, the total number of periods in which the annuity is paid. ‘FV’ is the future value, the final value to be attained after the last payment. ‘PV’ is the present value, the current value of a series of payments. ‘Type’ defines whether the payment for each period is due at the beginning of the period or at the end of the period. ‘1’ denotes due at the beginning and ‘0’ denotes due at the end.

We have already seen an example for the ‘FV’ function. The ‘PMT’ function works in a similar manner. The only difference is that here, instead of ‘PMT’, we have ‘FV’ as one of the arguments.
[the_ad id=”12397″]

Use text functions: left, right, mid, trim, concatenate.

In the ‘Function Wizard’, under the ‘Text’ category, we have some functions which can be used to analyze text values.

One such function is ‘LEFT’. This function returns a specified number of first characters from any word. This function takes two arguments, ‘text’ and ‘number’. The ‘text’ is the text value on which the operation needs to be performed and ‘number’ is the number of characters which are to be returned, starting with the first character up to the character at this number.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-text-functions]
[the_ad id=”12356″]

Below is an example. Here, the text chosen is ‘Science’ and the number is 3. So the end result, as has been shown is ‘Sci’.

We also have a ‘RIGHT’ function which works in exactly the same way as ‘LEFT’, but for one difference. Here the characters returned are from the end instead of being from the start. Other than this, everything else is same.

Another text function is ‘MID’; this function returns a partial text string from a text value. This function takes three arguments, ‘text’, ‘start’, and ‘number’. The text value from which the partial text string is to be determined is given by ‘text’, ‘start’ gives the position in the text value from where the part word is to be determined and ‘number’ gives the number of characters in the part word, the number of characters to be returned starting with the position specified by ‘start’.

Here is an example of the usage of ‘MID’.

The text value selected in this example is ‘Female’. The ‘start’ is given as 3 and the ‘number’ is given as 2. So starting from third character which is ‘m’ up to 2 characters from this character, the end result is ‘ma’. This is how ‘MID’ function works.

‘TRIM’ function simply removes any extra spaces between words in a text field. It takes just one argument, the text field containing a number of words and returns the same set of words, but by removing any extra spaces between these words.

Another text function is ‘CONCATENATE’. This function adds the text values present in different cells and returns a single text value by adding all the different values together. Here a simple example of ‘CONCATENATE’ function has been shown. The words ‘Science’, ‘Humanity’ and ‘Arts’ have been added together and have been returned as a single word.

[the_ad id=”12397″]

Use text functions: left, right, mid, trim, concatenate.

In the ‘Function Wizard’, under the ‘Text’ category, we have some functions which can be used to analyze text values.

One such function is ‘LEFT’. This function returns a specified number of first characters from any word. This function takes two arguments, ‘text’ and ‘number’. The ‘text’ is the text value on which the operation needs to be performed and ‘number’ is the number of characters which are to be returned, starting with the first character up to the character at this number.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-text-functions]
[the_ad id=”12356″]

Below is an example. Here, the text chosen is ‘Science’ and the number is 3. So the end result, as has been shown is ‘Sci’.

We also have a ‘RIGHT’ function which works in exactly the same way as ‘LEFT’, but for one difference. Here the characters returned are from the end instead of being from the start. Other than this, everything else is same.

Another text function is ‘MID’; this function returns a partial text string from a text value. This function takes three arguments, ‘text’, ‘start’, and ‘number’. The text value from which the partial text string is to be determined is given by ‘text’, ‘start’ gives the position in the text value from where the part word is to be determined and ‘number’ gives the number of characters in the part word, the number of characters to be returned starting with the position specified by ‘start’.

Here is an example of the usage of ‘MID’.

The text value selected in this example is ‘Female’. The ‘start’ is given as 3 and the ‘number’ is given as 2. So starting from third character which is ‘m’ up to 2 characters from this character, the end result is ‘ma’. This is how ‘MID’ function works.

‘TRIM’ function simply removes any extra spaces between words in a text field. It takes just one argument, the text field containing a number of words and returns the same set of words, but by removing any extra spaces between these words.

Another text function is ‘CONCATENATE’. This function adds the text values present in different cells and returns a single text value by adding all the different values together. Here a simple example of ‘CONCATENATE’ function has been shown. The words ‘Science’, ‘Humanity’ and ‘Arts’ have been added together and have been returned as a single word.

[the_ad id=”12397″]

Use statistical functions: countif, countblank, rank.

In the same wizard, under the ‘Statistical’ category, we have the ‘COUNTIF’ function. This function counts the number of arguments which meet the set conditions. This function takes two arguments, ‘range’ and ‘criteria’. These give the range of cells to be evaluated and the criteria to be used for this evaluation respectively. These can be entered by using any of the three methods: manually, using the ‘Select’ icon, or using the ‘Function’ icon.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-statistical-functions]
[the_ad id=”12356″]

Let us take an example for the ‘COUNTIF’ function. Let the ‘range’ which is selected contains 5 cells, and the values in these cells be ‘550’, ‘450’, ‘700’, ‘380’, and ‘750’. Now let us set the ‘criteria’ as “>500”. It should be remembered that when the ‘criteria’ includes any mathematical or logical symbol, then it must be surrounded by double quotes. “>500” means greater than 500. So this ‘criteria’ will check the cells specified in the range and will only count those cells whose value is greater than 500. So here the counted cells will be the ones containing ‘550’, ‘700’ and ‘750’ and the count will come out to be ‘3’. This is what will get printed in the selected cell for the output on the click of the ‘OK’ button. This is how the ‘COUNTIF’ function works.

We also have a ‘COUNTBLANK’ function in the same ‘Statistical’ category. This function counts the number of empty cells in a given cell range. This function takes only one argument, ‘range’. This gives the range of cells to be evaluated. This can be entered either manually or by using the ‘Select’ icon. So if the range entered contains say 10 cells and out of these 7 contain some values and 3 are empty, then the result of the ‘COUNTBLANK’ function will be ‘3’.

We have another function in this category, called ‘RANK’. This function returns the ranking of a value in a sample data. This function takes three values, ‘value’, ‘Data’ and ‘Type’. These give the value for which the rank is to be determined, the data array and the type of ranking method respectively. These can be entered by using any of the three methods: manually, using the ‘Select’ icon, or using the ‘Function’ icon.

Say we enter the ‘value’ as ‘34’, and the selected data array under ‘Data’ contains 10 values one among which is ‘34’. ‘Type’ can be any number. If it is taken as 0 or no value is provided, then this means that the data array needs to be arranged in descending order. Any other value for ‘Type’ means the data array needs to be arranged in ascending order.

Say here we give the ‘Type’ as 0, so we need to arrange the data array in descending order. Now the data will get arranged in descending order and ‘RANK’ will return the position of ‘34’ from this arrangement. That position will be the rank of the argument. This is how ‘RANK’ function works.

[the_ad id=”12397″]

Use mathematical functions: rounddown, roundup, sumif.

To use mathematical functions, in the same ‘Function Wizard’, select the ‘Mathematical’ category. From the ‘Function’ drop-down list, select ‘ROUNDDOWN’. This function rounds a number down to a predefined accuracy. This means that the number is rounded in such a way that the lower value is always displayed.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-mathematical-functions]
[the_ad id=”12356″]

After selecting ‘ROUNDDOWN’, click on ‘Next’. This function will take two inputs, ‘number’ and ‘count’. ‘number’ is the number which needs to be rounded down and this can be selected from the sheet using the ‘Select’ icon. ‘count’ is the number of places down to which the number needs to be rounded. This can be entered manually or can be selected from the sheet.

After selecting everything, click on ‘OK’. This will display the result in the selected cell. As an example, if the ‘number’ selected is say ‘51.867’ and ‘count’ is given as ‘2’, then the result will be ‘51.86’.

The ‘ROUNDUP’ function is exactly similar to the ‘ROUNDDOWN’ function, with the only point of difference being that in ‘ROUNDUP’, the number is rounded up to the predefined accuracy. This simply means that in this case, the higher value is always going to be displayed. Rest everything, including the steps of usage, remain exactly same. Taking the same example, if the ‘number’ selected here is again ‘51.867’ and ‘count’ is given as ‘2’, then the result this time will be ‘51.87’. The next highest value will be taken.

In the same wizard, we have the ‘SUMIF’ function. This function totals the arguments if they meet the specified conditions. The ‘SUMIF’ function takes three arguments, ‘range’, ‘criteria’ and ‘sum_range’. ‘range’ is the cell range which is to be evaluated by the given criteria, ‘criteria’ is the criteria which need to be applied to this range and ‘sum_range’ gives the range from which the values are to be totaled.

All these arguments can be entered either manually, or by using the ‘Select’ icon, or by using the ‘Function’ icon in front of the different options. This icon helps in entering a function as an argument for another function.

Let us take an example for the ‘SUMIF’ function. Let the ‘range’ which is selected contains 5 cells, and the values in these cells be ‘550’, ‘450’, ‘700’, ‘380’, and ‘750’. Now let us set the ‘criteria’ as “>500”. It should be remembered that when the ‘criteria’ includes any mathematical or logical operator, then it must be surrounded by double quotes. “>500” means greater than 500. So this ‘criteria’ will check the cells specified in the range and will only take those cells whose value is greater than 500. So here the selected cells will be ‘550’, ‘700’ and ‘750’ and the sum will come out to be ‘2000’. This is what will get printed in the selected cell for the output on the click of the ‘OK’ button. This is how the ‘SUMIF’ function works.

Another input which can be provided to the ‘SUMIF’ function is ‘sum_range’. Here we can provide a range of cells. These will be the cells whose sum will be actually taken as the output in case the cells in the range satisfy the criteria. This input is optional.
[the_ad id=”12397″]

Use date and time functions: today, now, day, month, and year.

To use the different types of functions and formulas in Calc, select the cell in which you want to display the result of the function, and then click on ‘Insert’ from the main menu bar, and from the resulting drop-down menu, click on ‘Function’. This will open the ‘Function Wizard’.

[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-date-and-time-functions]
[the_ad id=”12356″]

In the ‘Function Wizard’, under the ‘Functions’ tab, we have a ‘Category’ drop-down list. To use the date and time functions, select ‘Date&Time’ in ‘Category’. This will display all the date and time functions under the ‘Function’ drop-down list. From this list, select ‘TODAY’ and click on ‘Next’.

The today function will get displayed in the ‘Formula’ section. After this, click on ‘OK’. The current date of your computer will get displayed in the cell which had been selected initially.

Similarly we can use the other date and time functions. From the same ‘Function Wizard’, under the same ‘Date&Time’ category, we have the ‘NOW’ function. We can use this function in the same way as we did the ‘TODAY’ function and this will display the current date and time of your computer.

In the same wizard, we have the ‘DAY’, ‘MONTH’, and ‘YEAR’ functions. The ‘DAY’ function determines the sequential date of the month as an integer in relation to a date value. The ‘MONTH’ function determines the sequential number of a month of the year for a date value. Similarly the ‘YEAR’ function returns the year of a date value as an integer.

To use any of these functions, in the same ‘FUNCTION WIZARD’, select the appropriate function and then click on ‘Next’. Here, we need to provide a date value.

To provide a date value, use the ‘Select’ icon in front of ‘Number’. When this icon is clicked, the dialog box is minimized and you can select the value of the date from any of the cells from the sheet. Select any cell which stores a complete date value and then again click on this ‘Select’ icon. This will again maximize the dialog and the input will get stored in the function. After this, click on ‘OK’ and the required value will get displayed in the selected cell.

Say we are using the ‘MONTH’ function and the date value which is provided as the input is ‘08/07/15’. In this case the value returned by the ‘MONTH’ function will be ‘7’ which will be an integer value. The ‘YEAR’ and ‘DAY’ functions work in a similar manner.
[the_ad id=”12397″]

Hide, show rows, columns, worksheets.

To hide a row or a column, just select the row or column you want to hide. After selecting the row or column, click on ‘Format’ from the main menu bar and from the drop-down, click on ‘Row’ or ‘Column’ as per the selected data range. From the sub-menu, click on ‘Hide’ and the selected row or column will no longer be visible.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-hide-and-show]
[the_ad id=”12356″]

For hiding a worksheet, right click on the sheet name as has been shown below. From the resulting menu, just click on ‘Hide’ and the sheet will no longer be visible.

To make the hidden rows and columns visible again, select the rows or columns on both sides of the hidden row or column and then click on ‘Format’ from the main menu bar. From the resulting drop-down, click on ‘Row’ or ‘Column’ and then from the sub-menu, click on ‘Show’. The hidden rows or columns will again become visible.

The important point is that the columns or rows on the both sides of the hidden column or row need to selected to make them visible again.

To make a hidden worksheet visible again, right-click on any of the adjoining sheets of the hidden sheet and from the resulting menu, click on ‘Show’. This will give a dialog box containing the names of all the hidden sheets. Select the one which is required and click on ‘OK’. The sheet will become visible again.
[the_ad id=”12397″]

Split a window. Move and remove split bars.

Splitting a window into multiple instances can help us in viewing and operating with multiple spreadsheets at the same time. To split a window, Click on the row header below the rows where you want to split the screen horizontally or click on the column header to right of the columns where you want to split the screen vertically. After this, click on ‘Window’ from the main menu bar. From the resulting drop-down, click on ‘Split’. This will split the window into different portions.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-split-bars]
[the_ad id=”12356″]

To move the split bars, hover the mouse on top of the split bar. The shape of the cursor will change to an icon with arrows on both the sides of two small parallel lines. When this icon gets displayed, click on the split bar and without releasing the mouse button move the icon to the left or right or to the top or bottom. This way you can move the split bars.

To remove the slip bars, just click on ‘Window’ from the main menu bar, and from the resulting drop-down, click on ‘Split’ to deselect it. This will remove the split bars.
[the_ad id=”12397″]

Split a window. Move and remove split bars.

Splitting a window into multiple instances can help us in viewing and operating with multiple spreadsheets at the same time. To split a window, Click on the row header below the rows where you want to split the screen horizontally or click on the column header to right of the columns where you want to split the screen vertically. After this, click on ‘Window’ from the main menu bar. From the resulting drop-down, click on ‘Split’. This will split the window into different portions.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-split-bars]
[the_ad id=”12356″]

To move the split bars, hover the mouse on top of the split bar. The shape of the cursor will change to an icon with arrows on both the sides of two small parallel lines. When this icon gets displayed, click on the split bar and without releasing the mouse button move the icon to the left or right or to the top or bottom. This way you can move the split bars.

To remove the slip bars, just click on ‘Window’ from the main menu bar, and from the resulting drop-down, click on ‘Split’ to deselect it. This will remove the split bars.
[the_ad id=”12397″]

Copy, move worksheets between spreadsheets.

To copy or move a worksheet between spreadsheets, right-click on the sheet name which needs to be moved or copied and from the resulting menu, click on ‘Move/Copy Sheet’.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-worksheets]
[the_ad id=”12356″]

This will open the ‘Move/Copy Sheet’ dialog box.

In this dialog box, we have two radio buttons to choose if we want to ‘Move’ the sheet, or ‘Copy’ the sheet. Select this option as per the requirement. Then under ‘Location’, in ‘To document’ drop-down menu, select the spreadsheet to which you want to copy or move this sheet. This drop-down will list all the open spreadsheets at the time. So the spreadsheet, to which you need to copy or move the sheet, must be simultaneously open. In ‘Insert before’, select the position in the spreadsheet where you want to copy or move this sheet.

A new name to this sheet can also be given under ‘New name’. This name will be used for the sheet after copying or moving. After making all the inputs, click on ‘OK’. The sheet will get copied to or will be moved to the new location.
[the_ad id=”12397″]