Announcement

Collapse
No announcement yet.

Missing IFS criteria

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Missing IFS criteria



    Hi,

    In regard to the attachment, I would like to know what is the missing formula part (see the end of the formula syntax) that will show me 6 as result in D6.

    The nights' count in column D are based on the from/to days in D1 and D2. It is about rooms occupancy.

    Cheers!


  • #2
    Please attach your file to the post, not a link to a Cloud storage.

    If the file is removed then it makes the help nonsense to future viewers looking for similar help.
    Hope that Helps

    Roy

    New users should read the Forum Rules before posting

    For free Excel tools & articles visit my web site

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

    Comment


    • #3
      Originally posted by royUK View Post
      Please attach your file to the post, not a link to a Cloud storage.

      If the file is removed then it makes the help nonsense to future viewers looking for similar help.
      Hi, I had upload issue. Retrying. forum.xlsx

      Comment


      • #4
        You can't get a correct answer in D6 because there is no end datae in C6.

        What are all the conditions for. I often calculate occupancy and it is simply End Date - Start Date. What may affect it is if the you are counting nights.
        Hope that Helps

        Roy

        New users should read the Forum Rules before posting

        For free Excel tools & articles visit my web site

        RoyUK's Web Site

        royUK's Database Form

        Where to paste code from the Forum

        About me.

        Comment


        • #5
          Originally posted by royUK View Post
          You can't get a correct answer in D6 because there is no end datae in C6.

          What are all the conditions for. I often calculate occupancy and it is simply End Date - Start Date. What may affect it is if the you are counting nights.
          Hi RoyUK,

          And this what I am pursuing to achieve. No end date means tenant is still renting, thus I need a formula that detects empty cell and subtracts D1 from B6

          Comment


          • #6
            I think this is what you mean

            =IF(ISBLANK(C6),B6-$D$1,C6-B6)

            The only problem is that your start date in B6 is before D1, hence a minus number.
            Attached Files
            Hope that Helps

            Roy

            New users should read the Forum Rules before posting

            For free Excel tools & articles visit my web site

            RoyUK's Web Site

            royUK's Database Form

            Where to paste code from the Forum

            About me.

            Comment


            • #7
              Originally posted by royUK View Post
              I think this is what you mean

              =IF(ISBLANK(C6),B6-$D$1,C6-B6)

              The only problem is that your start date in B6 is before D1, hence a minus number.
              Hi Roy,

              I had to modify a bit your suggestion and it works. However, when I copy and paste it by removing the +if in another sheet, it keeps throwing a high minus number.

              I reattach the previous attachment with the working formula (refer to E6) and the screenshot of what I did in the other sheet. forum.xlsx

              In the screenshot just refer to the red arrow and to the formula text highlighted in yellow.

              Click image for larger version

Name:	forum2.PNG
Views:	5
Size:	88.8 KB
ID:	1221668

              Comment


              • #8
                You don't need the plus sign in Excel formulas as you have used it.

                I think you need to use OR, not AND. If you use AND the formula requires all the criteria to be true, so will return FALSE when that is not the case.

                =IF(OR(ISBLANK(C7),B7>=$D$2,B7<$D$1),$D$1-B7)

                I still don't see why you are using the two fixed dates
                Hope that Helps

                Roy

                New users should read the Forum Rules before posting

                For free Excel tools & articles visit my web site

                RoyUK's Web Site

                royUK's Database Form

                Where to paste code from the Forum

                About me.

                Comment


                • #9


                  Hi Roy,

                  I had tried with the OR before but not working.

                  The two past fixed dates (FROM and TO) are the timeframe I want to know the occupancy of the rooms. I have adjusted the attachment to a real scenario and the empty cell filled with yellow is still the only one where I am missing the formula.

                  Thanks a lot for your efforts!!!! forum.xlsx

                  Comment

                  Working...
                  X