Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am using the following formula in all my first hundred rows for column F:

=(E5-D5)*1440

This is used to calculate the time in minutes between the cells in column E and column D. I have this formula applied to every row. The formula works however I have rows where the E and D columns are empty. For these Excel displays a 0 in column f.

Is there a way I can make a number format show nothing if the value of the cell is zero? Note that I still want to keep this as a number formatted cell as later on I sum up all the values in column f and use that in another part of the spreadsheet.

share|improve this question

migrated from stackoverflow.com Jan 18 '13 at 13:17

This question came from our site for professional and enthusiast programmers.

4 Answers 4

Yes, with a custom format like this:

0;-0;

The pattern for a zero is the 3rd argument (which in your case is blank), to show for example an x in place of zero, use this:

0;-0;x
share|improve this answer

If there are circumstances where zero is a legitimate result (for example when E5 and D5 are not blank but E5 = D5) then rather than format to show zero as blank you could alter the formula to return a blank, e.g.

=IF(COUNT(D5,E5)=2,(E5-D5)*1440,"")

That will return a blank unless both D5 and E5 are numeric (e.g. time values)

You can still use SUM function on the results, blanks will be ignored

share|improve this answer

Try NOT(ISBLANK(E5)) and NOT(ISBLANK(D5))

share|improve this answer

Try =if(((E5-D5)*1440)=0," ",((E5-D5)*1440))

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.