...when used with Vision 3

Importing Vision data into Microsoft Excel requires two elements to of been performed per workstation; If you are on the INPS remote server (VES) the following is automatically installed and you may skip these steps: A Microsoft Visual FoxPro ODBC Driver needs to of been installed per workstation it is run on. Download and run : English VFPODBC.MSI Microsoft Query needs to of been installed which is part of the  Microsoft Office installation. If it hasn't been installed you will be prompted and may need the original CD to add this feature. How do we : ...use VLOOKUP to combine data from 2 worksheets? ...remove #N/A when using VLOOKUP? ...remove zero values when using VLOOKUP? ...change the reverse date format into a UK date? ...change text time into actual time values? ...change numbers entered as text back into a number? ...add a day and week value from the date? ...change a date of birth into an age? ...remove the space from an NHS No? Other Useful Information Conditional Formulas and functions used in Excel View a few keyboard shortcuts you may use in Excel

Conditional Formulas and functions used in Excel

What is a function? Basically, functions are complex formulas whereby all of that complexity happens "behind the scenes." As the user, all you need to memorise is the function name and how to set it up properly. Excel does the rest. Using functions always requires typing in the function name and a set of parentheses ( ). The parentheses usually hold one or more arguments, such as cell addresses (e.g., A1 or C3, etc.).

So, what's a formula, then? Well, formulas are usually combinations of operators (like +, - and = signs), numbers, cell addresses, and sometimes functions themselves. Formulas are needed when there is no specific existing function for what you need, or if you need to combine multiple functions together.

Note: to use both formulas and functions properly, always start with a =

Testing whether conditions are true or false and making comparisons between them are very common. You can use the AND, OR, NOT, and IF function to create conditional formulas.

The IF function uses the following arguments.

 logical_test: The condition that you want to check.

 value_if_true: The value to return if the condition is true.

 value_if_false: The value to return if the condition is false.

Below, area few useful Excel formulas that you should know. Each is very useful, and most Excel users will find that these come in very handy:

1. Use a formula with an IF function to evaluate or compare text or numbers within cells:

As an Excel user, you will often want an easy way to compare or evaluate a number in a cell or a range of cells. One way to do this is with an IF function. An IF function tells you whether something is true or false. However, instead of displaying a TRUE or FALSE value as a result, instead it lets you control exactly how the resulting value will look.

For example, here are some formulas:

2. Evaluate more than one cell at once with OR and AND functions:

The AND and OR functions work similarly to the IF function. However, they return the values TRUE or FALSE depending on the result, rather than text that you specify. Also, unlike the IF function, AND and OR necessarily compare combinations of two or more values at once.

The difference between the AND and OR functions themselves is that (as their names imply): the AND function only returns TRUE if both/all conditions are met, while OR returns TRUE if any one condition is met. Examples:

=AND(A1>1, A1<10)   (note: this returns TRUE if the number in A1 is 2, 3, 4, 5, 6, 7, 8 or 9; otherwise, it returns FALSE).

=OR(A1="Dr John Mcallister","B1="Dr John Mcallister")    (note: this returns TRUE if the text in A1 or the text in B1 (or both) is "Dr John Mcallister.")

For example, here are some formulas:

3. Add today's date to a spreadsheet:

Okay, now for a quick-and-easy one. How do you easily add today's date to a cell without having to type it in again every day? Simple! Use: TODAY().

Example:

=TODAY()   (note: this returns today's date in a date format, such as DD/MM/YYYY).

The next time you find yourself needing to evaluate one or more values, try the IF, OR and AND functions. And, to add today's date to a spreadsheet, try the TODAY function.

Excel shortcuts

Back to top