|
ignoring blank cells
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.. |
ignoring blank cells
=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.. |
ignoring blank cells
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 - |
ignoring blank cells
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 - |
ignoring 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.. |
ignoring blank cells
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 - |
ignoring blank cells
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 - |
ignoring blank cells
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 - |
ignoring blank cells
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 - |
ignoring blank cells
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 - |
ignoring blank cells
On Jul 29, 12:05*pm, Brad wrote:
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 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 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 -- Hide quoted text - - Show quoted text - |
ignoring blank cells
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. |
ignoring blank cells
On Jul 29, 12:27*pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote: 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 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 ... ... 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.- Hide quoted text - - Show quoted text - |
ignoring blank cells
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 "K-Man" wrote in message ... On Jul 29, 12:27 pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: 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. .... 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. .... |
ignoring blank cells
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 "K-Man" wrote in message ... .... 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. .... |
ignoring blank cells
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 "K-Man" wrote: On Jul 29, 12:05 pm, Brad wrote: 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 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 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 -- Hide quoted text - - Show quoted text - |
ignoring blank cells
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 |
ignoring blank cells
On Jul 29, 1:33*pm, Brad wrote:
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 "K-Man" wrote: On Jul 29, 12:05 pm, Brad wrote: 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 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.) 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 ifblanksare included. thanks On Jul 29, 10:56 am, Brad wrote: =product(a1:a10). *this will ignoreblanks -- 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 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.. Thanks does not. *However it gives me result of 0 if blank cells are also included in the calculation. Anyways..thanks very much for your assitance.. 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 -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 12:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com