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(D30:D60)
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(D45:D61=0,D45:E61=""),"",PRODUCT(D45:D61))
to elminate the possible zero being generated
it still doesn't work and i'm at a lost
can anyone help!

--
billy-bob

Maybe

=IF(OR(count(D45:D61)=0,D45:E61=""),"",PRODUCT(D45 :D61))

--
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(D30:D60)
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(D45:D61=0,D45:E61=""),"",PRODUCT(D45:D61))
to elminate the possible zero being generated
it still doesn't work and i'm at a lost
can anyone help!

--
billy-bob

I'm sure there is a more elegant solution, but this works:

=SUMPRODUCT((D45:D61<"")*(D45:D61<0)*PRODUCT(D45 :D61))/SUMPRODUCT((D45:D61<"")*(D45:D61<0))

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(D30:D60)
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(D45:D61=0,D45:E61=""),"",PRODUCT(D45:D61))
to elminate the possible zero being generated
it still doesn't work and i'm at a lost
can anyone help!

--
billy-bob

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(D45:D61)=0,D45:E61=""),"",PRODUCT(D45 :D61))

--
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(D30:D60)
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(D45:D61=0,D45:E61=""),"",PRODUCT(D45:D61))
to elminate the possible zero being generated
it still doesn't work and i'm at a lost
can anyone help!

--
billy-bob

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((D45:D61<"")*(D45:D61<0)*PRODUCT(D45 :D61))/SUMPRODUCT((D45:D61<"")*(D45:D61<0))

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(D30:D60)
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(D45:D61=0,D45:E61=""),"",PRODUCT(D45:D61))
to elminate the possible zero being generated
it still doesn't work and i'm at a lost
can anyone help!

--
billy-bob

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((D45:D61<"")*(D45:D61<0)*PRODUCT(D45 :D61))/SUMPRODUCT((D45:D61<"")*(D45:D61<0))

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(D30:D60)
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(D45:D61=0,D45:E61=""),"",PRODUCT(D45:D61))
to elminate the possible zero being generated
it still doesn't work and i'm at a lost
can anyone help!

--
billy-bob

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(D45:D61)=0,D45:E61=""),"",PRODUCT(D45 :D61))

--
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(D30:D60)
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(D45:D61=0,D45:E61=""),"",PRODUCT(D45:D61))
to elminate the possible zero being generated
it still doesn't work and i'm at a lost
can anyone help!

--
billy-bob