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]

What exactly do you entries in column B look like? Its a row of numbers generated by this formula, =C7-DATE(YEAR(C7),1,0)By "u22", do you mean the literal text "u22", or what is in cell u22? It means cell u22. Sorry, for some reason my formala didn't include the <, > or =, see below. When I enter >U23 into the Function Argument is adds the " ".

=AVERAGEIFS(F2:F8761,B2:B8761,"<T23",B2:B8761,">U23",B2:B8761,"<=1")

 

[Joe4]

If you want > U23, then you need to write it like this:

=AVERAGEIFS(F2:F8761,B2:B8761,[COLOR=#ff0000]">" & U23[/COLOR],B2:B8761,"<=1")

 

[rssl2000]

Thanks, that worked.

 

[rssl2000]

One other question. The range of data I'm looking at is 1-365, each number represented a day of the year. Typically the first number (Beg) is smaller then the last number (End), see example below. But when the numbers are reversed (last data in set), the function returns #DIV/0!. I think this is because the data set that I'm comparing those two numbers too is from 1 to 365. So when I say I want all number less then 15 or greater then 348, this causes the error. I tried to include >=1 as another criteria, hoping this could only look at numbers 1 and above, but no luck. Any ideas if that makes sense?

Beg	End
257	285
226	256
195	225
165	194
132	164
103	131
73	102
44	72
16	43
348	15

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Joe4]

OK, so how exactly does this Beg/End grid you just introduced now relate to your formula?

 

[rssl2000]

My goal is to return an average outdoor air temp based on the two numbers. $F$2:$F$8761 is the outdoor air temperature for each hour of the year. I find all the days/hours between and including those two numbers to find the average. In the formula below, T=Beg, U=End. The numbers in the table represent dates, at first I couldn't figure out how to use dates, so I converted the dates to numbers, Jan 1=1, Jan 2=2, December 31=365, etc. $B$2:$B$8761 is hourly data for an entire year, 8,760 hours.

=AVERAGEIFS($F$2:$F$8761,$B$2:$B$8761,">=" & T12,$B$2:$B$8761,"<=" & U12)

B C D E F Month Hour DAT Rounded 1 1-Jan 0 42.08 42 1 1-Jan 1 33.08 33 1 1-Jan 2 37.04 37 1 1-Jan 3 35.06 35 1 1-Jan 4 28.94 29 1 1-Jan 5 30.02 30 1 1-Jan 6 30.02 30 1 1-Jan 7 32.00 32 1 1-Jan 8 32.00 32 1 1-Jan 9 39.02 39 1 1-Jan 10 46.94 47 1 1-Jan 11 51.08 51 1 1-Jan 12 53.96 54 1 1-Jan 13 57.02 57 1 1-Jan 14 57.92 58 1 1-Jan 15 57.00 57 1 1-Jan 16 57.00 57 1 1-Jan 17 57.00 57 1 1-Jan 18 56.00 56 1 1-Jan 19 56.00 56 1 1-Jan 20 56.00 56 1 1-Jan 21 56.00 56 1 1-Jan 22 56.93 57 1 1-Jan 23 55.94 56 2 2-Jan 0 55.04 55 2 2-Jan 1 55.94 56 2 2-Jan 2 55.04 55 2 2-Jan 3 53.96 54 2 2-Jan 4 53.96 54 2 2-Jan 5 53.96 54 2 2-Jan 6 55.04 55 2 2-Jan 7 57.00 57 2 2-Jan 8 57.00 57 2 2-Jan 9 57.00 57 2 2-Jan 10 60.00 60 2 2-Jan 11 60.00 60 <colgroup><col><col span="2"><col><col></colgroup><tbody> </tbody>

<colgroup><col><col span="2"><col><col></colgroup><tbody>
</tbody>

 

[Joe4]

at first I couldn't figure out how to use dates, so I converted the dates to numbers

So that does mean you have everything figured out now, or is there still sort sort of issue/question?

By the way, you should be able to use dates. If Column C has valid dates, and cells T12 and U12 are valid dates, then this formula would work:

=AVERAGEIFS($F$2:$F$8761,$C$2:$C$8761,">=" & T12,$C$2:$C$8761,"<=" & U12)

Just note that even if you do not display the year in your Date format on the Excel screen, the year is still considered, as it is part of the date value.

 

[rssl2000]

Thanks, but I'm still trying to work through the issue four posts above, see below.

One other question. The range of data I'm looking at is 1-365, each number represented a day of the year. Typically the first number (Beg) is smaller then the last number (End), see example below. But when the numbers are reversed (last data in set), the function returns #DIV/0!. I think this is because the data set that I'm comparing those two numbers too is from 1 to 365. So when I say I want all number less then 15 or greater then 348, this causes the error. I tried to include >=1 as another criteria, hoping this could only look at numbers 1 and above, but no luck. Any ideas if that makes sense?

 

[Joe4]

Let me ask an important question.
Are you trying to find the average temperature for a single time period, or for across ALL plan years?
For example, let's say you wanted the average temperature for January. Would you be looking the average of January for one particular year, or across ALL years?

 

[rssl2000]

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