Excel Formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-1626070%22%20slang%3D%22en-US%22%3EExcel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1626070%22%20slang%3D%22en-US%22%3E%3CP%3EI%20made%20a%20spreadsheet%20and%20I%20added%20a%20formula%20so%20that%20one%20of%20the%20columns%20calculates%20the%20difference%20between%20numbers%20in%20two%20other%20columns.%20That%20worked%2C%20but%20it's%20a%20situation%20where%20the%20people%20I%20work%20with%20will%20add%20values%20to%20the%20spreadsheet%20over%20time%20and%20I%20don't%20want%20the%20column%20that%20will%20find%20the%20difference%20to%20calculate%20a%20negative%20number%20after%20the%20first%20column%20number%20is%20entered.%20I%20want%20the%20difference%20formula%20to%20show%20only%20when%20both%20the%20values%20are%20entered%20in%20the%20other%20columns.%20I%20think%20I%20need%20to%20add%20a%20condition%2C%20but%20I%20can't%20figure%20out%20what%20to%20put.%20Basically%20I%20want%20the%20column%20that%20will%20show%20the%20difference%20to%20wait%20to%20calculate%20until%20both%20of%20the%20other%20values%20are%20entered.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1626070%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1626087%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1626087%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F778613%22%20target%3D%22_blank%22%3E%40Mariel617%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(%20(A1%3D%22%22)%20%2B%20(B1%3D%22%22)%2C%20%22%22%2C%20B1-A1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eif%20no%20any%20other%20conditions%20to%20check.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1672627%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1672627%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F778613%22%20target%3D%22_blank%22%3E%40Mariel617%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESince%20you%20have%20not%20disclosed%20that%20what%20formula%20you%20have%20used%20for%20TOTAL%20GROWTH%2C%20therefore%20I%20would%20like%20to%20suggest%20two%20possible%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Rajesh-S_0-1600249092654.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F218715i7E3B6EE414D88F95%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Rajesh-S_0-1600249092654.png%22%20alt%3D%22Rajesh-S_0-1600249092654.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EFormula%20in%20cell%20K6%3A%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3DIF(OR(ISBLANK(H6)%2CISBLANK(I6)%2CISBLANK(J6))%2C%22%22%2C%20SUM(H6%3AJ6))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EN.B%20%3A%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CEM%3EIf%20either%20of%20cells%20in%20range%20H6%3AJ6%20is%20blank%2C%20then%20formula%20returns%20BLANK%20in%20cell%20K6.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EAnother%20formula%20in%20K6%3A%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3DIF(ISBLANK(J6)%2C%22%22%2CSUM(H6%3AJ6))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EN.B%20%3A%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CEM%3EIf%20only%20J6%20(Spring%20MAP)%20is%26nbsp%3Bblank%20then%2C%20formula%20keeps%20the%20cell%20K6%26nbsp%3B%26nbsp%3BBLANK.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ENote%3A%3C%2FSTRONG%3E%3C%2FP%3E%3COL%3E%3CLI%3EYou%20need%20to%20adjust%20cell%20references%20in%20the%20formula%20as%20needed.%3C%2FLI%3E%3CLI%3EYou%20may%20replace%20SUM%20function%20with%20any%20other%20of%20your%20need.%3C%2FLI%3E%3C%2FOL%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I made a spreadsheet and I added a formula so that one of the columns calculates the difference between numbers in two other columns. That worked, but it's a situation where the people I work with will add values to the spreadsheet over time and I don't want the column that will find the difference to calculate a negative number after the first column number is entered. I want the difference formula to show only when both the values are entered in the other columns. I think I need to add a condition, but I can't figure out what to put. Basically I want the column that will show the difference to wait to calculate until both of the other values are entered. 

2 Replies

@Mariel617 

That could be like

=IF( (A1="") + (B1=""), "", B1-A1)

if no any other conditions to check.

@Mariel617 

 

Since you have not disclosed that what formula you have used for TOTAL GROWTH, therefore I would like to suggest two possible formula.

 

Rajesh-S_0-1600249092654.png

 

  • Formula in cell K6:

=IF(OR(ISBLANK(H6),ISBLANK(I6),ISBLANK(J6)),"", SUM(H6:J6))

 

N.B : 

If either of cells in range H6:J6 is blank, then formula returns BLANK in cell K6.

 

  • Another formula in K6:

=IF(ISBLANK(J6),"",SUM(H6:J6))

 

N.B : 

If only J6 (Spring MAP) is blank then, formula keeps the cell K6  BLANK.

 

Note:

  1. You need to adjust cell references in the formula as needed.
  2. You may replace SUM function with any other of your need.