CODEWITHSUNDEEP
if-statementexcel-formula
Captain Who
Captain Who

Reputation: 35

If Statement giving value error

I am trying to compare two data ranges to determine if they are the same or not.

I'm using the following statement and I get a #Value! error message: 

=IF(SUM(ABS(B2:E7-G2:J7))=0,"Same", "Not")

Upvotes: 0

Views: 106

Answers (2)

user4039065
user4039065

Reputation:

If you are looking to see if the arithmetic total of the numbers in the two ranges is the same then this standard formula should do.

=IF(SUM(B2:E7)-SUM(G2:J7), "Not", "Same")

A zero in Excel evaluates to a boolean FALSE. Anything that is not false is TRUE.

This does not determine whether each cell directly corresponds to its 'sister' cell in the other range; only that the sum total of each is equal or not. The values in different cells could be interchanged or by coincidence be offset from one another in a perfect proportion to create an equal sum.

If you require a cell by cell analysis, then a much more complicated formula could be provided.

=IF(SUMPRODUCT(--(B2:E7=G2:J7))=24, "Same", "Not")

24 being the total number of cells in each range. While this does not require Ctrl+Shift+Enter, a SUMPRODUCT function does produce cyclic calculations.

Upvotes: 2

Robin Gertenbach
Robin Gertenbach

Reputation: 10806

Do you have strings in your answer or have the formula in row 1 or 8+? If yes that might be the reason as the formula works correctly assuming you

  • hit ctrl+shift+enter and
  • have only numbers in the ranges

For a general solution try 

{=IF(AND(B2:E7=G2:J7),"Same","Not")}

Do not forget to hit ctrl+shift+enter as otherwise it will only look at the row the formula itself is in!

Upvotes: 0

Related Questions