Simple (?) formula

[Phil Hageman]

A
17 96% I want to sum the products
18 60% of three cell pairs, and then multiply
that sum by another cell value. However,
20 65% if cells are blank, like A23 and A24,
21 40% I receive the #Value! error. What would
the formula be for the correct result
23 of 50.16%? (A20 & A21 could be blank too.)
24
Something like:
34 60% =SUM(((A17*A18)+(A20*A21)+(A23*A24))*(A35))

Thanks,
Phil

 

[J.E. McGimpsey]

If A23 and A24 are truly blank, your SUM() function works fine.

I suspect you've cleared the cells using a space character, however,
which causes SUM() to throw the #VALUE! error.

Try clearing your "blank" cells using Edit/Clear or the Delete key
instead.

 

[Phil Hageman]

The value in cell A23 is a link formula to another
sheet/cell B30. In that cell B30 is the formula
=IF(B10="","",SUM(AB10:AB13)). Could the problem be with
the quote marks? Although there is no space between them.

 

[J.E. McGimpsey]

Yes - it causes the same error: trying to multiply text. "" is a
"null string" - a string of zero length, which to XL is quite
different from blank.

The most efficient solution would be to return 0 in your function,
perhaps using Conditional Formatting to hide zero values.

Alternatively, you could add some function calls to convert the null
string to zero:

=SUM(((A17*A18)+(N(A20)*N(A21))+(N(A23)*N(A24)))*A34)

 

[Phil Hageman]

The altenative plan is the way I have to go - though I do
not understand the function call (not a programmer). Can
you take me further with this?