Professor Excel

Comments 6

  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


    Can you help?


  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:


  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

    • 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.

  4. jay klein

    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.

Leave a comment