Post reply

a way to emulate =PRODUCT() from Excel?

  • June 19, 2008 at 12:01 pm

    #208601

    Hi there,

    I have an excel spreadsheet with 3 values in cells

    -1.16%

    -4.89%

    1.96%

    and a forumula cell =PRODUCT(A1:A3+1)-1

    which would return a value of -4.16%

    how could I write PRODUCT as part of a select statement?

    i have a table with x columns it it with values. It isn't as simple as a SUM as it multiplies all of the values in the cells.

    Any thoughts?

    thanks!

  • June 19, 2008 at 12:25 pm

    #831307

    In 2005 this would be easy to define as a user-defined aggregate, or a "regular" T-SQL function as well. You'd have to pass in a way to know which rows to multiply to each other.

    Otherwise - something like:

    Declare @prod numeric(18,2)

    set @prod=1;

    select @prod=(1+value)*prod

    from Mytable

    select @prod-1

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • June 19, 2008 at 12:57 pm

    #831335

    I cobbled this together from an example but it doesn't give me the right -4.16

    I have to account for the negative numbers and I hate doing math in SQL.

    DECLARE @tblValues TABLE

    (

    prodfloat

    )

    INSERT @tblValues (

    prod

    ) VALUES (-1.16)

    INSERT @tblValues (

    prod

    ) VALUES (-4.89)

    INSERT @tblValues (

    prod

    ) VALUES (1.96)

    select convert(varchar,case sum(case when sign(prod) = -1 then 1

    else 0 end)%2

    when 1 then -1 else 1 end

    * exp(sum(log(abs(case when sign(prod) <> 0 then prod end))))

    * min(case when prod = 0 then 0 else 1 end)) as product

    from @tblValues

    gives me back 11.1179

    tought that would do it but not close... damn trixy excel functions...

  • June 19, 2008 at 1:05 pm

    #831338

    It does help if I put in the correct scale for the numerics. This does seem to work (just adding in test data and fixing the scales to show the accuracy):

    drop table #mytable

    create table #mytable(value numeric(18,6))

    insert #mytable

    select -.0116 union all

    select -.0489 union all

    select .0196

    Declare @prod numeric(18,6)

    set @prod=1;

    select @prod=(1+value)*@prod

    from #Mytable

    select @prod-1

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • June 20, 2008 at 8:19 am

    #831804

    thanks 🙂

  • April 4, 2011 at 8:25 am

    #1307008

    Thanks , its performed well than using CTE.

  • April 4, 2011 at 10:39 pm

    #1307292

    {edit} Sorry... wrong post.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply