Solved General Math Question (1 Viewer)

[tmyers]

I am having a really hard time trying to get an expression right in a sub-form that is for display purposes. Maybe some math experts could help out.

I am trying to properly calculate distributor net (DN), customer net (CN), profit and % mark up (GP).
Distributor net was easy, just sum our cost together. Customer net, in a nutshell, is easy as well. Take % markup and divide it by the CN. However, I cant get the query to do it correctly.

I tried initially just having it average the GP then divided the CN by it, but the average verse the...mean? isn't the same. A short example:

GP values are:
5, 7, 6, 9, 4, 10, 8 ,7 and 8%.
Access averages that out to around 7.1%.

Using those same values (shown in a different manner like .95, .92 etc.) in Excel, those same mark up values actually comes out to 5.24%.
If we have a DN of $13763.00 and divide it by the 7.1%, we get something around $14657.08. Whereas by 5.24% we get $14523.70, which is correct based on adding up the individual marked up values.

To get to the 5.24%, in our Excel sheet we simply do the profit divided by the CN then convert it to a percent. I can't do that in my App since CN is already based of GP, so it would create a circular reference.

Anyone have an idea to remedy this? It is a minor thing and is driving me insane.

 

[theDBguy]

Hi. It's hard to follow what you want without seeing actual data. But, to avoid circular references, you simply use the same expression, rather than the result, in your succeeding calculations.

For example, if you want to do A*B, but B means C+D, then you can do this instead of A*B: A*(C+D)

Hope that makes sense...

 

[MajP]

Same here can you show what you have to start with? It is hard to see the calculations. In general you have to be careful when taking averages of averages especially if the samples are different sizes. Not necessarily saying that is what is going on, but looks this way.

Imagine I have two samples
10,20,30,40,50 avg = 30
10, 100 avg = 55
the "average of the averages" is 42.5%
however the sum all numbers is 260 and there are 7 items. So the overall average is 37%.

 

[tmyers]

Here is the sub-form/display:

Here is the data that is feeding it:

Does that help?

 

[MajP]

I am still lost. Not sure how these numbers relate. The only thing I understand is the Cost. Where does GP come from and what does it mean? I thought that was the markup The first has 7%. Does that mean the item was marked up 7%. If that is the case it would be marked up 60.83 and sold for 929.83. So what does 85 and 935 represent?

 

[tmyers]

I am still lost. Not sure how these numbers relate. The only thing I understand is the Cost. Where does GP come from and what does it mean? I thought that was the markup The first has 7%. Does that mean the item was marked up 7%. If that is the case it would be marked up 60.83 and sold for 929.83. So what does 85 and 935 represent?

What I get for quickly doing it and not paying attention. Here is the full set:

Ext cost is just [Cost] * [Qty]. [CN] is [Cost]/(1-[GP]) then extended just multiplies that by quantity.
So line 1 is (11) of an item that is $79/e marked up 7%. That makes the $85 which then multiplied by 11 is $935.
Does that make more sense? I tend to be bad at explaining.

 

[MajP]

You did do exactly what I thought. If you avg GP it sums up all the GP and divides by 9. So that gives you the average GP per order but not the average GP per item. But you are concerned about GP per item which is the GP on total sales. You applied 7% 11 times, 6 % 15 times, 9% 4 times...... 5% 1 time.

You can simply take

1 - (sum(cost) /sum(extended))
1 - 13763 / 14511
1 - .948
.0515 5.2 %

However I think you have a problem in C/N because I get 14524 and that gives me a more accurate 5.24%

 

[tmyers]

You did do exactly what I thought. If you avg GP it sums up all the GP and divides by 9. So that gives you the average GP per order but not the average GP per item. But you are concerned about GP per item which is the GP on total sales. You applied 7% 11 times, 6 % 15 times, 9% 4 times...... 5% 1 time.

You can simply take

1 - (sum(cost) /sum(extended))
1 - 13763 / 14511
1 - .948
.0515 5.2 %

However I think you have a problem in C/N because I get 14524 and that gives me a more accurate 5.24%

You were correct. I have to completely redesign the query due to when I fixed it, I had circular references. I did the majority of the math within the controls on the form with the query providing the values. It now shows correctly.