+ Reply to Thread
Results 1 to 4 of 4

Conditionally ignoring certain cells in columnar calculations

  1. #1
    Damien
    Guest

    Conditionally ignoring certain cells in columnar calculations

    It's probably easier if I ask this using an example, so please reference this
    sample:

    A B

    1 0.3 0.2
    2 0.4 0.3
    3 0.5 0.4
    4 # N/A N/A 0.5

    I use a Bloomberg add-in to automatically load data into Excel. If for some
    reason this data is not available, the add-in will return a value such as the
    one seen in cell A4.

    What I'm trying to do is sum these numbers and calculate the percentage
    change from the sum of column B to the sum of column A. However, calculating
    the percentage change in three quarters' worth of data over four is useless,
    so I'm trying to find a way (SUMIF?) to exclude cell B4 from the sum
    calculation of column B if the adjacent cell (A4) is blank or contains an
    error code like the one shown above. Is this possible?

    Thanks so much for the help!

  2. #2
    Elkar
    Guest

    RE: Conditionally ignoring certain cells in columnar calculations

    It's not pretty, but this should get you the sum of column B that you want:

    =SUMPRODUCT(--NOT(ISERROR(A1:A4)),--NOT(ISBLANK(A1:A4)),B1:B4)

    HTH,
    Elkar

    "Damien" wrote:

    > It's probably easier if I ask this using an example, so please reference this
    > sample:
    >
    > A B
    >
    > 1 0.3 0.2
    > 2 0.4 0.3
    > 3 0.5 0.4
    > 4 # N/A N/A 0.5
    >
    > I use a Bloomberg add-in to automatically load data into Excel. If for some
    > reason this data is not available, the add-in will return a value such as the
    > one seen in cell A4.
    >
    > What I'm trying to do is sum these numbers and calculate the percentage
    > change from the sum of column B to the sum of column A. However, calculating
    > the percentage change in three quarters' worth of data over four is useless,
    > so I'm trying to find a way (SUMIF?) to exclude cell B4 from the sum
    > calculation of column B if the adjacent cell (A4) is blank or contains an
    > error code like the one shown above. Is this possible?
    >
    > Thanks so much for the help!


  3. #3
    Elkar
    Guest

    RE: Conditionally ignoring certain cells in columnar calculations

    It's not pretty, but this should get you the sum of column B that you want:

    =SUMPRODUCT(--NOT(ISERROR(A1:A4)),--NOT(ISBLANK(A1:A4)),B1:B4)

    HTH,
    Elkar

    "Damien" wrote:

    > It's probably easier if I ask this using an example, so please reference this
    > sample:
    >
    > A B
    >
    > 1 0.3 0.2
    > 2 0.4 0.3
    > 3 0.5 0.4
    > 4 # N/A N/A 0.5
    >
    > I use a Bloomberg add-in to automatically load data into Excel. If for some
    > reason this data is not available, the add-in will return a value such as the
    > one seen in cell A4.
    >
    > What I'm trying to do is sum these numbers and calculate the percentage
    > change from the sum of column B to the sum of column A. However, calculating
    > the percentage change in three quarters' worth of data over four is useless,
    > so I'm trying to find a way (SUMIF?) to exclude cell B4 from the sum
    > calculation of column B if the adjacent cell (A4) is blank or contains an
    > error code like the one shown above. Is this possible?
    >
    > Thanks so much for the help!


  4. #4
    Damien
    Guest

    RE: Conditionally ignoring certain cells in columnar calculations

    That did it. Thanks for the help!

    "Elkar" wrote:

    > It's not pretty, but this should get you the sum of column B that you want:
    >
    > =SUMPRODUCT(--NOT(ISERROR(A1:A4)),--NOT(ISBLANK(A1:A4)),B1:B4)
    >
    > HTH,
    > Elkar
    >
    > "Damien" wrote:
    >
    > > It's probably easier if I ask this using an example, so please reference this
    > > sample:
    > >
    > > A B
    > >
    > > 1 0.3 0.2
    > > 2 0.4 0.3
    > > 3 0.5 0.4
    > > 4 # N/A N/A 0.5
    > >
    > > I use a Bloomberg add-in to automatically load data into Excel. If for some
    > > reason this data is not available, the add-in will return a value such as the
    > > one seen in cell A4.
    > >
    > > What I'm trying to do is sum these numbers and calculate the percentage
    > > change from the sum of column B to the sum of column A. However, calculating
    > > the percentage change in three quarters' worth of data over four is useless,
    > > so I'm trying to find a way (SUMIF?) to exclude cell B4 from the sum
    > > calculation of column B if the adjacent cell (A4) is blank or contains an
    > > error code like the one shown above. Is this possible?
    > >
    > > Thanks so much for the help!


+ Reply to 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