Professor Excel

Comments 14

  1. Stuart

    Hi Henri can you help me with what is I’m sure a simple problem.
    I have text in column A which is one of 4 options. In a related row I have times. I want a function to find the longest time in each text option. An example would be:

    A 01:30
    B O2:20
    A. 02:05
    C. 01:31

    Etc

    Can you help?

    Stuart

  2. Dom

    Options 2 and 3 are seriously ugly solutions. By far the most elegant approach is to concatenate a null string to the lookup result, thus forcing the result to a string:

    =””&VLOOKUP(A3,C:D,2,FALSE)

  3. Ali

    Option 3: Show zeroes but don’t show empty return values
    sir this formula not working,
    show zeros but a same time show the empty return values. please fix this problem

  4. Topher

    How is Option 3 any different from just using the original formula?

    • Henrik Schiffner

      If the value in your original formula is blank, the original formula would (without the if-formula according to number 3) return 0. Using option 3 changes it to blank again.
      You can easily try it by just using a cell reference, for example writing =B1 in cell A1. If you leave B1 blank, A1 would show 0. Using the option 3 would show a blank cell A1.

  5. jay klein

    greetings
    I have a sumif formula applied to a column that has blanks in it, and in some cases may have ALL blanks. I need to know that when I see a zero it is a ZERO, not a blank being displayed as a zero. I have tried everything.

  6. mll

    @Dom: your trick is great, as long as we only need to prevent zeroes to display.

    but if I need to do for example
    =VLOOKUP(A3,C:D,2,FALSE)<1

    cases where VLOOKUP(A3,C:D,2,FALSE) = “” will display TRUE instead of the desired blank value

    If we do
    =””&VLOOKUP(A3,C:D,2,FALSE)<1, it will always display FALSE whatever the case.

    So finally we have no choice but to revert to the – ugly, I concur – suggestion of the article, which is
    =IF(VLOOKUP(A3,C:D,2,FALSE)=””;””;VLOOKUP(A3,C:D,2,FALSE)<1)

  7. Erik

    Wouldn´t ORIGINAL FORMULA&”” work as well?
    That displays the cell as empty if the value is 0

  8. Hi, I am using a simple if statement =IF(G2="L",1, ) and I dont want 0 as a false value. I am fine to get a blank cell when False. Please help.

    Hi, I am using a simple if statement =IF(G2=”L”,1, ) and I dont want 0 as a false value. I am fine to get a blank cell when False. Please help.

  9. Vester

    Option 2 worked for me. It’s so simple and useful. Thank you so much!!

  10. Rishie

    Hi Henrik!

    I have been using something like this: =If( [condition] , [result if true] , “”)

    This formula gives me blanks in case the condition is false and works good for me as it ‘shows’ cells as blanks. But in reality it has some special character which gets copied when I need to. How can I get rid of these special characters in the cell as I will need to keep it like this. Can we use any other option to make it ‘actual’ blank?

    Thanks
    Mohit

  11. Damir

    I have the same problem.
    When the “” result cell value is copied to another workbook as value it is not empty.
    The cell shows as populated and messes up calculations, for example the average of that column is #Value!
    It would be better to use some letter instead of “” as it would not interfere down the line.

Leave a comment

%d bloggers like this: