#Value error on a simple formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1680571%22%20slang%3D%22en-US%22%3E%23Value%20error%20on%20a%20simple%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1680571%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20columns%20of%20numbers.%20(Debits%20%26amp%3B%20credits).%20I%20need%20to%20net%20these%20columns%20using%20a%20simple%20x-y%20formula.%20For%20every%20row%20one%20cell%20has%20a%20positive%20number%20and%20cell%20that%20is%20blank.%20I%20have%20one%20workbook%20that%20is%20returning%20%23value%20instead%20of%20a%20number.%20If%20I%20enter%20a%200%20in%20the%20blank%20cell%20I%20get%20a%20result.%20How%20do%20I%20get%20excel%20to%20register%20the%20blank%20cells%20as%200's%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1680571%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1680698%22%20slang%3D%22en-US%22%3ERe%3A%20%23Value%20error%20on%20a%20simple%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1680698%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F797879%22%20target%3D%22_blank%22%3E%40samd685%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20calculations%20blank%20cell%20is%20considered%20as%20one%20having%20zero%20value%2C%20other%20words%20returns%200.%20Perhaps%20you%20have%20not%20blank%20cells%2C%20but%20cells%20with%20empty%20string%2C%20when%20you%20have%20an%20error.%20Usually%20formulas%20return%20empty%20string%20instead%20of%20blank%2C%20like%20%3DIF(A1%26gt%3BB1%2C%20A1*10%2C%20%22%22)%3C%2FP%3E%0A%3CP%3EAs%20workaround%20instead%20of%3C%2FP%3E%0A%3CP%3E%3DA1%2BB1%3C%2FP%3E%0A%3CP%3Eyou%20may%20use%3C%2FP%3E%0A%3CP%3E%3DSUM(A1%3AB1)%20or%20%3DSUM(A1%2CB1)%3C%2FP%3E%0A%3CP%3ESUM()%20ignores%20all%20texts.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1681975%22%20slang%3D%22en-US%22%3ERe%3A%20%23Value%20error%20on%20a%20simple%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1681975%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F797879%22%20target%3D%22_blank%22%3E%40samd685%3C%2FA%3E%26nbsp%3B%2C%2C%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20%23VALUE!%20error%20is%20a%26nbsp%3Bcommon%20issue%2C%20and%20possible%20reasons%20are%2C%2C%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EValue%20is%20other%20than%20expected%20type.%3C%2FLI%3E%3CLI%3ECells%20is%2Fare%20left%20blank.%3C%2FLI%3E%3CLI%3EFormula%20encounters%20with%20literally%20a%20text%20value.%3C%2FLI%3E%3CLI%3EDates%20are%20evaluated%20as%20text%20by%20Excel.%3C%2FLI%3E%3C%2FOL%3E%3CP%3EFixing%20needs%20entering%20the%20right%20kind%20of%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBelow%20I've%20shown%20a%20sample%20data%20finding%20how%20to%20calculate%20RUNNING%20TOTAL%20while%20handling%20Debit%20%26amp%3B%20Credit%20amount.%3C%2FP%3E%3CP%3E%26nbsp%3B%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-1600407198535.png%22%20style%3D%22width%3A%20433px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219441i4931A32F3D799A9E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Rajesh-S_0-1600407198535.png%22%20alt%3D%22Rajesh-S_0-1600407198535.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EN.B.%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFormula%20In%20cell%20E81%26nbsp%3B%3D(B81%2BD80)-C81%20%2C%20encounters%20with%20%23VALUE%20error%20in%20Cell%20E84%2C%20reason%20is%20that%20the%20cell%20C84%20has%20TEXT%20value%20.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20to%20trap%20the%20error%20I've%20used%20correct%20formula%20in%20cell%20D81%20is%2C%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFERROR(B81%2BD80-C81%2CD80)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I have two columns of numbers. (Debits & credits). I need to net these columns using a simple x-y formula. For every row one cell has a positive number and cell that is blank. I have one workbook that is returning #value instead of a number. If I enter a 0 in the blank cell I get a result. How do I get excel to register the blank cells as 0's?

2 Replies
Highlighted

@samd685 

In calculations blank cell is considered as one having zero value, other words returns 0. Perhaps you have not blank cells, but cells with empty string, when you have an error. Usually formulas return empty string instead of blank, like =IF(A1>B1, A1*10, "")

As workaround instead of

=A1+B1

you may use

=SUM(A1:B1) or =SUM(A1,B1)

SUM() ignores all texts.

Highlighted

@samd685 ,,,

 

The #VALUE! error is a common issue, and possible reasons are,,,

 

  1. Value is other than expected type.
  2. Cells is/are left blank.
  3. Formula encounters with literally a text value.
  4. Dates are evaluated as text by Excel.

Fixing needs entering the right kind of value.

 

Below I've shown a sample data finding how to calculate RUNNING TOTAL while handling Debit & Credit amount.

 

 

Rajesh-S_0-1600407198535.png

 

N.B. 

 

Formula In cell E81 =(B81+D80)-C81 , encounters with #VALUE error in Cell E84, reason is that the cell C84 has TEXT value .

 

Now to trap the error I've used correct formula in cell D81 is,, 

 

=IFERROR(B81+D80-C81,D80)