PC Review


Reply
Thread Tools Rate Thread

Math Error in dataset expression calculation

 
 
hfarner
Guest
Posts: n/a
 
      17th Sep 2009
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
 
Reply With Quote
 
 
 
 
Miha Markic
Guest
Posts: n/a
 
      18th Sep 2009
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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


 
Reply With Quote
 
 
 
 
Patrice
Guest
Posts: n/a
 
      18th Sep 2009
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" <(E-Mail Removed)> a ťcrit dans le message de
groupe de discussion : http://www.pcreview.co.uk/forums/(E-Mail Removed)...
> 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
>


 
Reply With Quote
 
hfarner
Guest
Posts: n/a
 
      18th Sep 2009
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" <(E-Mail Removed)> a écrit dans le message de
> groupe de discussion : (E-Mail Removed)...
> > 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
> >

>
>

 
Reply With Quote
 
hfarner
Guest
Posts: n/a
 
      18th Sep 2009
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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

>

 
Reply With Quote
 
Patrice
Guest
Posts: n/a
 
      18th Sep 2009
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





 
Reply With Quote
 
hfarner
Guest
Posts: n/a
 
      18th Sep 2009
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
>
>
>
>
>
>

 
Reply With Quote
 
Patrice
Guest
Posts: n/a
 
      18th Sep 2009
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

 
Reply With Quote
 
Patrice
Guest
Posts: n/a
 
      18th Sep 2009
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:(E-Mail Removed)...
> 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


 
Reply With Quote
 
hfarner
Guest
Posts: n/a
 
      21st Sep 2009
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:(E-Mail Removed)...
> > 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

>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
changing math operations for math operations with = sign filo666 Microsoft Excel Programming 3 3rd Mar 2008 02:35 PM
How Math.Cos & Math.Sin is implemented? Morgan Cheng Microsoft C# .NET 13 17th Oct 2006 04:03 AM
excel math forumla? (simple math problem inside for math people!) Jason Microsoft Excel Discussion 3 16th Feb 2006 11:54 AM
DataSet.Merge with Column.Expression. expression not =?Utf-8?B?R2llZHJpdXM=?= Microsoft ADO .NET 4 12th Jan 2005 10:45 AM
Excel Basic Math Spreadsheet Calculation Error? S. Reiman Microsoft Excel Worksheet Functions 3 2nd Mar 2004 03:47 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 10:58 PM.