I have a simple formula (A1*B1*C1)/((A1*B1*C1)+(D1*E1*F1)+(G1*H1*I1)).
However there are times when the values of D through F or the G through I
denominators can change into letters (if D changes to a letter so do E and
F, same happens to H and I if G changes to a letter). I need the formula to
complete the calculation by assuming any value that becomes a letter as zero.


Thanks for your help.

Regards,

ZUO

Try this:
=PRODUCT(A1:C1)/SUM(PRODUCT(A1:C1),PRODUCT(D1:F1),PRODUCT(G1:I1))

PRODUCT and SUM both ignore text inputs.

--
Best Regards,

Luke M
"Zuo" wrote in message
...
I have a simple formula (A1*B1*C1)/((A1*B1*C1)+(D1*E1*F1)+(G1*H1*I1)).
However there are times when the values of D through F or the G through I
denominators can change into letters (if D changes to a letter so do E
and
F, same happens to H and I if G changes to a letter). I need the formula
to
complete the calculation by assuming any value that becomes a letter as
zero.


Thanks for your help.

Regards,

ZUO

"Luke M" wrote:
PRODUCT and SUM both ignore text inputs.

Which is what I would want. And that might indeed be what Zuo really wants.

But I hasten to point out that PRODUCT(A1:C1), for example, is not the same
as "assuming any value that becomes a letter as zero".

If only B1 is non-numeric, for example, PRODUCT(A1:C1) will effectively
become A1*C1, whereas what Zuo asked for is effectively A1*0*C1, which is
zero.

Presumably Zuo misspoke.


----- original message -----

"Luke M" wrote in message
...
Try this:
=PRODUCT(A1:C1)/SUM(PRODUCT(A1:C1),PRODUCT(D1:F1),PRODUCT(G1:I1))

PRODUCT and SUM both ignore text inputs.

--
Best Regards,

Luke M
"Zuo" wrote in message
...
I have a simple formula (A1*B1*C1)/((A1*B1*C1)+(D1*E1*F1)+(G1*H1*I1)).
However there are times when the values of D through F or the G through I
denominators can change into letters (if D changes to a letter so do E
and
F, same happens to H and I if G changes to a letter). I need the formula
to
complete the calculation by assuming any value that becomes a letter as
zero.


Thanks for your help.

Regards,

ZUO

Joe,

No, actually the three values always become letters at the same time, so you
will be adding a zero not multiplying x zero. Luke's solution works for what
I need. Thank you both for taking the time to look into this.

Kind Regards,

ZUO

"Joe User" wrote:

"Luke M" wrote:
PRODUCT and SUM both ignore text inputs.

Which is what I would want. And that might indeed be what Zuo really wants.

But I hasten to point out that PRODUCT(A1:C1), for example, is not the same
as "assuming any value that becomes a letter as zero".

If only B1 is non-numeric, for example, PRODUCT(A1:C1) will effectively
become A1*C1, whereas what Zuo asked for is effectively A1*0*C1, which is
zero.

Presumably Zuo misspoke.


----- original message -----

"Luke M" wrote in message
...
Try this:
=PRODUCT(A1:C1)/SUM(PRODUCT(A1:C1),PRODUCT(D1:F1),PRODUCT(G1:I1))

PRODUCT and SUM both ignore text inputs.

--
Best Regards,

Luke M
"Zuo" wrote in message
...
I have a simple formula (A1*B1*C1)/((A1*B1*C1)+(D1*E1*F1)+(G1*H1*I1)).
However there are times when the values of D through F or the G through I
denominators can change into letters (if D changes to a letter so do E
and
F, same happens to H and I if G changes to a letter). I need the formula
to
complete the calculation by assuming any value that becomes a letter as
zero.


Thanks for your help.

Regards,

ZUO



.