Return empty cell from formula in Excel

Return empty cell from fromula in Excel

Asked on December 14, 2018 in Excel.
Add Comment

  • 3 Answer(s)

    Solution for Return empty cell from formula in Excel,

        If moving to have to use VBA, then. Iterate over the cells in our range, test the condition, and delete the contents if they match.

    Just use this code,

    For Each cell in SomeRange
      If (cell.value = SomeTest) Then cell.ClearContents
    Answered on December 14, 2018.
    Add Comment

    Follow this solution,

      The result of a formula in a cell in Excel must be a number, text, logical (boolean) or error. There is no formula cell value type of “empty” or “blank”.

    When using NA() and ISNA(), but that may or may not really solve  issue since there is a big differrence in the way NA() is treated by other functions (SUM(NA()) is #N/A while SUM(A1) is 0 if A1 is empty).

    Answered on December 17, 2018.
    Add Comment

    Just try this answer,

     Needs one table that is the source for a graph, and did not want any blank or zero cells to produce a point on the graph.  Then need to set the graph property, select data, hidden and empty cells to “show empty cells as Gaps” (click the radio button). That’s the first step.

    Then this cells may end up with a result that need not plotted, put the formula in an IF statement with an NA() results such as =IF($A8>TODAY(),NA(), *formula to be plotted*)

    It will give the required graph with no points when an invalid cell value occurs. Of course this leaves all cells with that invalid value to read #N/A, and that’s messy.

      To clean up, select the cell that may contain the invalid value, then select conditional formatting – new rule. Select ‘format only cells that contain’ and under the rule description select ‘errors’ from the drop down box. Then under format select font – colour – white (or whatever your background colour happens to be). Click the various buttons to get out and you should see that cells with invalid data look blank (they actually contain #N/A but white text on a white background looks blank.) Then the linked graph also does not display the invalid value points.

    Answered on December 17, 2018.
    Add Comment

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.