Calculating Weighted Average of a Data Subset

[Oxon258]

Hi,

I have a dataset with a variable versus depth (see below example). I want to calculate a weighted average of a calculated subset of these data, for example, the weighted average from 3.2 to, say, 8.7. I know how to calculate the weighted average of the full dataset, but not a subset, which will be determined by other parameters where the output will be the depth range (in this example 3.2 and 8.7). Any one have any ideas?
[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]1[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]169[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]0.1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]0.25[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]

 

[AhoyNC]

Assuming column A are the weights try:
Formula in D5

=SUMPRODUCT(--($B$2:$B$11>=$D$2),--($B$2:$B$11<=$D$3),$A$2:$A$11,$B$2:$B$11)/SUMPRODUCT(--($B$2:$B$11>=$D$2),--($B$2:$B$11<=$D$3),$A$2:$A$11)

[TABLE="width: 245"]
<tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: #BDD7EE"]row/col
[/TD]
[TD="class: xl66, width: 64, bgcolor: #BDD7EE"]A
[/TD]
[TD="class: xl66, width: 64, bgcolor: #BDD7EE"]B
[/TD]
[TD="class: xl66, width: 71, bgcolor: #BDD7EE"]C
[/TD]
[TD="class: xl66, width: 64, bgcolor: #BDD7EE"]D
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #BDD7EE"]1
[/TD]
[TD="class: xl63, bgcolor: #D9D9D9"]Weight
[/TD]
[TD="class: xl63, bgcolor: #D9D9D9"]Depth
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #BDD7EE"]2
[/TD]
[TD="class: xl64, bgcolor: transparent"]1
[/TD]
[TD="class: xl64, bgcolor: transparent"]15
[/TD]
[TD="class: xl68, bgcolor: transparent"]Subset
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]3.2
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #BDD7EE"]3
[/TD]
[TD="class: xl64, bgcolor: transparent"]2
[/TD]
[TD="class: xl64, bgcolor: transparent"]2
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]8.7
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #BDD7EE"]4
[/TD]
[TD="class: xl64, bgcolor: transparent"]3
[/TD]
[TD="class: xl64, bgcolor: transparent"]169
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #BDD7EE"]5
[/TD]
[TD="class: xl64, bgcolor: transparent"]4
[/TD]
[TD="class: xl64, bgcolor: transparent"]7
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: yellow, align: right"]5.3333
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #BDD7EE"]6
[/TD]
[TD="class: xl64, bgcolor: transparent"]5
[/TD]
[TD="class: xl64, bgcolor: transparent"]4
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #BDD7EE"]7
[/TD]
[TD="class: xl64, bgcolor: transparent"]6
[/TD]
[TD="class: xl64, bgcolor: transparent"]123
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #BDD7EE"]8
[/TD]
[TD="class: xl64, bgcolor: transparent"]7
[/TD]
[TD="class: xl64, bgcolor: transparent"]0.1
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #BDD7EE"]9
[/TD]
[TD="class: xl64, bgcolor: transparent"]8
[/TD]
[TD="class: xl64, bgcolor: transparent"]0.25
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #BDD7EE"]10
[/TD]
[TD="class: xl64, bgcolor: transparent"]9
[/TD]
[TD="class: xl64, bgcolor: transparent"]30
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #BDD7EE"]11
[/TD]
[TD="class: xl64, bgcolor: transparent"]10
[/TD]
[TD="class: xl64, bgcolor: transparent"]20
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Oxon258]

Hi AhoyNC,

Thanks very much for your response. This has added some useful tips but I probably should've added some more detail to help people understand the probelm clearer. That said, your response helped me think about it better too. I've now added some detail

The values in column A are depth (always >or= depth above) and the values in column B are parameter values (could be >, = or < the above). The weight of each layer would therefore be the difference between each successive layer (see below). What I'm hoping to do is to find the weighted average of a user defined depth subset (example of 6.00 to 19.00 below).

I would like to determine the weighted average of 'Value' in column C over the user specified depth interval (E4:E5). To do this I understand I first need to interpolate the 'Value' at Depths 6.00 and 19.00. Currently I'm using the following to interpolate the 'Value' at depth 6.00 and 19.00:

=INDEX('parameter array')+('Depth'-INDEX('Depth Array','Row'))*(INDEX('parameter array','row'+1)-INDEX('parameter array','row'))/(INDEX('depth array','row+1)-INDEX('depth array','row'))

Where 'row' is =MATCH('depth','depth array')

I now need to determine the weighted average of 'Value' from 'depth' 6.00 to 19.00 allowing for the non linearity of the 'values' between these points. I found it useful to plot 'value' versus 'depth' and then plot two horizontal lines, one at 6.00 and one at 19.00 to help visualise the problem.

It would also be great if I could remove the requirement for depths to increase (e.g. 1.00 and 1.01) and instead have a step change so depths 1.00 and 1.01 could instead be 1.00 and 1.00, without affecting the calculation.

[TABLE="width: 1071"]
<tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Depth[/TD]
[TD]Weight[/TD]
[TD]Value[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0.0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1.00[/TD]
[TD]1[/TD]
[TD]15[/TD]
[TD]Depth Subset[/TD]
[TD]6.00[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1.01[/TD]
[TD]0.01[/TD]
[TD]15[/TD]
[TD][/TD]
[TD]19.00[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3.00[/TD]
[TD]1.99[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]3.01[/TD]
[TD]0.01[/TD]
[TD]100[/TD]
[TD]Weighted Av. over subset[/TD]
[TD]????[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]4.50[/TD]
[TD]1.49[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]4.51[/TD]
[TD]0.01[/TD]
[TD]125[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]8.70[/TD]
[TD]4.19[/TD]
[TD]60[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]8.71[/TD]
[TD]0.01[/TD]
[TD]60[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]15.00[/TD]
[TD]6.29[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]15.01[/TD]
[TD]0.01[/TD]
[TD]70[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]20.00[/TD]
[TD]4.99[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]20.01[/TD]
[TD]0.01[/TD]
[TD]250[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]50.00[/TD]
[TD]29.99[/TD]
[TD]250[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD="colspan: 2"]Weighted Average[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD="align: right"]171.674[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Oxon258]

I have had this one solved by a friend.

I first use the interpolation formula above to calculate the parameter values at 0.1 m intervals. I then simply use AVERAGE(INDEX( "parameter value array",MATCH("top depth"):INDEX("parameter value array",MATCH("bottom depth")))