Search and replace

Menu location: Data_Search & Replace.

This function searches the active worksheet for data that match the conditions you specify. When the condition, for example data > 20, is found then you have the option to replace or simply count the data that match this condition.

Note that you can use the replace function to remove cells or rows from a worksheet by leaving the "replace with" box blank.

Example

The following are data imported from an application which uses -99 as the missing data value:

To replace -99 with StatsDirect missing data you can use the search function with the "search for x" condition set to equal to -99 and the "replace with" entry set to *. To run this example, enter the above data into a workbook column and select that column. Then select search from the data menu. Check the "equal to" option, enter -99 in the "search for x" box and enter * in the "replace with" box. When you click on the OK button the -99 values will be replaced with * and these will also be displayed as an asterisk (missing data) in the workbook.

The match expression option of the StatsDirect search form enables you to select data for an expression which evaluates as true. You use x to denote the data to match, i.e. x ³ 10 and x £ 20 matches all selected data between 10 and 20 inclusive, you can count or replace the matches by clicking the relevant button. You can compose an expression using x and any of the functions, operators and logical expressions below:

Please note that the largest factorial allowed is 170.569398315538748, but you can work with Log factorials via the LOG! function, e.g. LOG!(171).

Calculations give an order of priority to arithmetic operators, this must be considered when entering expressions. For example, the result of the expression "6 - 3/2" is 4.5 and not 1.5 because division takes priority over subtraction.

To convert degrees to radians, multiply degrees by pi/180. To convert radians to degrees, multiply radians by 180/pi.