Which Function

[rssl2000]

Hello,
I'm trying to figure out which function or combination of functions to use. I'm trying to take the average from one column of data, but the data is chosen from another column of data, but in the same row. In the example below, column A is the criteria, but I want the number averaged to come from row B. So if I choose "1", the average is (15+19)=17.

A B
1 15
2 13
3 10
1 19
2 22
3 17

Thanks, Adam

 

[rssl2000]

I'm wondering if I'm event taking the best approach to the issue.

 

[Joe4]

This is all based on one particular year, the year 2000.

Then I fail to understand how you could have a Beginning value (348) that is greater than your Ending value (15).
Why would you even have that situation? What is that supposed to mean?
By definition, the Beginning value should not be less than the Ending value.

So I am not sure i understand that you are trying to do with that.
Are you trying to take the average of two distinctly unrelated time periods at once (i.e. the first 15 days of the year, and the last 18 days of the year)?

 

[rssl2000]

Correct, I'm looking at utility bills. And most times the bill start in December and end in January. So I need both December and January dates to create my average.

 

[Joe4]

OK, then we need to check to see if T12 is greater than U12, and if so, we need to average the two end ranges.
It looks a little messy, but this should work:

=IF([COLOR=#0000ff]T12>U12[/COLOR],[COLOR=#ff0000](SUMIFS($F$2:$F$8761,$B$2:$B$8761,">=" & T12)+SUMIFS($F$2:$F$8761,$B$2:$B$8761,"<=" & U12))/(COUNTIF($B$2:$B$8761,">=" & T12)+COUNTIF($B$2:$B$8761,"<=" & U12))[/COLOR][COLOR=#008000],AVERAGEIFS($F$2:$F$8761,$B$2:$B$8761,">=" & T12,$B$2:$B$8761,"<=" & U12))[/COLOR]

Since we are now average an OR situation (less than the smaller number combined with greater than the larger number), we have to manually do the average by summing up all the values and dividing by the counts.
I color coded it so you can more easily see the different sections.

 

[rssl2000]

Thank you for your help. I will give this a try and let you know if I have anymore questions.