Math Error in dataset expression calculation

[hfarner]

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]

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)

 

[Patrice]

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 %)) ???

 

[hfarner]

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

 

[hfarner]

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

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

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]

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 ???

 

[hfarner]

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]

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]

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

Cost/(1-PCT) ?

 

[hfarner]

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