Need help finding a function to multiply...

  • Thread starter Thread starter Teri
  • Start date Start date
T

Teri

I need to multiply a range of cells together that may or
may not have 0's or empty cells within the range. But of
course, if you mutliply by 0 you get 0.

My formula needs to calculate even if there is 0's or
empty cells. In essence I want to ignore any 0's or empty
cells so I can still get my calculation.

Please HELP!
 
How about this:
use an if function to look at the cell and determine if it
is blanck or zero. If it is then return a 1, it it isn't
then return the actual number in the cell. For instance
say you are using a formula to get your results in the
cells that you need to multiply. lets say you have a cell
c3 that is a result of the formula =(X3*Y3). If the
result is zero then you want to ignore it. So change the
formula to be =If((X3*Y3)=0,1,(X3*Y3)). (Note: This will
work with whatever formula you have in the cell or just
hard data) If you do not want to see a 1 where there
should be a zero, then you can do a conditional format on
the cell. If it is 1 then you simply change the font to
be white and it will give the illusion that the cell is
blank, but you multiplication will still work since taking
anything times 1 results in the original answer.

I hope this helps...Good Luck

Ecocandle
 
Teri,
The product function will ignore blanks and text. Are the zeros coming from formulas? Maybe you could use IF statements on these formulas to return "", then they will be treated as text by the PRODUCT function.

Actually, I just came up with something better. Try the following array entered formula (control-shift-enter instead of just enter)

=PRODUCT(IF(A1:A8=0,1,A1:A8))

If you hit control-shift-enter the formula will appear with { } around it. This will then convert all of the zeros into ones before multipliing the range.

Good Luck,
Mark Graesser
(e-mail address removed)

----- Teri wrote: -----

I need to multiply a range of cells together that may or
may not have 0's or empty cells within the range. But of
course, if you mutliply by 0 you get 0.

My formula needs to calculate even if there is 0's or
empty cells. In essence I want to ignore any 0's or empty
cells so I can still get my calculation.

Please HELP!
 
Back
Top