Professor Excel

Comments 9

  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

Leave a comment

%d bloggers like this: