0

I am trying to return a 1 if two cells each contain a 10...unfortunately unless I resize my table it is returning a 10 for cells that only contain formula.

I can make it work (i.e. leave it blank when area1change doesn't contain a value, and return 1 if Star 2-score 1 contains 10) for this formula but can't seem to get it right if I want to add that one more cell also has to contain 10:

=IF(AND(ISNUMBER([@[area 1 change]])),IF([@[Star 2 - Score 1]]=10, 1,0),"")
1
  • You want 1 if both [@[area 1 change]] and [@[Star 2 - Score 1]] equal 10? – Mark Fitzgerald Feb 6 '19 at 11:28
0
=AND(ISNUMBER[@[AREA 1 CHANGE]],[@[STAR 2- SCORE 1]]=10,[SOME OTHER REFERENCE]=10)*1

Just use AND function and seperate each condition you want to check with a ,. When all conditions are TRUE, AND will return true. If any condition is FALSE, AND will return FALSE.

In excel, when the boolean result of TRUE/FALSE is sent through a math operation such as --, -, +, /, *, TRUE is converted to 1 and FALSE is converted to 0. Therefore perform a math operation on the the AND function that does not change its value and you will wind up with either 1 or 0.

0

Anna, Your Formula needs little correction and It should written like show below.

=IF(AND(Table1[@Qty1],Table1[@Qty2]=10),1,0)

enter image description here

Actually AND checks multiple conditions and returns True if all condition evaluates True.

And When warped with IF then IF verifies whether conditions is met or not, if met then works with True option, and the Syntax of command is,

=IF(and(Condition1, Condition2), True option, False option).

If you are stick with ISNUMBER then you Formula should written like show below.

=IF(AND(ISNUMBER(Table1[@Qty1]),Table1[@Qty1]=10),IF(AND(ISNUMBER(Table1[@Qty2]),Table1[@Qty2]=10),1,0),0)

N.B.

Check at last the Formula has 0, which replaces FALSE with Zero for Row 2 and 5, since both doesn't have 10 for either Qty1 or Qty2.

Adjust cell/Field reference as needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.