Closed Thread
Results 1 to 4 of 4

Disregarding blank cells when performing calculations

  1. #1
    Moshe
    Guest

    No replace for #Value?

    Is there no way to request #Value to be replaced with an empty string?

  2. #2
    Harager
    Guest

    Formula-error

    I found out that in above written formula there was made a mistake, it should be :

    IF(AND(ISNUMBER(A2);ISNUMBER(B2)); A2*B2; " ")

    where between ISNUMBER(A2) and ISNUMBER(B2) there should be ';' and not ','

    With friendly greetings,

    Harager

  3. #3
    Forum Contributor
    Join Date
    12-07-2004
    Posts
    596

    Disregarding blank cells when performing calculations

    Problem:

    Columns A:B contain numbers, as well as empty cells.
    How could we multiply each number in column A with the matching number in Column B without getting false results or errors?

    Solution:

    Using the ISNUMBER function will allow us to validate the values prior to the calculation.
    Thus, when failing to multiply a pair of values, the formula will return a blank cell, rather than an error or 0.
    Following is the formula:
    =IF(AND(ISNUMBER($A$2:$A$5), ISNUMBER($B$2:$B$5)),A2*B2,\"\"\"\")

    Example:


    List1___List2___Result
    2_______3_______6
    5
    8
    11______4_______44



  4. #4
    Registered User
    Join Date
    12-08-2005
    Posts
    2

    Ignoring Blank Cells when Performing Calculations

    This formula is more useful when the cell has a non numeric entry, say a text entry. In which case but for this formula it would have given a result "#VALUE!"
    Which would have meant one could not sum the value up.

    If the cell is blank, without the formula it would give a result "0", with the formula it returns with a blank text entry.

    This forumla is more appropriate termed as "ignoring text cells when performing calculations"

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1