Math Error in dataset expression calculation

Discussion in 'Microsoft ADO .NET' started by hfarner, Sep 17, 2009.

  1. hfarner

    hfarner Guest

    Hi,

    I have a rather complex dataset where there error occurrs, but I created a
    small project and can reproduce the error. Here it is in a nutshell:

    Datatable Bid has columns PCT decimal and ID, also TotalBid and TotalCost
    expression columns.

    Table BidChild has column ID relating to parent Bid, Quantity and price,
    decimal. Cost is the expression Quantity*Cost as decimal, BidCost is the
    following formula:

    Cost + (Cost / (1-Parent.PCT))

    In theory, this would show the marked up cost based on the value of the PCT
    field in parent record. So, put two grids on a form showing the data and
    place .25 in the PCT column, add a child putting 1 in quantity, 100 in price
    and the calculation yields 233.333333333333 (Not the correct answer).
    However, if you change the formula to Cost + (Cost * Parent.PCT) it yield a
    correct answer. I need the formula as shown above, not the one that actually
    yeilds a value. Can someone please tell me either a) what I am doing wrong
    or b) suggestion on corrective action to get the correct value.

    Thanks!
    Howard Arner
     
    hfarner, Sep 17, 2009
    #1
    1. Advertisements

  2. hfarner

    Miha Markic Guest

    If you have problems with expression columns I recommend you to fill the
    values by yourself - either when you retrieve data or when data changed
    (perhaps by attaching to various events)
    --
    Miha Markic [MVP C#, INETA Country Leader for Slovenia]
    RightHand .NET consulting & development www.rthand.com
    Blog: blog.rthand.com

    "hfarner" <> wrote in message
    news:...
    > Hi,
    >
    > I have a rather complex dataset where there error occurrs, but I created a
    > small project and can reproduce the error. Here it is in a nutshell:
    >
    > Datatable Bid has columns PCT decimal and ID, also TotalBid and TotalCost
    > expression columns.
    >
    > Table BidChild has column ID relating to parent Bid, Quantity and price,
    > decimal. Cost is the expression Quantity*Cost as decimal, BidCost is the
    > following formula:
    >
    > Cost + (Cost / (1-Parent.PCT))
    >
    > In theory, this would show the marked up cost based on the value of the
    > PCT
    > field in parent record. So, put two grids on a form showing the data and
    > place .25 in the PCT column, add a child putting 1 in quantity, 100 in
    > price
    > and the calculation yields 233.333333333333 (Not the correct answer).
    > However, if you change the formula to Cost + (Cost * Parent.PCT) it yield
    > a
    > correct answer. I need the formula as shown above, not the one that
    > actually
    > yeilds a value. Can someone please tell me either a) what I am doing
    > wrong
    > or b) suggestion on corrective action to get the correct value.
    >
    > Thanks!
    > Howard Arner
     
    Miha Markic, Sep 18, 2009
    #2
    1. Advertisements

  3. hfarner

    Patrice Guest

    The result is correct so as this is not what you want, the formula you want
    to use is just wrong.

    You could start from the correct formula and transform it into the form you
    want (could it be a confusion between a formula that would take a part of
    the whole percentage (25%) against another formula that would use the other
    part of the percentage (75 %)) ???

    --
    Patrice


    "hfarner" <> a écrit dans le message de
    groupe de discussion : ...
    > Hi,
    >
    > I have a rather complex dataset where there error occurrs, but I created a
    > small project and can reproduce the error. Here it is in a nutshell:
    >
    > Datatable Bid has columns PCT decimal and ID, also TotalBid and TotalCost
    > expression columns.
    >
    > Table BidChild has column ID relating to parent Bid, Quantity and price,
    > decimal. Cost is the expression Quantity*Cost as decimal, BidCost is the
    > following formula:
    >
    > Cost + (Cost / (1-Parent.PCT))
    >
    > In theory, this would show the marked up cost based on the value of the
    > PCT
    > field in parent record. So, put two grids on a form showing the data and
    > place .25 in the PCT column, add a child putting 1 in quantity, 100 in
    > price
    > and the calculation yields 233.333333333333 (Not the correct answer).
    > However, if you change the formula to Cost + (Cost * Parent.PCT) it yield
    > a
    > correct answer. I need the formula as shown above, not the one that
    > actually
    > yeilds a value. Can someone please tell me either a) what I am doing
    > wrong
    > or b) suggestion on corrective action to get the correct value.
    >
    > Thanks!
    > Howard Arner
    >
     
    Patrice, Sep 18, 2009
    #3
  4. hfarner

    hfarner Guest

    Hi patrice, thank you for your answer. The formula is correct as it should
    yield 133 not 233. The formula is part of bidding program an is attempting
    to mark up items by percentage. I always thought that you would, in the case
    of 25 percent, just take 25 percent of total and add it, however this is not
    correct. To achieve a true markup I must use the formula supplied. (Old
    formula was in program and is not making estimators happy, new formula was
    mandated). Somehow this calculation is not being achieved, I have tried
    casting the types within the formula to differing values thinking it might be
    some type of decimal overflow, but to nought. I would appreciate any
    feedback.

    Thanks
    Howard

    "Patrice" wrote:

    > The result is correct so as this is not what you want, the formula you want
    > to use is just wrong.
    >
    > You could start from the correct formula and transform it into the form you
    > want (could it be a confusion between a formula that would take a part of
    > the whole percentage (25%) against another formula that would use the other
    > part of the percentage (75 %)) ???
    >
    > --
    > Patrice
    >
    >
    > "hfarner" <> a écrit dans le message de
    > groupe de discussion : ...
    > > Hi,
    > >
    > > I have a rather complex dataset where there error occurrs, but I created a
    > > small project and can reproduce the error. Here it is in a nutshell:
    > >
    > > Datatable Bid has columns PCT decimal and ID, also TotalBid and TotalCost
    > > expression columns.
    > >
    > > Table BidChild has column ID relating to parent Bid, Quantity and price,
    > > decimal. Cost is the expression Quantity*Cost as decimal, BidCost is the
    > > following formula:
    > >
    > > Cost + (Cost / (1-Parent.PCT))
    > >
    > > In theory, this would show the marked up cost based on the value of the
    > > PCT
    > > field in parent record. So, put two grids on a form showing the data and
    > > place .25 in the PCT column, add a child putting 1 in quantity, 100 in
    > > price
    > > and the calculation yields 233.333333333333 (Not the correct answer).
    > > However, if you change the formula to Cost + (Cost * Parent.PCT) it yield
    > > a
    > > correct answer. I need the formula as shown above, not the one that
    > > actually
    > > yeilds a value. Can someone please tell me either a) what I am doing
    > > wrong
    > > or b) suggestion on corrective action to get the correct value.
    > >
    > > Thanks!
    > > Howard Arner
    > >

    >
    >
     
    hfarner, Sep 18, 2009
    #4
  5. hfarner

    hfarner Guest

    Miha,

    Thank you for your reply. I am dreading having to do it this way becasue of
    the side effects introduced in the application. Application has giant dataset
    document with many calculated fields which contains parts, labor, other,
    subcontractors, etc, etc. each differing item has this calculation. Dataset
    is passed to reports, web services, etc. I dread having to create all of the
    interdependent changes to accomplish doing this manually when it was working
    so brilliantly before the formula change.

    Thanks
    Howard

    "Miha Markic" wrote:

    > If you have problems with expression columns I recommend you to fill the
    > values by yourself - either when you retrieve data or when data changed
    > (perhaps by attaching to various events)
    > --
    > Miha Markic [MVP C#, INETA Country Leader for Slovenia]
    > RightHand .NET consulting & development www.rthand.com
    > Blog: blog.rthand.com
    >
    > "hfarner" <> wrote in message
    > news:...
    > > Hi,
    > >
    > > I have a rather complex dataset where there error occurrs, but I created a
    > > small project and can reproduce the error. Here it is in a nutshell:
    > >
    > > Datatable Bid has columns PCT decimal and ID, also TotalBid and TotalCost
    > > expression columns.
    > >
    > > Table BidChild has column ID relating to parent Bid, Quantity and price,
    > > decimal. Cost is the expression Quantity*Cost as decimal, BidCost is the
    > > following formula:
    > >
    > > Cost + (Cost / (1-Parent.PCT))
    > >
    > > In theory, this would show the marked up cost based on the value of the
    > > PCT
    > > field in parent record. So, put two grids on a form showing the data and
    > > place .25 in the PCT column, add a child putting 1 in quantity, 100 in
    > > price
    > > and the calculation yields 233.333333333333 (Not the correct answer).
    > > However, if you change the formula to Cost + (Cost * Parent.PCT) it yield
    > > a
    > > correct answer. I need the formula as shown above, not the one that
    > > actually
    > > yeilds a value. Can someone please tell me either a) what I am doing
    > > wrong
    > > or b) suggestion on corrective action to get the correct value.
    > >
    > > Thanks!
    > > Howard Arner

    >
     
    hfarner, Sep 18, 2009
    #5
  6. hfarner

    Patrice Guest

    Once again this is because mathematically :

    Cost + (Cost / (1-Parent.PCT))

    and

    Cost + (Cost * Parent.PCT)

    are not equal so you'll get a different result. This is not a precision
    problem...

    In one case you do 100+100/0.75 that is 100+100*4/3

    In the other case you do 100+100*0.25 which is 100+100*1/4

    Obviously 4/3 and 1/4 are not equal so you don't get the same result which
    is correct...

    Tell the guy to provide the appropriate formula. It could be that PCT is not
    correctly defined or not the same way by both of you ???

    --
    Patrice
     
    Patrice, Sep 18, 2009
    #6
  7. hfarner

    hfarner Guest

    Yes,
    I realize that these are two different formula. the formula with the
    multiplication calculates correctly in the dataset, but it is not the correct
    formula for getting a true mark up. The second formula is the correct
    formula, but is not calculated correctly by the data set. Disreguard the
    first formula completly as it is muddying the issue. The issue is that the
    second formula is not calculated. i.e. second formula for 100 dollars is 100
    + (100/(1-0.25)) = 133 whereas dataset is returning value 233. Inspection
    of all variables shows that they hold the correct values, but data returned
    is not the correct value. somewhere the data set plumbing is causing
    incorrect value to be returned.

    Thanks
    Howard

    "Patrice" wrote:

    > Once again this is because mathematically :
    >
    > Cost + (Cost / (1-Parent.PCT))
    >
    > and
    >
    > Cost + (Cost * Parent.PCT)
    >
    > are not equal so you'll get a different result. This is not a precision
    > problem...
    >
    > In one case you do 100+100/0.75 that is 100+100*4/3
    >
    > In the other case you do 100+100*0.25 which is 100+100*1/4
    >
    > Obviously 4/3 and 1/4 are not equal so you don't get the same result which
    > is correct...
    >
    > Tell the guy to provide the appropriate formula. It could be that PCT is not
    > correctly defined or not the same way by both of you ???
    >
    > --
    > Patrice
    >
    >
    >
    >
    >
    >
     
    hfarner, Sep 18, 2009
    #7
  8. hfarner

    Patrice Guest

    Sorry for being unclear but what I'm trying to say is that this is the
    CORRECT mathematical result.

    Go to Google and enter 100+(100/(1-0.25)) in the search box, the result is
    233.333333... So what is the problem ? (you are adding 100 with 100 divided
    by a value lower than 1 so you'll have more than 200 anyway and not 125 or
    133).

    For now my understanding is that :
    - you have currently in your code a formula that returns the correct result
    - for some reason this formula needs to be replaced by some other formula
    that would be supposed to return the same value but that will never be able
    to do so, but this new formula is considered "good" and the older one "bad"
    ???

    I would definitely keep the "bad" formula. Another option could be that PCT
    is not defined the same way in both formula leading to a somewhat messay
    situation...

    Or ask the guy who provided the formula to provide a sampler wiht numeric
    values for "Cost" and "PCT" and the numeric value he would expect from this
    formula as a result to make things crystal clear...

    Good luck.

    --
    Patrice
     
    Patrice, Sep 18, 2009
    #8
  9. hfarner

    Patrice Guest

    Could it be just that the formula you want is actually :

    Cost/(1-PCT) ?

    --
    Patrice

    "Patrice" <http://scribe-en.blogspot.com/> a écrit dans le message de
    news:...
    > Sorry for being unclear but what I'm trying to say is that this is the
    > CORRECT mathematical result.
    >
    > Go to Google and enter 100+(100/(1-0.25)) in the search box, the result is
    > 233.333333... So what is the problem ? (you are adding 100 with 100
    > divided by a value lower than 1 so you'll have more than 200 anyway and
    > not 125 or 133).
    >
    > For now my understanding is that :
    > - you have currently in your code a formula that returns the correct
    > result
    > - for some reason this formula needs to be replaced by some other formula
    > that would be supposed to return the same value but that will never be
    > able to do so, but this new formula is considered "good" and the older one
    > "bad" ???
    >
    > I would definitely keep the "bad" formula. Another option could be that
    > PCT is not defined the same way in both formula leading to a somewhat
    > messay situation...
    >
    > Or ask the guy who provided the formula to provide a sampler wiht numeric
    > values for "Cost" and "PCT" and the numeric value he would expect from
    > this formula as a result to make things crystal clear...
    >
    > Good luck.
    >
    > --
    > Patrice
     
    Patrice, Sep 18, 2009
    #9
  10. hfarner

    hfarner Guest

    You are correct. BTW, wat I am trying to do is gross margin, not percent.
    Finally got a correct answer from the person asking for this, and a correct
    formula.

    Thanks for your help Patrice!
    Howard

    "Patrice" wrote:

    > Could it be just that the formula you want is actually :
    >
    > Cost/(1-PCT) ?
    >
    > --
    > Patrice
    >
    > "Patrice" <http://scribe-en.blogspot.com/> a écrit dans le message de
    > news:...
    > > Sorry for being unclear but what I'm trying to say is that this is the
    > > CORRECT mathematical result.
    > >
    > > Go to Google and enter 100+(100/(1-0.25)) in the search box, the result is
    > > 233.333333... So what is the problem ? (you are adding 100 with 100
    > > divided by a value lower than 1 so you'll have more than 200 anyway and
    > > not 125 or 133).
    > >
    > > For now my understanding is that :
    > > - you have currently in your code a formula that returns the correct
    > > result
    > > - for some reason this formula needs to be replaced by some other formula
    > > that would be supposed to return the same value but that will never be
    > > able to do so, but this new formula is considered "good" and the older one
    > > "bad" ???
    > >
    > > I would definitely keep the "bad" formula. Another option could be that
    > > PCT is not defined the same way in both formula leading to a somewhat
    > > messay situation...
    > >
    > > Or ask the guy who provided the formula to provide a sampler wiht numeric
    > > values for "Cost" and "PCT" and the numeric value he would expect from
    > > this formula as a result to make things crystal clear...
    > >
    > > Good luck.
    > >
    > > --
    > > Patrice

    >
    >
     
    hfarner, Sep 21, 2009
    #10
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Kathleen Dollard

    Re: Using Math functions in DataColumn.expression ??

    Kathleen Dollard, Jul 16, 2003, in forum: Microsoft ADO .NET
    Replies:
    0
    Views:
    2,701
    Kathleen Dollard
    Jul 16, 2003
  2. Chayapathi Mahesha

    Re: Using Math functions in DataColumn.expression ??

    Chayapathi Mahesha, Jul 16, 2003, in forum: Microsoft ADO .NET
    Replies:
    1
    Views:
    675
    Kathleen Dollard
    Jul 17, 2003
  3. Graeme

    Dataset math

    Graeme, Feb 17, 2004, in forum: Microsoft ADO .NET
    Replies:
    4
    Views:
    229
    Graeme
    Feb 18, 2004
  4. Guest
    Replies:
    4
    Views:
    920
    Guest
    Jan 12, 2005
  5. Guest
    Replies:
    4
    Views:
    291
    Cor Ligthert
    Jun 23, 2005
Loading...

Share This Page