If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
=PRODUCT
i'm trying to generate an automatic form (which will locked down)
within the hiden E columm if have a list of numbers, so when i pick 'p' (this is a tick mark in wingdings) from a drop-down in columm C, it automatically aprears in columm D as choosen. at the bottom of the form all choosen items in columm D need to be multiplied altogether so i used the product formula - =PRODUCT(D3060) However because not all of the items in the columm can be choosen at anyone time - it was coming up as a blank cell - so after looking through this site I tried this - =IF(OR(D4561=0,D45:E61=""),"",PRODUCT(D4561)) to elminate the possible zero being generated it still doesn't work and i'm at a lost can anyone help! -- billy-bob |
#2
|
|||
|
|||
=PRODUCT
Maybe
=IF(OR(count(D4561)=0,D45:E61=""),"",PRODUCT(D45 61)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "billy-bob" wrote in message ... i'm trying to generate an automatic form (which will locked down) within the hiden E columm if have a list of numbers, so when i pick 'p' (this is a tick mark in wingdings) from a drop-down in columm C, it automatically aprears in columm D as choosen. at the bottom of the form all choosen items in columm D need to be multiplied altogether so i used the product formula - =PRODUCT(D3060) However because not all of the items in the columm can be choosen at anyone time - it was coming up as a blank cell - so after looking through this site I tried this - =IF(OR(D4561=0,D45:E61=""),"",PRODUCT(D4561)) to elminate the possible zero being generated it still doesn't work and i'm at a lost can anyone help! -- billy-bob |
#3
|
|||
|
|||
=PRODUCT
I'm sure there is a more elegant solution, but this works:
=SUMPRODUCT((D4561"")*(D45610)*PRODUCT(D45 61))/SUMPRODUCT((D4561"")*(D45610)) Hope this helps, Hutch "billy-bob" wrote: i'm trying to generate an automatic form (which will locked down) within the hiden E columm if have a list of numbers, so when i pick 'p' (this is a tick mark in wingdings) from a drop-down in columm C, it automatically aprears in columm D as choosen. at the bottom of the form all choosen items in columm D need to be multiplied altogether so i used the product formula - =PRODUCT(D3060) However because not all of the items in the columm can be choosen at anyone time - it was coming up as a blank cell - so after looking through this site I tried this - =IF(OR(D4561=0,D45:E61=""),"",PRODUCT(D4561)) to elminate the possible zero being generated it still doesn't work and i'm at a lost can anyone help! -- billy-bob |
#4
|
|||
|
|||
=PRODUCT
thanks bob for post - sorry it didn't work, cell is returning as '#VALUE' no
matter what if put in D cells thanks PS Tom hutches formula 'sort' of works please see my reply to him thanks again "Bob Phillips" wrote: Maybe =IF(OR(count(D4561)=0,D45:E61=""),"",PRODUCT(D45 61)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "billy-bob" wrote in message ... i'm trying to generate an automatic form (which will locked down) within the hiden E columm if have a list of numbers, so when i pick 'p' (this is a tick mark in wingdings) from a drop-down in columm C, it automatically aprears in columm D as choosen. at the bottom of the form all choosen items in columm D need to be multiplied altogether so i used the product formula - =PRODUCT(D3060) However because not all of the items in the columm can be choosen at anyone time - it was coming up as a blank cell - so after looking through this site I tried this - =IF(OR(D4561=0,D45:E61=""),"",PRODUCT(D4561)) to elminate the possible zero being generated it still doesn't work and i'm at a lost can anyone help! -- billy-bob |
#5
|
|||
|
|||
=PRODUCT
tom
thanks for post. it works but... when one or more of the figures in the D45-D61 is removed or blank - to cell comes back blank. this is the problem i'm looking to resolve. "Tom Hutchins" wrote: I'm sure there is a more elegant solution, but this works: =SUMPRODUCT((D4561"")*(D45610)*PRODUCT(D45 61))/SUMPRODUCT((D4561"")*(D45610)) Hope this helps, Hutch "billy-bob" wrote: i'm trying to generate an automatic form (which will locked down) within the hiden E columm if have a list of numbers, so when i pick 'p' (this is a tick mark in wingdings) from a drop-down in columm C, it automatically aprears in columm D as choosen. at the bottom of the form all choosen items in columm D need to be multiplied altogether so i used the product formula - =PRODUCT(D3060) However because not all of the items in the columm can be choosen at anyone time - it was coming up as a blank cell - so after looking through this site I tried this - =IF(OR(D4561=0,D45:E61=""),"",PRODUCT(D4561)) to elminate the possible zero being generated it still doesn't work and i'm at a lost can anyone help! -- billy-bob |
#6
|
|||
|
|||
=PRODUCT
thanks tom for post
it did work i found the problem i was having was was in the formula in D45 to D61 it was =IF(C49="P",E49,) this was returning this cell as zero (althrough show as blank), so i changed all these cells to =IF(C49="P",E49,"") and it's all working ok now (yip-hee) thanks for the help. "Tom Hutchins" wrote: I'm sure there is a more elegant solution, but this works: =SUMPRODUCT((D4561"")*(D45610)*PRODUCT(D45 61))/SUMPRODUCT((D4561"")*(D45610)) Hope this helps, Hutch "billy-bob" wrote: i'm trying to generate an automatic form (which will locked down) within the hiden E columm if have a list of numbers, so when i pick 'p' (this is a tick mark in wingdings) from a drop-down in columm C, it automatically aprears in columm D as choosen. at the bottom of the form all choosen items in columm D need to be multiplied altogether so i used the product formula - =PRODUCT(D3060) However because not all of the items in the columm can be choosen at anyone time - it was coming up as a blank cell - so after looking through this site I tried this - =IF(OR(D4561=0,D45:E61=""),"",PRODUCT(D4561)) to elminate the possible zero being generated it still doesn't work and i'm at a lost can anyone help! -- billy-bob |
#7
|
|||
|
|||
=PRODUCT
thanks bob for post
it did work i found the problem, i had the wrong formula in D45 to D61 it was =IF(C49="P",E49,) this was returning this cell as zero (althrough show as blank), so i changed all these cells to =IF(C49="P",E49,"") and it's all working ok now with just the product formula alone (yip-hee) thanks for the help. "Bob Phillips" wrote: Maybe =IF(OR(count(D4561)=0,D45:E61=""),"",PRODUCT(D45 61)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "billy-bob" wrote in message ... i'm trying to generate an automatic form (which will locked down) within the hiden E columm if have a list of numbers, so when i pick 'p' (this is a tick mark in wingdings) from a drop-down in columm C, it automatically aprears in columm D as choosen. at the bottom of the form all choosen items in columm D need to be multiplied altogether so i used the product formula - =PRODUCT(D3060) However because not all of the items in the columm can be choosen at anyone time - it was coming up as a blank cell - so after looking through this site I tried this - =IF(OR(D4561=0,D45:E61=""),"",PRODUCT(D4561)) to elminate the possible zero being generated it still doesn't work and i'm at a lost can anyone help! -- billy-bob |
Thread Tools | |
Display Modes | |
|
|