Test Ranges (XP)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote