Forum Discussion
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?
- 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.
3 Replies
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?
- 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.
