Function Call

[Phil Hageman]

Trying to sum three mulitplied cell pairs (A17*A8,
A20*A21, AND A23*A24), then, multiply that sum by a final
cell value (A34). Problem is, A17, A20, and A23 are
linked to cells in another sheet where the formula is =IF
(B10="","",SUM(AB10:AB13)) - which returns a #Value! error
because of the quotes "" (when the value is blank). Can't
use a zero instead of the quotes, so I'm advised to
substitute function calls, the "Ns", in the below
formula. Can someone help me further - how to set this
up.

"Add some function calls to convert the null string to
zero":
=SUM(((A17*A18)+(N(A20)*N(A21))+(N(A23)*N(A24)))*A34)

Thanks, Phl

 

[JON JON]

Give this a try

=SUM(PRODUCT(A17,A8),PRODUCT(A20,A21), PRODUCT(A23,A24))*A34

Note that Sum and Product will ignore text. Also avoid "+" within a sum
functions instead separate each addend with a ",")
e.g A1+B2+C3 will give the same result as SUM(A1,B2,C3) except that the
former will result to error when there are text in either of the three cell
while the latter will not.

Regards,

Jon-jon

 

[Tom Ogilvy]

It is a worksheet formula and it is already set up. If you want to do it in
code.

Application.Sum(((Val(Range("A17"))*Val(Range("A18")))+(val(Range("A20"))*Va
l(Range("A21")))+(Val(Range("A23"))*Val(Range("A24"))))*Range("A34"))

of course the Sum is superflous.

((Val(Range("A17"))*Val(Range("A18")))+(val(Range("A20"))*Val(Range("A21")))
+(Val(Range("A23"))*Val(Range("A24"))))*Range("A34")

would work as well.

 

[Phil Hageman]

Jon Jon, Thanks, I got things working just as I needed.

 

[Phil Hageman]

Thanks, Tom. I got things to work just as needed.

-----Original Message-----
It is a worksheet formula and it is already set up. If you want to do it in
code.

Application.Sum(((Val(Range("A17"))*Val(Range("A18")))+ (val(Range("A20"))*Va
*Range("A34"))

of course the Sum is superflous.

((Val(Range("A17"))*Val(Range("A18")))+(val(Range("A20")) *Val(Range("A21")))
+(Val(Range("A23"))*Val(Range("A24"))))*Range("A34")

would work as well.

--
Regards,
Tom Ogilvy







.