MS Excel, formular with blank cells.

Posted on 2012-08-23
Last Modified: 2012-08-23
I have an excel file (attached),  the column H shows #VALUE.
I have to move cursor to F2 then press delete key to delete nothing, then the column H show correct data.

Please help me this.
Question by:JameMeck
    LVL 85

    Accepted Solution

    You could simply change the formula in H2 to:
    and copy it down.
    LVL 50

    Assisted Solution


    where does the data come from? Even though the cells look empty, Excel considers some of the empty cells as text, which is the reason for the VALUE error. Some of the empty cells are seen as numbers, even if they are empty, and some of them are neither text nor numbers, i.e. just really empty.

    Did you copy and paste the data from another application? That would explain the behaviour.

    To repair the data, you could do the following,

    In K2 enter


    copy across to N2 and down to row 17. Then copy the results and use Paste Special > Values to paste into D2. Now all empty cells will have a zero, but your formulas will calculate correctly.

    You can hide the zeros with formatting.

    cheers, teylyn
    LVL 9

    Expert Comment

    Try the following in cell H2 and then copy it to the other cells


    Author Closing Comment


    With your solutions, I could make it works now.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now