Why does simple Summation give #VALUE!?

**Zarathrustra** · 10-28-2013, 08:13 PM

I am getting the error message "#VALUE!" when I attempt a simple summation - either using SUM() or direct cell addition A1+B1+C1+D1 with some blanks in some of the cells (see sample spreadsheet) - but only in some rows

I suspect there's something odd about the cells containing the numbers or blanks, but I cannot see it.

I fear it is something simple, but I've tried for some time to find what's wrong and failed. I would appreciate a fresh pair of eyes to look and hopefully tell me what I'm doing wrong.

**dredwolf** · 10-28-2013, 08:21 PM

all the SUM() formulas work okay, you get the value error there when you try to negate a blank in column D, in the straight forward addition, you get the value error for trying to use blanks as numbers (this is why SUM() works better, it ignores blanks, unless of course, you try to do a operation on a value within the sum function that is not a value

)

Hope this helps

**FDibbins** · 10-28-2013, 08:25 PM

Im not sure what is in those cells, but they are not blank. If you select A1:C1 and delete, the formulas all work.

Where is this data comimg from...downloaded from somewhere?

**dredwolf** · 10-28-2013, 08:31 PM

:@ FDibbins, just did as you sugessted, and yes, the simple additions work, as long as there is a value in D, but the SUMS() all go #value, at least for me, any Dcolumn value that is Blank, still returns #value here...weird...

Edit
Now some of them work, some do not...head scratching here

**FDibbins** · 10-28-2013, 08:35 PM

I deleted A1:C2, but did not touch row 3...

	A	B	C	D	E	F	G
1				0.0121	-0.0030	-0.00303	-0.0121
2				0.0056	-0.0014	-0.00141	-0.0056
3			0.0083		#VALUE!	#VALUE!	#VALUE!

**dredwolf** · 10-28-2013, 08:39 PM

Yes, I'm getting some weird results to now, the blanks in D column are definitely a problem (you can't negate "", I checked out row 3 with the evaluate formula tool, and that was what it showed was happening) but some of the others have me stumped yet...

**FDibbins** · 10-28-2013, 08:44 PM

I am going to throw this into the "call in the cav" thread, because I cant see what in there would cause this

**dredwolf** · 10-28-2013, 08:55 PM

Okay, if you delete the D column blanks,the sum works (ie, "" is invalid, but null is acceptable), so it appears that the results are coming from a formula that turns the cell to "", Iwould make the blank cells be 0, then use formatting to make them appear as "" (ie custom format #.####;;

)
that way both the sum and simple addition are guaranteed to have values to work with..I hope

Edit-
at least make the D column values = 0, as per first post, you can not negate a Blank ("")

**martindwilson** · 10-28-2013, 09:12 PM

im not sure either but select a1:d16
use find replace
find ... leave blank
replace with use say @
replace all
then find @ replace all with nothing

**dredwolf** · 10-28-2013, 09:15 PM

Thanks Martin much appreciated !

**Zarathrustra** · 10-29-2013, 03:42 AM

Originally Posted by dredwolf

all the SUM() formulas work okay, you get the value error there when you try to negate a blank in column D, in the straight forward addition, you get the value error for trying to use blanks as numbers (this is why SUM() works better, it ignores blanks, unless of course, you try to do a operation on a value within the sum function that is not a value

)

Hope this helps

Thanks. That does indeed explain why col.E has "#VALUE!" and will enable me to go forward. However, it does not explain cols F & G

**Zarathrustra** · 10-29-2013, 03:47 AM

All this data comes from an earlier part in my spreadsheet, but I've done a 'Values & Source Formatting' paste, so there are only numbers and blanks transferred into this sample spreadsheet.

**Zarathrustra** · 10-29-2013, 03:51 AM

Thanks everyone for your exploration of my problem and your responses. I'm glad that you found that there is indeed something odd going on. However, as I've said in my first response, it seems that if I can do an IF and get the negative blank of D out of the way, I can do what I want. Thanks again.

Why does simple Summation give #VALUE!?

LinkBack

Thread Tools

Rate This Thread

Display

Why does simple Summation give #VALUE!?

Re: Why does simple Summation give #VALUE!?

Re: Why does simple Summation give #VALUE!?

Re: Why does simple Summation give #VALUE!?

Re: Why does simple Summation give #VALUE!?

Re: Why does simple Summation give #VALUE!?

Re: Why does simple Summation give #VALUE!?

Re: Why does simple Summation give #VALUE!?

Re: Why does simple Summation give #VALUE!?

Re: Why does simple Summation give #VALUE!?

Re: Why does simple Summation give #VALUE!?

Re: Why does simple Summation give #VALUE!?

Re: Why does simple Summation give #VALUE!?

Thread Information

Users Browsing this Thread

Similar Threads

Using a summation

Relatively simple summation program

Simple summation problem

Give RELEVANT responses to questions. DO NOT give usless list

How use summation?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions