Results 1 to 2 of 2
  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    New York, New York, USA
    Thanked 0 Times in 0 Posts

    Test Ranges (XP)

    Hi All,

    I have a series of 7 cells that either contain TRUE or FALSE. I would like one cell that contained TRUE if and only if all of the other cells are TRUE. I'm thinking of an XOR type function. Does one exist in excel?

    Also, I have another group of cells that contain percentages. I would like another cell that contained the sum of the absolute value of these cells less 100%. Effectively, I would like to tell the total absolute value difference between these cells and 100%. Again, is there an excel function that can supply this?
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: Test Ranges (XP)

    1. If the cells are A1:A7, the simple formula =AND(A1:A7) will return TRUE if all cells in A1:A7 are true, FALSE if at least one of them is false. This formula ignores empty cells.

    2. If the cells with percentages are in B1:B7, use the formula =SUM(ABS(B1:B7-100%)) entered as an array formula, that is, confirmed with Ctrl+Shift+Enter instead of just Enter. You will have to format the cell containing the formula as percentage too, if you want that.

Posting Permissions

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