# User FAQs

If you have a question, please send it to us.

**1)** What is the quickest way to add up a column of numbers ?

*) AutoSum - clicking on this command will add the SUM worksheet function below the current region

*) SUM - using this worksheet function manually.

*) AutoCalculate - highlight the cells and look in the status bar for the quick total.

**2)** What is the difference between the **COUNT** function and the **COUNTA** function ?__COUNT__ - Returns the number of non blank cells in a cell range.__COUNTA__ - Returns the number of non blank cells in a cell range, including logical values and text.

**3)** Why am I seeing a **_xlfn. prefix** displayed in front of a function ?

This means that this particular function is not supported in this version of Excel.

For example if someone has used a function that is only available in Microsoft 365 and you open the workbook in Excel 2016.

For example the XLOOKUP function, which is not supported in versions of Excel earlier than Excel 2021.

**4)** Using the **NOW** function inserts the current date and time. Is there a way to enter this information so it does not automatically update ?

Yes. Use these shortcut keys instead:

(Ctrl + ;) - inserts the current date

(Ctrl + Shift + ;) - inserts the current time

(Ctrl + ;) space (Ctrl + Shift + ;) - inserts the current date and time

**5)** What is the difference between the **SUBSTITUTE** function and the **REPLACE** function ?__SUBSTITUTE__ - returns the text string with a substring substituted for another substring.__REPLACE__ - this should be used when you want to replace text that occurs in a specific location in a text string.

SUBSTITUTE(text, old_text, new_text [,instance_num])

REPLACE(old_text, start_num, num_chars, new_text)

**6)** Are there any built-in worksheet functions that are **volatile** ?

Yes. TODAY, NOW, RAND, OFFSET, INDIRECT

CELL and INFO are also volatile but it depends on their arguments.

**7)** How would you sum all the values in column A where column B is greater than 15 ?

{=SUM(A1:A10*(B1:B10>=15))}

=SUMIF(B1:B10,">=15",A1:A10)

=SUMIFS()

**8)** When would you use the **VLOOKUP** function ?

When you want to match a value in the first column of a table and return a specific item in that row.

**9)** What are the four arguments passed to the **VLOOKUP** function ?

VLOOKUP(lookup_value, table_array, colum_index [,range_lookup])

lookup value - the value to be found in the first column

table_array - the cell range containing the table of data

column_index - the column number for the value you want returned

range_lookup - (optional) whether to find an exact match

**10)** Can you describe some of the common problems that people experience when using the **VLOOKUP** function ?

This function defaults to approximate match (not exact match)

The value you are looking up must be in the first column of your data table.

The column index number will be broken is you insert or delete columns in your data table.

Forgetting to make the table_array absolute references (using $) before dragging down a formula.

**11)** What function would you use if you want to match a value in the first row of a table and return a specific item in that column ?__HLOOKUP__ - returns the value in a row that matches a value in the top row of a table.

**12)** When would you use a combination of the **INDEX** and **MATCH** functions ?

If you wanted to lookup a value that was not in the first column.

INDEX('col_return', MATCH('match_item', 'col_data',0), 1)

**13)** When would you use a combination of the **OFFSET** and **MATCH** functions ?

You should try not to use this combination because OFFSET is a volatile function and using it is very inefficient.

Always use __INDEX__ and __MATCH__ instead.

OFFSET('col_header', MATCH('col_data','table',0) -1, col_offset, 1, 1)

**14)** Be careful when you use the **ROUND** function.

The sum of the rounded numbers does not always equal the rounded sum of the unrounded numbers.

© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext