blocking negative numbers

%3CLINGO-SUB%20id%3D%22lingo-sub-2535500%22%20slang%3D%22en-US%22%3Eblocking%20negative%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2535500%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20operating%20on%20a%20desktop%20with%20windows%2010%20pro%20and%20office%20365%20business%20premium.%20I%20have%20created%20a%20simple%20excel%20spreadsheet%20forgas%20mileage%20calculations.%26nbsp%3B%20In%20column%20B%20there%20are%20an%20ascending%20succession%20of%20numbers%20(the%20auto%20mileage).%26nbsp%3B%20I%20have%20used%20a%20formula%20in%20column%20D%20to%20indicate%2C%20for%20example%3A%20in%20D-3%20the%20difference%20between%20B-3%20and%20B-2.%20(miles%20traveled%20since%20last%20entry)%3C%2FP%3E%3CP%3EI%20have%20formulated%20the%20entire%20column%20to%20do%20this.%26nbsp%3B%20The%20formula%20works%20great%2C%20with%20the%20small%20exception%20that%20when%20the%20spreadsheet%20is%20filled%20through%20row%203%2C%20it%20then%20displays%20in%20D-4%20the%20difference%20between%20B-3%20and%20B-4%20(which%20is%20empty)%2C%20thereby%20displaying%20a%20negative%20number%20equal%20to%20the%20value%20in%20B-3.%26nbsp%3B%20It%20seems%20as%20though%20there%20would%20be%20a%20simple%20solution%20to%20block%20the%20display%20of%20the%20negative%20number%20until%20such%20time%20as%20a%20value%20is%20in%20place%20at%20both%20locations%20that%20the%20sum%20is%20dependent%20upon.%26nbsp%3B%20I%20haven't%20been%20able%20to%20find%20it.%26nbsp%3B%20Can%20someone%20help%20me%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2535500%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2535628%22%20slang%3D%22en-US%22%3ERe%3A%20blocking%20negative%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2535628%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F973427%22%20target%3D%22_blank%22%3E%40LifeSupportAlliance%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEnter%20this%20formula%20in%20%3CSTRONG%3ED2%3C%2FSTRONG%3E%2C%20and%20copy%20down%20rows%3A%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DMAX(B2-N(B1)%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2539463%22%20slang%3D%22en-US%22%3ERe%3A%20blocking%20negative%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2539463%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3BThank%20you%20for%20your%20input.%26nbsp%3B%20I%20assume%20I%20replace%20my%20current%20formula%20with%20the%20one%20you%20provided.%20I%20am%20learning%20by%20the%20seat%20of%20my%20pants%2C%20but%20I%20would%20like%20to%20understand%20what%20I%20am%20doing.%20Can%20you%20break%20down%20the%20formula%20for%20me%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

@LifeSupportAlliance 

Enter this formula in D2, and copy down rows: 

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

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