Simple calculation where values change to letters?
 

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

Simple calculation where values change to letters?
 

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

Simple calculation where values change to letters?
 

"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

Simple calculation where values change to letters?
 

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



.