Search Contextures Sites Custom Search

More FAQs

Excel Functions and Excel Formats FAQFrequently Asked Questions (FAQs) and answers about Excel functions and formatting.

Functions

- Is there a formula that will round a value to the nearest increment of 5?
- What formula will look at a cell, find that value in another sheet and return data from an adjacent column.
- I want to add the largest/smallest 5 entries in A column.
- How do I pick 20 random items from a list of 100?
- Is it possible to write a SUMIF worksheet formula to sum visible cells only?
- How do I enter two criteria so I can sum numbers between 5 and 10?
- Why does my function display #NAME?
- Is there a way of returning the name of a sheet in a cell without using code?

Calculation

- My spreadsheet does not calculate correctly ! I'm right, Excel's wrong!
- My spreadsheet does not calculate at all!
- The formulas are showing, instead of the results

Working with Tables and Lists

- I have data stored in rows and I want to change these rows to columns
- Using Data>Subtotals, I would like to create a table with just these subtotals, not the detail rows.
- When I use AutoFilter I don't see all the items in the drop down list. Why not?
- In a cell I have "lastname, firstname". I want lastname in one cell and first name in another.
- I want to fix a cell so the user can only choose from a list

Formatting

- Is there a way to make a cell turn red, based on the value in another cell?
- Can I format a cell to blink or flash when a condition is met?
- How can I add a bar above a character, e.g. ?

## 1. Functions

## Is there a formula that will round a value to the nearest increment of 5?

=ROUND(A1/5,0)*5

## or to the nearest quarter ?

=ROUND(A1/0.25,0)*0.25

## I am looking for a formula that will look at an adjacent column then find that value in another sheet and return the data from an adjacent column.

=VLOOKUP(A2,Sheet2!A2:B100,2,FALSE)

For more information and examples, see Excel -- Worksheet Functions -- VLookup

## I want to add the largest/smallest 5 entries in A column.

=SUM(LARGE(A:A,{1,2,3,4,5}))

## How do I pick 20 random items from a list of 100?

- Enter the items down A1:A100.
- In B1:B100 enter formula =RAND().
- Sort the list by B column; top 20 rows is your selection.
- Press F9 for new B numbers
- Repeat for a new selection.
## Is it possible to write a SUMIF worksheet formula to sum visible cells only?

If the cells are hidden as a result of AutoFiltering, you can use the SUBTOTAL function, with 9 as the first argument

## I can sum all numbers >=10 with =SUMIF(A1:A20,">=10") . But how do I enter two criteria so I can sum numbers between 5 and 10?

That equals sum of all >=5 minus sum of all > 10:

=SUMIF(A1:A20,">=5")-SUMIF(A1:A20,">10")Or you can use this method:

=SUMPRODUCT((A1:A20>=5)*(A1:A20<=10)*A1:A20)## Why does my function display #NAME?

The function may point to an add-in function that is not available to this Excel. Most frequently it's an Analysis ToolPak function; choose Tools > Add-Ins and check that there are checks against Analysis ToolPak.

Unlike Excel's built-in functions, Add-in functions do not translate themselves to regional language, so American add-in functions are by default unavailable on a Norwegian computer and vice versa.

## Is there a way of returning the name of a sheet in a cell without using code?

=CELL("Filename",A1) returns the complete file path and sheet name

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) will strip away everything but the sheet name.

Note:The file must be saved or the formula will not work.

2. Calculation## My spreadsheet does not calculate correctly ! I'm right, Excel's wrong!

There are three common causes for messages like this.

- What is displayed in the cells is not what's really there. A cell can show 1 (no decimals format) but it may contain real or calculated values like 0.6 or 1.4. Add or multiply a bunch of those and you're surprised; Excel will calculate with real cell contents, NOT displayed contents. You may choose "precision as displayed" in the Tools > Options menu for a workaround, but make sure you know what you're doing.
- A computer use binary numbers, and this has its limitations. It canot represent numbers like 1/10 exactly. Numbers like that are rounded to nearest 15 significant decimal digits, and Excel will be "wrong" around 15th-16th digit. Some operations suffer from this, and some Boolean tests (tests that may appear as 0.1=0.1) can return False because of this. "Normal work" like sensible-number budgeting and day-to-day math is usually not affected, but this may not be the tool for advanced science.
- You are using Excel's statistical functions. Some of those are not good enough.

LINEST() (also SLOPE(), INTERCEPT(), VAR(), STDEV(), LOGEST(),

TREND(), FORECAST(), etc.) uses a numerically unstable algorithm.You're right, Excel's wrong. Links to backgrounders and tools at http://www.mathtools.net/Excel/Statistics/index.html

## My spreadsheet does not calculate at all!

- Perhaps Calculation is set to Manual, alter this in Tools > Options menu.
## The formulas are showing, instead of the results!

- The cell may be formatted as Text. To change it, choose Format>Cells, and on the Number tab, choose a category other than Text, e.g. General or Number.
- Perhaps the View Formulas option is turned on. To turn it off in Excel 2010:

- On the Excel Ribbon, click the Formulas tab. In the Formula Auditing group, click Show Formulas
- OR, in Excel 2003, choose Tools>Options. On the View tab, remove the check mark from Formulas.

Tip: The keyboard shortcut to show or hide the formulas is Ctrl +`(accent grave, may be above the Tab key on the keyboard)

3. Working with Tables and Lists## I have data stored in rows and I want to change these rows to columns

## In Excel 2007, and later versions:

- Select the data, and copy it.
- Select the cell where you want to paste the data
- On the Ribbon's Home tab, click Paste, and click Transpose.
Watch this short video to see how to Transpose data in Excel 2007.

## In Excel 2003, and earlier versions:

- Select the data, and copy it.
- Select the cell where you want to paste the data
- Choose Edit>Paste Special
- Check the Transpose option, click OK
## Using Data>Subtotals, I would like to create a table that has just these subtotals, not the hidden detail rows.

- Once you've applied the Subtotal, collapse the outline so that only the rows you want to copy are visible.
- Select the cells.
- Choose Edit>Go To, click the Special button
- Select 'Visible Cells Only', click OK
- Click the Copy button
- Go to another sheet, and paste
## When I use AutoFilter I don't see all the items in the drop down list. Why not?

In older versions of Excel, an AutoFilter dropdown list will only show 1000 entries. You could add a new column, and use a formula to split the list into 2 or three groups, e.g.:

=IF(LEFT(C2,1)<"N","A-M","N-Z")

Filter on this column first, then by the intended criteria.

Another option is to choose Custom from the drop-down list, and type the criteria.## In a cell I have "LastName, FirstName". I want to put Last Name in one cell and First Name in another.

Use Data>Text to Columns and specify the comma as a delimiter.

I want to fix a cell so the user can only choose from a list, for example DHL, FEDEX or UPS.You can use Data Validation to create a drop down list in the cell. For a short list, you can type the options in the Source box, as shown below.

- Select the cell(s) where you want the list to appear, then choose Data>Validation.
- In the Allow dropdown, select List.
- In the Source textbox, enter "DHL,FEDEX,UPS" (without the quotes)

4. Formatting## Is there a way to create a formula that will do this type of function --

=IF(D25 does not equal E25 then D25 font will turn red)?

- Select cell D25.
- Choose Format>Conditional Formatting.
- Set Condition 1 - Cell Value Is, not equal to, click on E25 on the worksheet.
- Click on Format, select the Font tab. Under Colour, choose red.
- Click OK, OK.
## Can I format a cell to blink or flash when a condition is met?

No.

## How can I add a bar above a character, e.g. ?

- Before you type the character for which you want the overbar, change the font to Symbol.
- To create the bar, type the ` character (accent grave, may be above the Tab key)
- Then, stay in Symbol font, or switch to a different font, and type the character that has the overbar.

Note: This technique looks better on-screen in some font sizes than in others, but all should look okay when printed.

5. International Issues## Translate Functions

These functions are in English, and you can not enter them in a Swedish Excel as is. Run this macro:

Sub EnterEnglishFunction() ActiveCell.Formula = InputBox("English function:") End Subpaste the function in and OK, and in most cases it translates.

Original FAQs compiled by Harald Staff, Excel MVP 2000-2005. Additions by Debra Dalgleish.

Contextures Inc., Copyright ©2016

All rights reserved.

Last updated: December 26, 2015 1:17 PM