ignoring blank cells

[K-Man]

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..

 

[Brad]

=product(a1:a10). this will ignore blanks

 

[K-Man]

Hello Brad,

This formula also gives me 0 if blanks are included.

thanks

 

[Brad]

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?

 

[David Biddulph]

=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.

 

[Brad]

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

 

[K-Man]

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?

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

 

[Brad]

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

 

[K-Man]

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, youcan
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 equivalentto
=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

Hello Brad,

This formula also gives me 0 if blanks are included.

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.

 

[Brad]

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

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

Hello Brad,

This formula also gives me 0 if blanks are included.

=product(a1:a10). this will ignore blanks

:
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.

- Show quoted text -

 

[K-Man]

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

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 youwant 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 inthe
array or reference are ignored
--
Wag more, bark less
:
Hello Brad,
This formula also gives me 0 if blanks are included.
thanks
=product(a1:a10).  this will ignore blanks

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.

Yes,

The problem is that my calculations in blank cells which have no data
in it defaults to zero so when I referece those cells when doing my
calculation it gives me zero, anything multiplied by 0 is 0 I guess..

Here's my worksheet:
B2
=sheet1!A1 (which is 94)
B3
=Sheet1!A2 (which is blank, data to be entered in future)
B4
=Sheet1!A3 (which is blank, data to be entered in future)
B5
=Sheet1!A4 (which is blank, data to be entered in future)
B6
=product(B2:B5) (it willgive you blank or zero depending if you have
the checkmark set to options view tab)

The answer I am looking for is 94, for B6 however it will update once
I input data in B3, B4 etc..it should not give me zero or blank.

try this out..

Thanks

 

[David Biddulph]

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.

 

[K-Man]

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.

OK,

I have figured it out by putting an IF statement, so I will check if
the cells are blank if blank then I will put 1 in the cell, if not
then I will put the value of the cell.

thanks

 

[David Biddulph]

You still haven't told us why PRODUCT wouldn't work for you.
What formula were you using, and with what data values?
--
David Biddulph

....

I have figured it out by putting an IF statement, so I will check if
the cells are blank if blank then I will put 1 in the cell, if not
then I will put the value of the cell.

....

 

[David Biddulph]

It would have been a lot easier if you had told us that earlier.
I assume that where you say "once I input data in B3, B4 etc.." you may have
intended to say "once I input data in Sheet1!A2 , Sheet1!A3 etc.." ?

Simple to cure. Change your =Sheet1!A2 to =IF(Sheet1!A2="","",Sheet1!A2)
and copy down.
--
David Biddulph

....

The problem is that my calculations in blank cells which have no data
in it defaults to zero so when I referece those cells when doing my
calculation it gives me zero, anything multiplied by 0 is 0 I guess..

Here's my worksheet:
B2
=sheet1!A1 (which is 94)
B3
=Sheet1!A2 (which is blank, data to be entered in future)
B4
=Sheet1!A3 (which is blank, data to be entered in future)
B5
=Sheet1!A4 (which is blank, data to be entered in future)
B6
=product(B2:B5) (it willgive you blank or zero depending if you have
the checkmark set to options view tab)

The answer I am looking for is 94, for B6 however it will update once
I input data in B3, B4 etc..it should not give me zero or blank.

....

 

[Brad]

Alright now I get it,

The equation that I gave you will not work for your column B, because it is
inserting a zero for a blank (because of the equation that you are using)
just as you were stating. I was understanding that you had blanks (not
equations looking at blanks). What you did, by using the if statement, will
work for column B

However, the product forumla will work for your column A (and you can
reference sheet1 very easily....) Hope the rest of your day goes better!!

Hope this helps!
--
Wag more, bark less

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

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

:
Hello Brad,

This formula also gives me 0 if blanks are included.

=product(a1:a10). this will ignore blanks

:
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

Yes,

The problem is that my calculations in blank cells which have no data
in it defaults to zero so when I referece those cells when doing my
calculation it gives me zero, anything multiplied by 0 is 0 I guess..

Here's my worksheet:
B2
=sheet1!A1 (which is 94)
B3
=Sheet1!A2 (which is blank, data to be entered in future)
B4
=Sheet1!A3 (which is blank, data to be entered in future)
B5
=Sheet1!A4 (which is blank, data to be entered in future)
B6
=product(B2:B5) (it willgive you blank or zero depending if you have
the checkmark set to options view tab)

The answer I am looking for is 94, for B6 however it will update once
I input data in B3, B4 etc..it should not give me zero or blank.

try this out..

Thanks

- Show quoted text -

 

[Lars-Åke Aspelin]

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

 

[K-Man]

Alright now I get it,

The equation that I gave you will not work for your column B, because it is
inserting a zero for a blank (because of the equation that you are using)
just as you were stating.   I was understanding that you hadblanks(not
equations looking atblanks). What you did, by using the if statement, will
work for column B  

However, the product forumla will work for your column A (and you can
reference sheet1 very easily....)  Hope the rest of your day goes better!!

Hope this helps!
--
Wag more, bark less

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
:
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
complexcalculations.) in Microsoft OfficeExcel.
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.) thatyou 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 numbersin the array
or reference are multiplied. Empty cells, logical values, and text in the
array or reference are ignored
--
Wag more, bark less
:
Hello Brad,
This formula also gives me 0 ifblanksare included.
thanks
=product(a1:a10).  this will ignoreblanks
--
Wag more, bark less
:
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 ithowever a6-a10 Yes,

The problem is that mycalculationsin blank cells which have no data
in it defaults to zero so when I referece those cells when doing my
calculation it gives me zero, anything multiplied by 0 is 0 I guess..

Here's my worksheet:
B2
=sheet1!A1 (which is 94)
B3
=Sheet1!A2 (which is blank, data to be entered in future)
B4
=Sheet1!A3 (which is blank, data to be entered in future)
B5
=Sheet1!A4 (which is blank, data to be entered in future)
B6
=product(B2:B5) (it willgive you blank or zero depending if you have
the checkmark set to options view tab)

The answer I am looking for is 94, for B6 however it will update once
I input data in B3, B4 etc..it should not give me zero or blank.

try this out..

Anyways..thanks very much for your assitance..