e.g., if A7 is the sum of A1:A5 but there is no character in cell A2, how can
I avoid the #Value! error without having to manually type in a "0" value. In
other words, the calculation does not recognize the blank cell as an
automatic "0" value.

I appreciate your help.

hi
if your are using the formula =A1:A5, you will get the value error. try this
=sum(A1:A5)

that should work for you'
regards
FSt1

"phowe43" wrote:

e.g., if A7 is the sum of A1:A5 but there is no character in cell A2, how can
I avoid the #Value! error without having to manually type in a "0" value. In
other words, the calculation does not recognize the blank cell as an
automatic "0" value.

I appreciate your help.

Hi,

"A7 is the sum of A1:A5 "

I think you had better show us the actual formula that is returning #VALUE
since the SUM function will not do this if a cell in the sum range is empty.
SUM does treat an empty cell as 0.

--
Thanks,
Shane Devenshire


"phowe43" wrote:

e.g., if A7 is the sum of A1:A5 but there is no character in cell A2, how can
I avoid the #Value! error without having to manually type in a "0" value. In
other words, the calculation does not recognize the blank cell as an
automatic "0" value.

I appreciate your help.

If there is no character in A2, you won't get a #VALUE! error from
=SUM(A1:A5). Similarly you won't get that error from the SUM function when
A2 contains a space =" ", or even normally troublesome invisible characters
such as CHAR(160), but in those cases you will get a #VALUE! error from
=A1+A2+A3+A4+A5.
I suggest you use =LEN(A2) to see how long a string you've got in A2, and
then you can use =CODE(MID(A2,1,1)) to =CODE(MID(A2,n,1)) to see the ANSI
code for each of your n characters.
--
David Biddulph

"phowe43" wrote in message
...
e.g., if A7 is the sum of A1:A5 but there is no character in cell A2, how
can
I avoid the #Value! error without having to manually type in a "0" value.
In
other words, the calculation does not recognize the blank cell as an
automatic "0" value.

I appreciate your help.

Hi,

Check the format of cell A2 ...
You should not get #Value in your sum formula ...

HTH

Excel will ignore any empty cell (or any cell with text) in a formula like:

=sum(a1:a5)

So maybe you have an error in one of those cells.


phowe43 wrote:

e.g., if A7 is the sum of A1:A5 but there is no character in cell A2, how can
I avoid the #Value! error without having to manually type in a "0" value. In
other words, the calculation does not recognize the blank cell as an
automatic "0" value.

I appreciate your help.

--

Dave Peterson

Sat, 5 Jan 2008 09:02:01 -0800 from phowe43 <phowe43
@discussions.microsoft.com:
e.g., if A7 is the sum of A1:A5 but there is no character in cell A2, how can
I avoid the #Value! error without having to manually type in a "0" value. In
other words, the calculation does not recognize the blank cell as an
automatic "0" value.

There's something you're not telling us. Excel ignores empty cells
when calculating SUM.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
"If there's one thing I know, it's men. I ought to: it's
been my life work." -- Marie Dressler, in /Dinner at Eight/

Thanks all...it's a lengthy formula and there may have been an easier way to
do it but I'm somewhat a novice. So here's the formula.

=(C8*'Data Tables'!B3+'Driver Input Form'!D8*'Data Tables'!C3+'Driver Input
Form'!E8*'Data Tables'!D3+'Driver Input Form'!F8*'Data Tables'!E3+'Driver
Input Form'!G8*'Data Tables'!F3+'Driver Input Form'!H8*'Data
Tables'!G3+'Driver Input Form'!I8*'Data Tables'!H3)

So what happens is that C8 - D8 - E8 (etc.), are referencing a table with
defined values from a different page...and then I'm simply trying to total
the values. Again what happens is that unless there is some value (including
a zero) in the cell (C8 - D8 etc.), I get the #Value! error.

Thanks again.

"ShaneDevenshire" wrote:

Hi,

"A7 is the sum of A1:A5 "

I think you had better show us the actual formula that is returning #VALUE
since the SUM function will not do this if a cell in the sum range is empty.
SUM does treat an empty cell as 0.

--
Thanks,
Shane Devenshire


"phowe43" wrote:

e.g., if A7 is the sum of A1:A5 but there is no character in cell A2, how can
I avoid the #Value! error without having to manually type in a "0" value. In
other words, the calculation does not recognize the blank cell as an
automatic "0" value.

I appreciate your help.

Well that's a very different story from when you first asked the question,
but still wrong. If the cell is empty, you don't get a #VALUE! result. You
must have something in the cell, even if only a space. If you do have
something like a space, you can avoid the #VALUE! result by changing to a
SUM() function.
--
David Biddulph

"phowe43" wrote in message
...
Thanks all...it's a lengthy formula and there may have been an easier way
to
do it but I'm somewhat a novice. So here's the formula.

=(C8*'Data Tables'!B3+'Driver Input Form'!D8*'Data Tables'!C3+'Driver
Input
Form'!E8*'Data Tables'!D3+'Driver Input Form'!F8*'Data Tables'!E3+'Driver
Input Form'!G8*'Data Tables'!F3+'Driver Input Form'!H8*'Data
Tables'!G3+'Driver Input Form'!I8*'Data Tables'!H3)

So what happens is that C8 - D8 - E8 (etc.), are referencing a table with
defined values from a different page...and then I'm simply trying to total
the values. Again what happens is that unless there is some value
(including
a zero) in the cell (C8 - D8 etc.), I get the #Value! error.

Thanks again.

"ShaneDevenshire" wrote:

Hi,

"A7 is the sum of A1:A5 "

I think you had better show us the actual formula that is returning
#VALUE
since the SUM function will not do this if a cell in the sum range is
empty.
SUM does treat an empty cell as 0.

--
Thanks,
Shane Devenshire


"phowe43" wrote:

e.g., if A7 is the sum of A1:A5 but there is no character in cell A2,
how can
I avoid the #Value! error without having to manually type in a "0"
value. In
other words, the calculation does not recognize the blank cell as an
automatic "0" value.

I appreciate your help.

All right folks ... can anyone explain this to me?

*Excluding* the first calc of
C8*'Data Tables'!B3,

This allows the OP to have text and/or nulls in any of the other cells:

=SUMPRODUCT('Data Input Form'!D8:I8,'Data Tables'!C3:H3)

And still return values without any errors.

Now, trying to duplicate the results of "no errors" for the first calc,
C8*'Data Tables'!B3

=SUMPRODUCT('Data Tables'!B3,C8)
*Doesn't* work!

If you make it into a range, it *does work* (accepts text):
=SUMPRODUCT('Data Tables'!B3:B4,C8:C9)

Tried fooling it with something like this:

=SUMPRODUCT('Data Tables'!B3:B3,C8:C8)

But that didn't work either.

What is it with the necessity of an actual range to make it accept
non-numeric values?

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


"phowe43" wrote in message
...
e.g., if A7 is the sum of A1:A5 but there is no character in cell A2, how
can
I avoid the #Value! error without having to manually type in a "0" value. In
other words, the calculation does not recognize the blank cell as an
automatic "0" value.

I appreciate your help.

Hi,

If there are any spacebars in any of the cells referenced by your formula
you will get a VALUE error because you are trying to multiple:

C8*'Data Tables'!B3
'Driver Input Form'!D8*'Data Tables'!C3
'Driver Input Form'!E8*'Data Tables'!D3
'Driver Input Form'!F8*'Data Tables'!E3
'Driver Input Form'!G8*'Data Tables'!F3
'Driver Input Form'!H8*'Data Tables'!G3
'Driver Input Form'!I8*'Data Tables'!H3

To find the problem do the following:
1. On the formula bar select one of the portions of the formula that I have
broken out above and press F9. If that portion of the formula is the problem
then go to the two cell and make sure they contain nothing (no spacebars) and
no text entries, for example a formula that evaluates to "" will cause a
problem.

--
Cheers,
Shane Devenshire


"phowe43" wrote:

Thanks all...it's a lengthy formula and there may have been an easier way to
do it but I'm somewhat a novice. So here's the formula.

=(C8*'Data Tables'!B3+'Driver Input Form'!D8*'Data Tables'!C3+'Driver Input
Form'!E8*'Data Tables'!D3+'Driver Input Form'!F8*'Data Tables'!E3+'Driver
Input Form'!G8*'Data Tables'!F3+'Driver Input Form'!H8*'Data
Tables'!G3+'Driver Input Form'!I8*'Data Tables'!H3)

So what happens is that C8 - D8 - E8 (etc.), are referencing a table with
defined values from a different page...and then I'm simply trying to total
the values. Again what happens is that unless there is some value (including
a zero) in the cell (C8 - D8 etc.), I get the #Value! error.

Thanks again.

"ShaneDevenshire" wrote:

Hi,

"A7 is the sum of A1:A5 "

I think you had better show us the actual formula that is returning #VALUE
since the SUM function will not do this if a cell in the sum range is empty.
SUM does treat an empty cell as 0.

--
Thanks,
Shane Devenshire


"phowe43" wrote:

e.g., if A7 is the sum of A1:A5 but there is no character in cell A2, how can
I avoid the #Value! error without having to manually type in a "0" value. In
other words, the calculation does not recognize the blank cell as an
automatic "0" value.

I appreciate your help.

F1 = 2
G1 = x

=SUMPRODUCT(F1,G1) = #VALUE!
=SUMPRODUCT(F1:F2,G1:G2) = 0
=SUMPRODUCT({2},{"x"}) = 0

It appears that the arguments to SUMPRODUCT (in its native form) must be
arrays. Internally, it must not recognize (F1,G1) as arrays but when you
force the arrays by using { } it works. However, this seems to only apply
when there are multiple arguments.

=SUMPRODUCT(F1) = 2
=SUMPRODUCT(G1) = #VALUE!


--
Biff
Microsoft Excel MVP


"RagDyeR" wrote in message
...
All right folks ... can anyone explain this to me?

*Excluding* the first calc of
C8*'Data Tables'!B3,

This allows the OP to have text and/or nulls in any of the other cells:

=SUMPRODUCT('Data Input Form'!D8:I8,'Data Tables'!C3:H3)

And still return values without any errors.

Now, trying to duplicate the results of "no errors" for the first calc,
C8*'Data Tables'!B3

=SUMPRODUCT('Data Tables'!B3,C8)
*Doesn't* work!

If you make it into a range, it *does work* (accepts text):
=SUMPRODUCT('Data Tables'!B3:B4,C8:C9)

Tried fooling it with something like this:

=SUMPRODUCT('Data Tables'!B3:B3,C8:C8)

But that didn't work either.

What is it with the necessity of an actual range to make it accept
non-numeric values?

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


"phowe43" wrote in message
...
e.g., if A7 is the sum of A1:A5 but there is no character in cell A2, how
can
I avoid the #Value! error without having to manually type in a "0" value.
In
other words, the calculation does not recognize the blank cell as an
automatic "0" value.

I appreciate your help.

On Jan 5, 9:47*am, phowe43 wrote:
So here's the formula.

=(C8*'Data Tables'!B3+'Driver Input Form'!D8*'Data Tables'!C3+'Driver Input
Form'!E8*'Data Tables'!D3+'Driver Input Form'!F8*'Data Tables'!E3+'Driver
Input Form'!G8*'Data Tables'!F3+'Driver Input Form'!H8*'Data
Tables'!G3+'Driver Input Form'!I8*'Data Tables'!H3)

I am assuming the C8 is also in sheet 'Driver Input Form'.

As others have suggested, after row 8, temporarily insert a row (now
row 9), and in C9 enter =len(C8), then copy that across through I9.
If you expect zero in any of C9:I9 because the corresponding cell in
C8:I8 appears to be empty, this might reveal that it is not truly
empty. Note that "" and " " are not the same as an empty cell
(WYSInotWYG <sigh), and __sometimes__ they are not treated the same
as empty cell (consistency is not Excel's strong suit <sigh).

Delete row 9. If that does not reveal the source of the #VALUE error
(i.e. the non-empty cell), do something similar in the 'Data Tables'
sheet. That is, after row 3, temporarily insert a row (now row 4) and
enter =len(B3) in B4 and copy across through H4. Again, look for non-
zero where you would expected zero. And again, delete row 4 when you
are done.

Thanks all...it's a lengthy formula and there may have been an easier way to
do it but I'm somewhat a novice.

Again, assuming that C8 is in the 'Driver Input Form', you could
replace the long formula with simply:

=sumproduct(C8:I8, 'Data Tables'!B3:H3)

Ironically, that might also mask the problem creating the #VALUE
error. But it would be prudent to locate the source of the #VALUE
error, if not fix it, just to be sure that the contents are what you
intended.

BTW, I wrote C8:I8 instead of 'Driver Input Form'!C8:I8 because I ass-
u-me that this formula is on in the 'Driver Input Form' sheet, if
"C8+..." works for you. On the other hand, if the formula is not in
the 'Driver Input Form' sheet, perhaps the unqualified C8 in the
formula is the source of the #VALUE error insofar as that is the wrong
cell reference, and you intended it to be 'Driver Input Form'!C8.

Final comment: If you stick with the long formula, which I represent
as "a+b+c+...", beware that in Excel, the result of "(a+b+c+...)" is
not always the same as "a+b+c+...". In the latter case, Excel might
make an adjust toward zero if the result is infinitesimally different
from zero. This is an attempt to correct for small numerical errors
that creep into computation as an avoidable consequence of using a
binary representation of decimal fractions. Only you can decide which
result you would prefer. But generally, it is prudent to remove
superfluous parentheses, if only to improve readability (in more
complex formulas).

HTH.

Considering that array constants don't accept cell references, there doesn't
appear to be a way around this, does there?

Thanks for the input.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"T. Valko" wrote in message
...
F1 = 2
G1 = x

=SUMPRODUCT(F1,G1) = #VALUE!
=SUMPRODUCT(F1:F2,G1:G2) = 0
=SUMPRODUCT({2},{"x"}) = 0

It appears that the arguments to SUMPRODUCT (in its native form) must be
arrays. Internally, it must not recognize (F1,G1) as arrays but when you
force the arrays by using { } it works. However, this seems to only apply
when there are multiple arguments.

=SUMPRODUCT(F1) = 2
=SUMPRODUCT(G1) = #VALUE!


--
Biff
Microsoft Excel MVP


"RagDyeR" wrote in message
...
All right folks ... can anyone explain this to me?

*Excluding* the first calc of
C8*'Data Tables'!B3,

This allows the OP to have text and/or nulls in any of the other cells:

=SUMPRODUCT('Data Input Form'!D8:I8,'Data Tables'!C3:H3)

And still return values without any errors.

Now, trying to duplicate the results of "no errors" for the first calc,
C8*'Data Tables'!B3

=SUMPRODUCT('Data Tables'!B3,C8)
*Doesn't* work!

If you make it into a range, it *does work* (accepts text):
=SUMPRODUCT('Data Tables'!B3:B4,C8:C9)

Tried fooling it with something like this:

=SUMPRODUCT('Data Tables'!B3:B3,C8:C8)

But that didn't work either.

What is it with the necessity of an actual range to make it accept
non-numeric values?

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


"phowe43" wrote in message
...
e.g., if A7 is the sum of A1:A5 but there is no character in cell A2, how
can
I avoid the #Value! error without having to manually type in a "0" value.
In
other words, the calculation does not recognize the blank cell as an
automatic "0" value.

I appreciate your help.

That is ... a way around with Sumproduct!
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


"RagDyeR" wrote in message
...
Considering that array constants don't accept cell references, there doesn't
appear to be a way around this, does there?

Thanks for the input.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"T. Valko" wrote in message
...
F1 = 2
G1 = x

=SUMPRODUCT(F1,G1) = #VALUE!
=SUMPRODUCT(F1:F2,G1:G2) = 0
=SUMPRODUCT({2},{"x"}) = 0

It appears that the arguments to SUMPRODUCT (in its native form) must be
arrays. Internally, it must not recognize (F1,G1) as arrays but when you
force the arrays by using { } it works. However, this seems to only apply
when there are multiple arguments.

=SUMPRODUCT(F1) = 2
=SUMPRODUCT(G1) = #VALUE!


--
Biff
Microsoft Excel MVP


"RagDyeR" wrote in message
...
All right folks ... can anyone explain this to me?

*Excluding* the first calc of
C8*'Data Tables'!B3,

This allows the OP to have text and/or nulls in any of the other cells:

=SUMPRODUCT('Data Input Form'!D8:I8,'Data Tables'!C3:H3)

And still return values without any errors.

Now, trying to duplicate the results of "no errors" for the first calc,
C8*'Data Tables'!B3

=SUMPRODUCT('Data Tables'!B3,C8)
*Doesn't* work!

If you make it into a range, it *does work* (accepts text):
=SUMPRODUCT('Data Tables'!B3:B4,C8:C9)

Tried fooling it with something like this:

=SUMPRODUCT('Data Tables'!B3:B3,C8:C8)

But that didn't work either.

What is it with the necessity of an actual range to make it accept
non-numeric values?

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


"phowe43" wrote in message
...
e.g., if A7 is the sum of A1:A5 but there is no character in cell A2, how
can
I avoid the #Value! error without having to manually type in a "0" value.
In
other words, the calculation does not recognize the blank cell as an
automatic "0" value.

I appreciate your help.

Haven't been able to come up with anything.

--
Biff
Microsoft Excel MVP


"RagDyeR" wrote in message
...
That is ... a way around with Sumproduct!
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


"RagDyeR" wrote in message
...
Considering that array constants don't accept cell references, there
doesn't
appear to be a way around this, does there?

Thanks for the input.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"T. Valko" wrote in message
...
F1 = 2
G1 = x

=SUMPRODUCT(F1,G1) = #VALUE!
=SUMPRODUCT(F1:F2,G1:G2) = 0
=SUMPRODUCT({2},{"x"}) = 0

It appears that the arguments to SUMPRODUCT (in its native form) must be
arrays. Internally, it must not recognize (F1,G1) as arrays but when you
force the arrays by using { } it works. However, this seems to only apply
when there are multiple arguments.

=SUMPRODUCT(F1) = 2
=SUMPRODUCT(G1) = #VALUE!


--
Biff
Microsoft Excel MVP


"RagDyeR" wrote in message
...
All right folks ... can anyone explain this to me?

*Excluding* the first calc of
C8*'Data Tables'!B3,

This allows the OP to have text and/or nulls in any of the other cells:

=SUMPRODUCT('Data Input Form'!D8:I8,'Data Tables'!C3:H3)

And still return values without any errors.

Now, trying to duplicate the results of "no errors" for the first calc,
C8*'Data Tables'!B3

=SUMPRODUCT('Data Tables'!B3,C8)
*Doesn't* work!

If you make it into a range, it *does work* (accepts text):
=SUMPRODUCT('Data Tables'!B3:B4,C8:C9)

Tried fooling it with something like this:

=SUMPRODUCT('Data Tables'!B3:B3,C8:C8)

But that didn't work either.

What is it with the necessity of an actual range to make it accept
non-numeric values?

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


"phowe43" wrote in message
...
e.g., if A7 is the sum of A1:A5 but there is no character in cell A2, how
can
I avoid the #Value! error without having to manually type in a "0" value.
In
other words, the calculation does not recognize the blank cell as an
automatic "0" value.

I appreciate your help.