New Contributor

blocking negative numbers

I am operating on a desktop with windows 10 pro and office 365 business premium. I have created a simple excel spreadsheet forgas mileage calculations.  In column B there are an ascending succession of numbers (the auto mileage).  I have used a formula in column D to indicate, for example: in D-3 the difference between B-3 and B-2. (miles traveled since last entry)

I have formulated the entire column to do this.  The formula works great, with the small exception that when the spreadsheet is filled through row 3, it then displays in D-4 the difference between B-3 and B-4 (which is empty), thereby displaying a negative number equal to the value in B-3.  It seems as though there would be a simple solution to block the display of the negative number until such time as a value is in place at both locations that the sum is dependent upon.  I haven't been able to find it.  Can someone help me?

3 Replies

Re: blocking negative numbers

Enter this formula in D2, and copy down rows:

``=MAX(B2-N(B1),0)``

Re: blocking negative numbers

@Twifoo Thank you for your input.  I assume I replace my current formula with the one you provided. I am learning by the seat of my pants, but I would like to understand what I am doing. Can you break down the formula for me?

Re: blocking negative numbers

I assume your Column headers are in Row 1. The N function ensures that the cell directly above the current Row in Column B is converted to a number. In this case, if the header for Column B is in B1, and it is text, N converts it to 0.

The MAX function ensures that the difference that results, when B1 is subtracted from B2, will never be a negative number and the lowest it can be will be 0. Stated differently, MAX returns the maximum number between the difference (whether positive or negative) and 0.