Hi,

I am doing calcuations mainly multiplications of multiple cells, for
e.g =a1*a2*a3...a10. Cells a1-a5 has some number in it however a6-a10
does not. However it gives me result of 0 if blank cells are also
included in the calculation.

How do I go about calculating so that the blank cells are ignored
unless it has any number in it.

thanks in advance..

=product(a1:a10). this will ignore blanks
--
Wag more, bark less


"K-Man" wrote:

Hi,

I am doing calcuations mainly multiplications of multiple cells, for
e.g =a1*a2*a3...a10. Cells a1-a5 has some number in it however a6-a10
does not. However it gives me result of 0 if blank cells are also
included in the calculation.

How do I go about calculating so that the blank cells are ignored
unless it has any number in it.

thanks in advance..

Hello Brad,

This formula also gives me 0 if blanks are included.

thanks

On Jul 29, 10:56*am, Brad wrote:
=product(a1:a10). *this will ignore blanks
--
Wag more, bark less



"K-Man" wrote:
Hi,

I am doing calcuations mainly multiplications of multiple cells, for
e.g =a1*a2*a3...a10. *Cells a1-a5 has some number in it however a6-a10
does not. *However it gives me result of 0 if blank cells are also
included in the calculation.

How do I go about calculating so that the blank cells are ignored
unless it *has any number in it.

thanks in advance..- Hide quoted text -

- Show quoted text -

I tested the formula (=product(a1:a10) with blanks, text and numbers and got
the right result. This agrees to the help of the function. Do you happen to
have a cell that has a zero? If that is the case, I can see why you get the
zero- otherwise there shouldn't be a reason why this isn't working for you.

Is your recalculation set to automatic or manual?

--
Wag more, bark less


"K-Man" wrote:

Hello Brad,

This formula also gives me 0 if blanks are included.

thanks

On Jul 29, 10:56 am, Brad wrote:
=product(a1:a10). this will ignore blanks
--
Wag more, bark less



"K-Man" wrote:
Hi,

I am doing calcuations mainly multiplications of multiple cells, for
e.g =a1*a2*a3...a10. Cells a1-a5 has some number in it however a6-a10
does not. However it gives me result of 0 if blank cells are also
included in the calculation.

How do I go about calculating so that the blank cells are ignored
unless it has any number in it.

thanks in advance..- Hide quoted text -

- Show quoted text -

On Jul 29, 11:19*am, Brad wrote:
I tested the formula (=product(a1:a10) with blanks, text and numbers and got
the right result. *This agrees to the help of the function. *Do you happen to
have a cell that has a zero? * If that is the case, I can see why you get the
zero- otherwise there shouldn't be a reason why this isn't working for you.

Is your recalculation set to automatic or manual?

--
Wag more, bark less



"K-Man" wrote:
Hello Brad,

This formula also gives me 0 if blanks are included.

thanks

On Jul 29, 10:56 am, Brad wrote:
=product(a1:a10). *this will ignore blanks
--
Wag more, bark less

"K-Man" wrote:
Hi,
Acutally I forgot to mention I am multiplying cells that are %age to
calculate cumulative yield for each week. say 94% 100%, 93% etc..so
blank cells are formatted in % format. If you do that you will get 0
even if the cells is blank

thanks


I am doing calcuations mainly multiplications of multiple cells, for
e.g =a1*a2*a3...a10. *Cells a1-a5 has some number in it however a6-a10
does not. *However it gives me result of 0 if blank cells are also
included in the calculation.

How do I go about calculating so that the blank cells are ignored
unless it *has any number in it.

thanks in advance..- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

I changed the format for all to percentage and it still worked for me

What do you have the cell that has the product formula formatted as? Since
these are all percentages less than one multiplying several small numbers
together could produce a number small enough to yield a zero (depending on
how it is formatted

--
Wag more, bark less


"K-Man" wrote:

On Jul 29, 11:19 am, Brad wrote:
I tested the formula (=product(a1:a10) with blanks, text and numbers and got
the right result. This agrees to the help of the function. Do you happen to
have a cell that has a zero? If that is the case, I can see why you get the
zero- otherwise there shouldn't be a reason why this isn't working for you.

Is your recalculation set to automatic or manual?

--
Wag more, bark less



"K-Man" wrote:
Hello Brad,

This formula also gives me 0 if blanks are included.

thanks

On Jul 29, 10:56 am, Brad wrote:
=product(a1:a10). this will ignore blanks
--
Wag more, bark less

"K-Man" wrote:
Hi,
Acutally I forgot to mention I am multiplying cells that are %age to
calculate cumulative yield for each week. say 94% 100%, 93% etc..so
blank cells are formatted in % format. If you do that you will get 0
even if the cells is blank

thanks


I am doing calcuations mainly multiplications of multiple cells, for
e.g =a1*a2*a3...a10. Cells a1-a5 has some number in it however a6-a10
does not. However it gives me result of 0 if blank cells are also
included in the calculation.

How do I go about calculating so that the blank cells are ignored
unless it has any number in it.

thanks in advance..- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Taken directly from microsoft help

=PRODUCT(number1, [number2], ...)
Show All
Hide All
This article describes the formula syntax and usage of the PRODUCT function
(function: A prewritten formula that takes a value or values, performs an
operation, and returns a value or values. Use functions to simplify and
shorten formulas on a worksheet, especially those that perform lengthy or
complex calculations.) in Microsoft Office Excel.

Description
The PRODUCT function multiplies all the numbers given as arguments and
returns the product. For example, if cells A1 and A2 contain numbers, you can
use the formula =PRODUCT(A1, A2) to multiply those two numbers together. You
can also perform the same operation by using the multiply (*) mathematical
operator; for example, =A1 * A2.

The PRODUCT function is useful when you need to multiply many cells
together. For example, the formula =PRODUCT(A1:A3, C1:C3) is equivalent to
=A1 * A2 * A3 * C1 * C2 * C3.

Syntax
PRODUCT(number1, [number2], ...)The PRODUCT function syntax has the
following arguments (argument: A value that provides information to an
action, an event, a method, a property, a function, or a procedure.):

number1 Required. The first number or range (range: Two or more cells on a
sheet. The cells in a range can be adjacent or nonadjacent.) that you want to
multiply.
number2, ... Optional. Additional numbers or ranges that you want to
multiply, up to a maximum of 255 arguments.

Note If an argument is an array or reference, only numbers in the array
or reference are multiplied. Empty cells, logical values, and text in the
array or reference are ignored

--
Wag more, bark less


"K-Man" wrote:

Hello Brad,

This formula also gives me 0 if blanks are included.

thanks

On Jul 29, 10:56 am, Brad wrote:
=product(a1:a10). this will ignore blanks
--
Wag more, bark less



"K-Man" wrote:
Hi,

I am doing calcuations mainly multiplications of multiple cells, for
e.g =a1*a2*a3...a10. Cells a1-a5 has some number in it however a6-a10
does not. However it gives me result of 0 if blank cells are also
included in the calculation.

How do I go about calculating so that the blank cells are ignored
unless it has any number in it.

thanks in advance..- Hide quoted text -

- Show quoted text -

On Jul 29, 11:26*am, Brad wrote:
Taken directly from microsoft help

=PRODUCT(number1, [number2], ...)
Show All
Hide All
This article describes the formula syntax and usage of the PRODUCT function
(function: A prewritten formula that takes a value or values, performs an
operation, and returns a value or values. Use functions to simplify and
shorten formulas on a worksheet, especially those that perform lengthy or
complex calculations.) in Microsoft Office Excel.

Description
The PRODUCT function multiplies all the numbers given as arguments and
returns the product. For example, if cells A1 and A2 contain numbers, you can
use the formula =PRODUCT(A1, A2) to multiply those two numbers together.. You
can also perform the same operation by using the multiply (*) mathematical
operator; for example, =A1 * A2.

The PRODUCT function is useful when you need to multiply many cells
together. For example, the formula =PRODUCT(A1:A3, C1:C3) is equivalent to
=A1 * A2 * A3 * C1 * C2 * C3.

Syntax
PRODUCT(number1, [number2], ...)The PRODUCT function syntax has the
following arguments (argument: A value that provides information to an
action, an event, a method, a property, a function, or a procedure.):

number1 *Required. The first number or range (range: Two or more cells on a
sheet. The cells in a range can be adjacent or nonadjacent.) that you want to
multiply.
number2, ... *Optional. Additional numbers or ranges that you want to
multiply, up to a maximum of 255 arguments.

*Note * If an argument is an array or reference, only numbers in the array
or reference are multiplied. Empty cells, logical values, and text in the
array or reference are ignored

--
Wag more, bark less



"K-Man" wrote:
Hello Brad,

This formula also gives me 0 if blanks are included.

thanks

On Jul 29, 10:56 am, Brad wrote:
=product(a1:a10). *this will ignore blanks
--
Wag more, bark less

"K-Man" wrote:
Hi,

Thanks for the info...I know all this..

my worksheet formula is giving me zero for all the calcuated cells
which I have not data for, so that's why I am getting zero when I try
to multiply all the blank cells.


I am doing calcuations mainly multiplications of multiple cells, for
e.g =a1*a2*a3...a10. *Cells a1-a5 has some number in it however a6-a10
does not. *However it gives me result of 0 if blank cells are also
included in the calculation.

How do I go about calculating so that the blank cells are ignored
unless it *has any number in it.

thanks in advance..- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Change the format on the cell that has the product formula to "General" with
10 decimal places - do you still get a zero?

--
Wag more, bark less


"K-Man" wrote:

On Jul 29, 11:26 am, Brad wrote:
Taken directly from microsoft help

=PRODUCT(number1, [number2], ...)
Show All
Hide All
This article describes the formula syntax and usage of the PRODUCT function
(function: A prewritten formula that takes a value or values, performs an
operation, and returns a value or values. Use functions to simplify and
shorten formulas on a worksheet, especially those that perform lengthy or
complex calculations.) in Microsoft Office Excel.

Description
The PRODUCT function multiplies all the numbers given as arguments and
returns the product. For example, if cells A1 and A2 contain numbers, you can
use the formula =PRODUCT(A1, A2) to multiply those two numbers together.. You
can also perform the same operation by using the multiply (*) mathematical
operator; for example, =A1 * A2.

The PRODUCT function is useful when you need to multiply many cells
together. For example, the formula =PRODUCT(A1:A3, C1:C3) is equivalent to
=A1 * A2 * A3 * C1 * C2 * C3.

Syntax
PRODUCT(number1, [number2], ...)The PRODUCT function syntax has the
following arguments (argument: A value that provides information to an
action, an event, a method, a property, a function, or a procedure.):

number1 Required. The first number or range (range: Two or more cells on a
sheet. The cells in a range can be adjacent or nonadjacent.) that you want to
multiply.
number2, ... Optional. Additional numbers or ranges that you want to
multiply, up to a maximum of 255 arguments.

Note If an argument is an array or reference, only numbers in the array
or reference are multiplied. Empty cells, logical values, and text in the
array or reference are ignored

--
Wag more, bark less



"K-Man" wrote:
Hello Brad,

This formula also gives me 0 if blanks are included.

thanks

On Jul 29, 10:56 am, Brad wrote:
=product(a1:a10). this will ignore blanks
--
Wag more, bark less

"K-Man" wrote:
Hi,

Thanks for the info...I know all this..

my worksheet formula is giving me zero for all the calcuated cells
which I have not data for, so that's why I am getting zero when I try
to multiply all the blank cells.


I am doing calcuations mainly multiplications of multiple cells, for
e.g =a1*a2*a3...a10. Cells a1-a5 has some number in it however a6-a10
does not. However it gives me result of 0 if blank cells are also
included in the calculation.

How do I go about calculating so that the blank cells are ignored
unless it has any number in it.

thanks in advance..- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

So change your worksheet formula so that it doesn't give zero from blank
cells.
You've had a number of suggestions as to how to do it.
PRODUCT does not give a zero from blank cells., so it isn't obvious what you
are doing to give zero. Tell us what formula you are using, and what data
values are in the cells feeding into that formula.
--
David Biddulph

"K-Man" wrote in message
...
....
Thanks for the info...I know all this..

my worksheet formula is giving me zero for all the calcuated cells
which I have not data for, so that's why I am getting zero when I try
to multiply all the blank cells.

=IF(COUNT(A1:A10)=10,PRODUCT(A1:A10),"") if you want the result to be blank
until all cells are completed.
=PRODUCT(A1:A10) if you want to multiply the cells which are completed, and
ignore the others.
--
David Biddulph

"K-Man" wrote in message
...
Hi,

I am doing calcuations mainly multiplications of multiple cells, for
e.g =a1*a2*a3...a10. Cells a1-a5 has some number in it however a6-a10
does not. However it gives me result of 0 if blank cells are also
included in the calculation.

How do I go about calculating so that the blank cells are ignored
unless it has any number in it.

thanks in advance..

On Wed, 29 Jul 2009 07:35:57 -0700 (PDT), K-Man
wrote:

Hi,

I am doing calcuations mainly multiplications of multiple cells, for
e.g =a1*a2*a3...a10. Cells a1-a5 has some number in it however a6-a10
does not. However it gives me result of 0 if blank cells are also
included in the calculation.

How do I go about calculating so that the blank cells are ignored
unless it has any number in it.

thanks in advance..


If you want to exclude not only cells that are "really" blank, but
also cells that are interpreted as 0 as they are blank "by a formula",
you may try this:

=PRODUCT(IF(A1:A10<0,A1:A10))

Note: This is an array formula that should be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

This formula will also ignore cells that really has a value of 0, but
if that is no problem it might work for you.

Hope this helps / Lars-Åke