Search Contextures Sites

                              

 

 

 

 

 

 

 

 

Excel Functions and Excel Formats FAQ

Menu: Frequently Asked Questions about Microsoft Excel

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

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

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

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

    5. International issues

      1. Translate Functions

       

    Learn how to create Excel dashboards.

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?

  1. Enter the items down A1:A100.
  2. In B1:B100 enter formula =RAND().
  3. Sort the list by B column; top 20 rows is your selection.
  4. Press F9 for new B numbers
  5. 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.

  1. 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.
  2. 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.
  3. 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!

      1. Perhaps Calculation is set to Manual, alter this in Tools > Options menu.

The formulas are showing, instead of the results!

      1. 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.
      2. Perhaps the View Formulas option is turned on. To turn it off, 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 2003, and earlier versions:

  1. Select the data, and copy it.
  2. Select the cell where you want to paste the data
  3. Choose Edit>Paste Special
  4. Check the Transpose option, click OK

In Excel 2007, and later versions:

  1. Select the data, and copy it.
  2. Select the cell where you want to paste the data
  3. On the Ribbon's Home tab, click Paste, and click Transpose.

Watch this short video to see how to Transpose data in Excel 2007.

Using Data>Subtotals, I would like to create a table that has just these subtotals, not the hidden detail rows.

      1. Once you've applied the Subtotal, collapse the outline so that only the rows you want to copy are visible.
      2. Select the cells.
      3. Choose Edit>Go To, click the Special button
      4. Select 'Visible Cells Only', click OK
      5. Click the Copy button
      6. Go to another sheet, and paste

When I use AutoFilter I don't see all the items in the drop down list. Why not?

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 Ffirst 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.

  1. Select the region you want to apply this to, then choose Data>Validation.
  2. In the Allow dropdown, select List.
  3. 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)?

Try Conditional Formatting:
1. Select cell D25.
2. Choose Format>Conditional Formatting.
3. Set Condition 1 - Cell Value Is, not equal to, click on E25 on the worksheet.
4. Click on Format, select the Font tab. Under Colour, choose red.
5. Click OK, OK.

Can I format a cell blink or flash when a condition is met?

No.

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

    1. Before you type the character for which you want the overbar, change the font to Symbol.
    2. To create the bar, type the ` character (accent grave, may be above the Tab key)
    3. 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 Sub

paste the function in and OK, and in most cases it translates.
   

Learn how to create Excel dashboards.

Top of Page | Main Index

FAQs compiled by Harald Staff, Excel MVP 2000-2005

       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

 

Privacy Policy

 

Contextures Inc., Copyright © 2009.
All rights reserved.

 

Last updated: December 30, 2009 4:04 PM