Home > Formulas > Sum > FAQs
Excel Functions and Formatting FAQ
Get answers to Frequently Asked Questions about Excel worksheet functions and Excel formatting

|
1. Functions
- Round a value
to the nearest increment of 5
- Find
value in another sheet and return data from lookup table
- Add the largest/smallest 5 entries
- Pick 20 random items from a list
- Sum visible cells only
- Sum
numbers between 5 and 10
- Formula result is #NAME
- Formula to get sheet name
Is there a formula that will round a value to
the nearest increment of 5, or to the nearest quarter?
=ROUND(A1/5,0)*5
=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
- Formula does not calculate correctly!
- Formula does not calculate at all!
- Formulas show, instead of
results
My formula does not calculate correctly! 
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
https://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
- Change data from rows to columns
- In Data>Subtotals copy the totals only.
- AutoFilter drop down does not show all
items
- Split first and last names.
- Make a drop down list in a cell
Data stored in rows,
change 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.
How to split full name into separate cells.
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
- Make cell change colour,
based on value in another cell
- Format a cell to blink or flash?
- Add a bar above a character, e.g.
Can a formula that
do this -- =IF(D25 does not equal E25 then D25 font will turn red)? 
Try Conditional
Formatting:
- 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. Use conditional formatting to change the cell colour instead.
How can I add a bar above a character?
- 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
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 Sub
paste the function in and OK, and in most cases it translates.