Function Call

  • Thread starter Thread starter Phil Hageman
  • Start date Start date
P

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
 
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
 
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.
 
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







.
Click to expand...
 
Back
Top