Weighted Averages

[Guest]

Hello everyone;

I have a query which links drilling sample intervals and geochemical
results. I would like to average these results so that I highlight intervals
with anomalous geochem readings. For example:

From To Length Element A
1 2.5 2.5 231
2.5 3 0.5 122
3 5.25 2.25 2250
5.25 12 6.75 2222
12 14 2 981
14 15.5 1.5 2500

I use the weighted average of the samples, as each sample has a different
length. So:

(Element A x Length)

From To Length Element A Weighted Element A
1 2.5 2.5 231 577.5
2.5 3 0.5 122 61
3 5.25 2.25 2250 5062.5
5.25 12 6.75 2222 14998.5
12 14 2 981 1962
14 15.5 1.5 2500 3750


I am only interested in seeing the averaged intervals which are above 2000.
So right now I eyeball the list, and manually average a series of weighted
Element A values which I believe will produce an average of 2000 or more:

(add the weighted values and divide by the total interval)

(Sum of weighted values)
5062.5+14998.5+1962+3750=25773

(Sum of sample intervals)
2.25+6.75+2+1.5=12.5

(Divide total weighted value by total interval)

25773/12.5=2061.84

So, as you can see, 2061.84 is above my threshold (2000) and I want to
record this interval and its average. This whole process is tedious, and
since I'm manually doing this there is room for human error. I would love to
automate it. Only thing is, I don't know how. Is there anyone out there who
could give me a couple tips on how to program these instructions? Thanks in
advance!

 

[Smartin]

Hello everyone;

I have a query which links drilling sample intervals and geochemical
results. I would like to average these results so that I highlight intervals
with anomalous geochem readings. For example:

From To Length Element A
1 2.5 2.5 231
2.5 3 0.5 122
3 5.25 2.25 2250
5.25 12 6.75 2222
12 14 2 981
14 15.5 1.5 2500

I use the weighted average of the samples, as each sample has a different
length. So:

(Element A x Length)

From To Length Element A Weighted Element A
1 2.5 2.5 231 577.5
2.5 3 0.5 122 61
3 5.25 2.25 2250 5062.5
5.25 12 6.75 2222 14998.5
12 14 2 981 1962
14 15.5 1.5 2500 3750


I am only interested in seeing the averaged intervals which are above 2000.
So right now I eyeball the list, and manually average a series of weighted
Element A values which I believe will produce an average of 2000 or more:

(add the weighted values and divide by the total interval)

(Sum of weighted values)
5062.5+14998.5+1962+3750=25773

(Sum of sample intervals)
2.25+6.75+2+1.5=12.5

(Divide total weighted value by total interval)

25773/12.5=2061.84

So, as you can see, 2061.84 is above my threshold (2000) and I want to
record this interval and its average. This whole process is tedious, and
since I'm manually doing this there is room for human error. I would love to
automate it. Only thing is, I don't know how. Is there anyone out there who
could give me a couple tips on how to program these instructions? Thanks in
advance!

Hello Casa,

I do not totally follow your logic since you include some Weighted
Element A in your example that do not exceed the threshold. However,
this might work for you:

Create a second query that uses the first query as its source. You can
Sum Weighted Element A, Sum Length, and do the division, limiting the
rows involved in a WHERE clause. Something like:

SELECT
Sum([Length]) AS Interval,
Sum([Weighted Element A]) / Sum([Length]) As MyAverage
FROM
Query1
WHERE
[Weighted Element A] > 2000
;

You could also do this all in one query but I think the two-step
approach is much easier.

Hope this helps!