getting a weighted average of two columns filtered for dates

[Jimbs4]

Hi,

I have a specific problem that I have been searching the forum but cannot seem to find the answer. I have three columns, one has the date, one has tonnes of dirt and the other has the moisture content within the dirt in percentages. What I am trying to do is get a weighted average of the percentage of moisture as measured for the dirt from a single day. I have succeeded so far to total the tonnes according to any given day by using a SUMIFS formula and specifying the date. I understand the simple way of getting the weighted average using the sum product however I would like the formula to look at the range according to the dates. I have attached the table I have. I feel like I need a SUMPRODUCTSIF however it does not exist, Hope someone can help with this

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 197"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]date[/TD]
[TD]tonnes[/TD]
[TD] moisture %[/TD]
[/TR]
[TR]
[TD="align: right"]1/11/2015[/TD]
[TD="align: right"]129.5[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]1/11/2015[/TD]
[TD="align: right"]103.6[/TD]
[TD="align: right"]1.51[/TD]
[/TR]
[TR]
[TD="align: right"]1/11/2015[/TD]
[TD="align: right"]259[/TD]
[TD="align: right"]0.87[/TD]
[/TR]
[TR]
[TD="align: right"]1/11/2015[/TD]
[TD="align: right"]129.5[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2/11/2015[/TD]
[TD="align: right"]129.5[/TD]
[TD="align: right"]0.97[/TD]
[/TR]
[TR]
[TD="align: right"]2/11/2015[/TD]
[TD="align: right"]129.5[/TD]
[TD="align: right"]0.97[/TD]
[/TR]
[TR]
[TD="align: right"]2/11/2015[/TD]
[TD="align: right"]129.5[/TD]
[TD="align: right"]0.97[/TD]
[/TR]
[TR]
[TD="align: right"]2/11/2015[/TD]
[TD="align: right"]129.5[/TD]
[TD="align: right"]1.51[/TD]
[/TR]
[TR]
[TD="align: right"]3/11/2015[/TD]
[TD="align: right"]129.5[/TD]
[TD="align: right"]0.97[/TD]
[/TR]
[TR]
[TD="align: right"]3/11/2015[/TD]
[TD="align: right"]129.5[/TD]
[TD="align: right"]0.97[/TD]
[/TR]
[TR]
[TD="align: right"]3/11/2015[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]0.97[/TD]
[/TR]
[TR]
[TD="align: right"]3/11/2015[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]0.97[/TD]
[/TR]
[TR]
[TD="align: right"]4/11/2015[/TD]
[TD="align: right"]129.5[/TD]
[TD="align: right"]0.97[/TD]
[/TR]
[TR]
[TD="align: right"]4/11/2015[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]2.96[/TD]
[/TR]
[TR]
[TD="align: right"]4/11/2015[/TD]
[TD="align: right"]129.5[/TD]
[TD="align: right"]2.96[/TD]
[/TR]
[TR]
[TD="align: right"]4/11/2015[/TD]
[TD="align: right"]129.5[/TD]
[TD="align: right"]1.51[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[MarcelBeug]

If the required date is in F1 and data in A2:C17:

=SUMPRODUCT(($A$2:$A$17=$F$1)*$B$2:$B$17*$C$2:$C$17)/SUMIF($A$2:$A$17,$F$1,$B$2:$B$17)

 

[Jimbs4]

Hi Marcel,

Thank you very much, that seems to have solved my problem however it may had led me to another. If I then want to categorize by putting the tonnes and moisture into packages based on parameters, can it be done? i.e. working it out manually I would have 362.6 tonnes with a moisture content over 1% on the 1st of November. Thanks in advance

 

[MarcelBeug]

Not sure what you mean. If I work it out with Excel , I get 621.6 tonnes on November 1st with an average moisture of 1,45%.

Anyhow I made a summary list with, date, total tonnes, average moisture and category in columns E-H with

E2 (array formula): =MIN(IF($A$2:$A$17>SUM($E1),$A$2:$A$17))
F2: =SUMIF($A$2:$A$17,$E2,$B$2:$B$17)
G2: =SUMPRODUCT(($A$2:$A$17=$E2)*$B$2:$B$17*$C$2:$C$17)/F2
H2: =VLOOKUP(G2,$J$2:$K$4,2)

copied down for as many different dates you have.

I just made up some categories myself in J2:K4 with lower limit (for average moisture) and description:
0 Dry
1 Moist
2 Wet

Hopefully this includes the information you're looking for.

 

[Jimbs4]

Thanks again Marcel, to be clearer, yes you'll get 621.6 tonnes in total @ 1.45% however that includes the tonnes that have a moisture content below 1%. I have the tonnes separated into separate tonnes for the 1st, i'e. two piles, one above 1% and one pile below 1%. I am trying to get the weighted averages for each piles moisture content for that day. Hope this is a bit clearer

 

[MarcelBeug]

Ah!
Well, now I have dates in column E (same as above) and in columns:
F sum of tonnes with moisture < 1
G average moisture < 1
H sum of tonnes with moisture >= 1
H average moisture >=1

F2: =SUMIFS($B$2:$B$17,$A$2:$A$17,$E2,$C$2:$C$17,"<1")
G2: =IF(F2=0,"n.a.",SUMPRODUCT(($A$2:$A$17=$E2)*($C$2:$C$17<1)*$B$2:$B$17*$C$2:$C$17)/F2)
H2: =SUMIFS($B$2:$B$17,$A$2:$A$17,$E2,$C$2:$C$17,">=1")
I2: =IF(H2=0,"n.a.",SUMPRODUCT(($A$2:$A$17=$E2)*($C$2:$C$17>=1)*$B$2:$B$17*$C$2:$C$17)/H2)

 

[Jimbs4]

Thanks again Marcel, I seem to have it now. I am using the formula in I2 to get the results I want. While it works great for values above 1% I was also hoping to get it to work for values with a range. As mentioned I used the formula for above 1% >=1 and I thought it would work if I substituted <1>0.5 in place of >=1 to get the weighted averages less than 1 but greater that 0.5. Am I doing it wrong?

 

[MarcelBeug]

No, if you have a lower limit and an upper limit, these are 2 separate criteria.
If you have a set of ranges, it's not a good idea to have these "hard coded" in your formulas.
So I added 2 header lines for the range definitions: row 1 with labels From and To, row 2 with the values.
Original headers now in row 3, so data starting in row 4.

F4: =SUMIFS($B$4:$B$19,$A$4:$A$19,$E4,$C$4:$C$19,">="&F$2,$C$4:$C$19,"<"&G$2)
G4: =IF(F4=0,"n.a.",SOMPRODUCT(($A$4:$A$19=$E4)*($C$4:$C$19>=F$2)*($C$4:$C$19 < G$2)*$B$4:$B$19*$C$4:$C$19)/F4)

You can copy columns F and G for all your ranges. Adjust the from and to values accordingly. Also the lowest and highest range must include both a lower limit and an upper limit.

 

[Jimbs4]

Thanks Marcel, got it all all sorted