Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Formulas: Leave a cell blank using an if condition

  1. #1
    Join Date
    11th July 2003
    Location
    Melbourne
    Posts
    5
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th June 2003
    Location
    Maryland, USA
    Posts
    368
    Try this -

    =IF(condition, formula, "")

    the "" is 2 double quotes together.

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

    ....Ralph

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    11th July 2003
    Location
    Melbourne
    Posts
    5
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    20th May 2003
    Location
    Mesa, Az
    Posts
    1,110
    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

  5. #5
    Join Date
    11th July 2003
    Location
    Melbourne
    Posts
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    12th July 2003
    Location
    Cedar Rapids, IA
    Posts
    33
    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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    11th July 2003
    Location
    Melbourne
    Posts
    5
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,677
    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

    Web Presence:
    Personal: Datasapien
    LinkedIn: Will Riley

  9. #9
    Join Date
    11th July 2003
    Location
    Melbourne
    Posts
    5
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,331
    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())

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. If No Data Leave It Blank (cell Contains Formula)
    By JohnnyBeGood in forum EXCEL HELP
    Replies: 12
    Last Post: June 10th, 2011, 19:15
  2. Replies: 7
    Last Post: May 22nd, 2007, 04:16
  3. If Tab Exists Put Cell, If Not, Leave Blank
    By nelacat in forum EXCEL HELP
    Replies: 18
    Last Post: February 26th, 2007, 11:30
  4. If Cell has Inactive Formula Leave Blank
    By Upside in forum EXCEL HELP
    Replies: 2
    Last Post: January 15th, 2007, 13:01
  5. Leave Loop When Condition Met
    By shevtsov in forum EXCEL HELP
    Replies: 6
    Last Post: December 2nd, 2006, 00:03

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno