Formula problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am having problem with a formula and am wondering if anyone can help. I
have a formula (=A1*B1*C1), where C1 does not always have a value however
excel will not simply just calculate A1*B1 how do i get round this?
 
maybe
=product(A1:C1)
or
=product(A1,B1,C1)
if however C1 could be text or an error indicator
=if(isnumber(A1),A1,1)*if(isnumber(B1),B1,1)*if(isnumber(C1),C1,1)
will work
 
Array-entered (Ctrl+Shift+Enter instead of just Enter):

=PRODUCT(IF(ISNUMBER(A1:C1),A1:C1,1))

Works great for longer ranges, to avoid multiple IF()s. Will omit
boolean values. Will multiply dates stored as number, yielding
potentially wrong results. It will also return 1 if all cells are
blank. To avoid that issue, use this instead to treat any blanks as 0
(also array-entered):

=PRODUCT(IF(ISNUMBER(A1:C1)+ISBLANK(A1:C1),A1:C1,1))
 
Why complicate things? To quote the OP

"where C1 does not always have a value "

It would be pretty easy to assume that it means that C1 is blank thus his
formula returns zero thus BJ's first formula =PRODUCT(A1:C1) works


Nevertheless your formulas do not work

put 2 in A1 and 2 in B1 and leave C1 blank

both formulas return 0 while the OP I am sure expects the result to be 4
 
Back
Top