Announcement

Collapse
No announcement yet.

Formulas: Leave a cell blank using an if condition

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

  • Formulas: Leave a cell blank using an if condition

    using an =IF condition, I want to populate the cell with the results of a formula if TRUE and leave the cell blank if FALSE.

    I can't seem to make the cell blank if FALSE, it ends up as either Zero or FALSE.

    Any help would be greatly appreciated.

  • #2
    Try this -

    =IF(condition, formula, "")

    the "" is 2 double quotes together.

    example =IF(A1=0, B1+B2, "")

    ....Ralph

    Comment


    • #3
      Hi Ralph,

      Thanks for the suggestion, unfortunately it didn't work. To double check it I type "" (no space) into a cell and then did a ISBLANK test on the cell. It came back as FALSE, i.e. even though there appeared to be nothing in the cell, it wasn't blank.

      Thanks again.

      Comment


      • #4
        Welcome to the forum!

        You are correct, Ralph's formula will give the appearance of a blank cell. That is the best you will get without going the VBA route though. The IF formula itself still resides in the cell whether it is true or false. To evaluate something and enter the result (if true) or nothing (if false) into another location requires some VBA coding.

        Is this what you were needing to do?

        HTH
        Brandtrock

        Brandtrock Consulting | Brandtrock Files | ISU Athletics | Bricktown | VBA Express

        Comment


        • #5
          Hi Brandtrock,

          Thanks for the feedback and sorry for the delay in responding, I've been on sick leave. I've been putting off the VBA path as it's uncharted territory for me, however, now I have no choice I'll get stuck in.

          Thanks again.

          Comment


          • #6
            If you want to stay away from VBA, and want to evaluate if the cell is "blank" don't use "ISBLANK" just use the "" that the cell makes.

            For example, you could do an if that says:

            C2 =IF(Some formula, your true value, "")

            D2 =IF(C2="", do something, else do something else)

            It will SHOW as blank, and in this situation, it will evaluate like you would need. You can use those double quotation marks quite easily. Just make sure that you don't put a space in, doing so will throw the formulas off.

            Hope that helps some! I also stay away from VBA if I can help it, so I know what you mean.

            -Scy

            Comment


            • #7
              Hi Scy,

              Appreciate waht you say and normally this would work fine, however, in this instance I am generating a graph. It's hard to illustrate what is required in this forum but I'll give it a try.

              I'm plotting figures for a graph, however, I only want to plot figures for the current date forward. I was trying to use a condition on each data cell to say if less than current date leave blank else calculate the data.

              The "" is interpruted as 0 in the graph so I end up with a line being plotted for all periods but the previous periods are all zero which is misleading.

              I reckon I just have to bite the bullet and try the VBA path.

              Thanks for your help and that of others in this forum. This is the first time I've used a forum like this an I'm really impressed with how helpful people are.

              Cheers,
              G.

              Comment


              • #8
                Why not use a dynamic range for your graph series, and only graph/plot data for dates >Today's date - is this an option?

                If so, I can give you an example or two...
                Kind Regards, Will Riley

                LinkedIn: Will Riley

                Comment


                • #9
                  I didn't know that was possible. I'd be very interested to see an example. I don't if I could apply it to my situation but there's only one way to find out.

                  Thank you.

                  Comment


                  • #10
                    Whether you go the dynamic route or not in this case, keep in mind that the return of the NA function does not plot, so you can use formulas such as =IF(condition, plotvalue,NA())
                    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

                    Comment


                    • #11
                      Originally posted by gwhitley
                      I didn't know that was possible. I'd be very interested to see an example. I don't if I could apply it to my situation but there's only one way to find out.

                      Thank you.
                      OK, I will try to post something up later
                      Kind Regards, Will Riley

                      LinkedIn: Will Riley

                      Comment

                      Working...
                      X