Isnumber and two other requirements





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}







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),"")









share|improve this question

























  • You want 1 if both [@[area 1 change]] and [@[Star 2 - Score 1]] equal 10?

    – Mark Fitzgerald
    Feb 6 at 11:28




















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),"")









share|improve this question

























  • You want 1 if both [@[area 1 change]] and [@[Star 2 - Score 1]] equal 10?

    – Mark Fitzgerald
    Feb 6 at 11:28
















0












0








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),"")









share|improve this question
















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),"")






microsoft-excel






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Feb 6 at 12:05









Ahmed Ashour

1,3872716




1,3872716










asked Feb 6 at 11:14









AnnaAnna

1




1













  • You want 1 if both [@[area 1 change]] and [@[Star 2 - Score 1]] equal 10?

    – Mark Fitzgerald
    Feb 6 at 11:28





















  • You want 1 if both [@[area 1 change]] and [@[Star 2 - Score 1]] equal 10?

    – Mark Fitzgerald
    Feb 6 at 11:28



















You want 1 if both [@[area 1 change]] and [@[Star 2 - Score 1]] equal 10?

– Mark Fitzgerald
Feb 6 at 11:28







You want 1 if both [@[area 1 change]] and [@[Star 2 - Score 1]] equal 10?

– Mark Fitzgerald
Feb 6 at 11:28












2 Answers
2






active

oldest

votes


















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.






share|improve this answer































    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.






    share|improve this answer


























      Your Answer








      StackExchange.ready(function() {
      var channelOptions = {
      tags: "".split(" "),
      id: "3"
      };
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function() {
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled) {
      StackExchange.using("snippets", function() {
      createEditor();
      });
      }
      else {
      createEditor();
      }
      });

      function createEditor() {
      StackExchange.prepareEditor({
      heartbeatType: 'answer',
      autoActivateHeartbeat: false,
      convertImagesToLinks: true,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: 10,
      bindNavPrevention: true,
      postfix: "",
      imageUploader: {
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      },
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      });


      }
      });














      draft saved

      draft discarded


















      StackExchange.ready(
      function () {
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1402625%2fisnumber-and-two-other-requirements%23new-answer', 'question_page');
      }
      );

      Post as a guest















      Required, but never shown

























      2 Answers
      2






      active

      oldest

      votes








      2 Answers
      2






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      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.






      share|improve this answer




























        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.






        share|improve this answer


























          0












          0








          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.






          share|improve this answer













          =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.







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Feb 6 at 11:58









          Forward EdForward Ed

          1,064215




          1,064215

























              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.






              share|improve this answer






























                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.






                share|improve this answer




























                  0












                  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.






                  share|improve this answer















                  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.







                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Feb 6 at 13:05

























                  answered Feb 6 at 12:21









                  Rajesh SRajesh S

                  4,4182724




                  4,4182724






























                      draft saved

                      draft discarded




















































                      Thanks for contributing an answer to Super User!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function () {
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1402625%2fisnumber-and-two-other-requirements%23new-answer', 'question_page');
                      }
                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      Popular posts from this blog

                      Сан-Квентин

                      Алькесар

                      Josef Freinademetz