Excel AVERAGEIF formula logic - Stack Overflow

admin管理员组

文章数量:1289845

I'm having a hard time coming up with the logic criteria I need to get the formula to produce a four hour average when I have at least three of the previous four hours of data.

For example, I have 24 hourly averages as source data. I have to create a 4 hour average for each four hour block. The four hour average is only valid if I have data for at least 3 out of each 4 hours.

IE:

I'm having a hard time coming up with the logic criteria I need to get the formula to produce a four hour average when I have at least three of the previous four hours of data.

For example, I have 24 hourly averages as source data. I have to create a 4 hour average for each four hour block. The four hour average is only valid if I have data for at least 3 out of each 4 hours.

IE:

• excel
• excel-formula

Share Improve this question Follow edited Feb 20 at 19:12 BigBen 50.2k77 gold badges2828 silver badges4444 bronze badges asked Feb 20 at 19:11 BBA HunterBBA Hunter 1144 bronze badges 1

• 1 Why do hours 3 and 20 have corresponding results? – BigBen Commented Feb 20 at 21:13

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 2

=IF(A2:A25<3,"",MAP(B2:B25,LAMBDA(b,LET(c,TOCOL(--TAKE(B2:b,-4),2),IF(ROWS(c)<3,"",SUM(c)/ROWS(c))))))

Value 20 would not return an average since you have less than 3 values

Or like TomSharpe mentioned: =IF(A2:A25<3,"",MAP(B2:B25,LAMBDA(b,LET(c,TOCOL(--TAKE(B2:b,-4),2),IF(ROWS(c)<3,"",AVERAGE(c))))))

Paste this in a new sheet:

Hour Number	Data Value	4 Hr Average
1	3245
2	3544
3	654	=IF(COUNTIF(B1:B4,">="&0)>2,SUM(B1:B4)/4,"No valid data")
4	2345	=IF(COUNTIF(B2:B5,">="&0)>2,SUM(B2:B5)/4,"No valid data")
5	654	=IF(COUNTIF(B3:B6,">="&0)>2,SUM(B3:B6)/4,"No valid data")
6	9876	=IF(COUNTIF(B4:B7,">="&0)>2,SUM(B4:B7)/4,"No valid data")
7	89709	=IF(COUNTIF(B5:B8,">="&0)>2,SUM(B5:B8)/4,"No valid data")
8	4653	=IF(COUNTIF(B6:B9,">="&0)>2,SUM(B6:B9)/4,"No valid data")
9	56789	=IF(COUNTIF(B7:B10,">="&0)>2,SUM(B7:B10)/4,"No valid data")
10	78655	=IF(COUNTIF(B8:B11,">="&0)>2,SUM(B8:B11)/4,"No valid data")
11	456564	=IF(COUNTIF(B9:B12,">="&0)>2,SUM(B9:B12)/4,"No valid data")
12	768	=IF(COUNTIF(B10:B13,">="&0)>2,SUM(B10:B13)/4,"No valid data")
13	9987	=IF(COUNTIF(B11:B14,">="&0)>2,SUM(B11:B14)/4,"No valid data")
14	657876	=IF(COUNTIF(B12:B15,">="&0)>2,SUM(B12:B15)/4,"No valid data")
15	67766	=IF(COUNTIF(B13:B16,">="&0)>2,SUM(B13:B16)/4,"No valid data")
16	67576	=IF(COUNTIF(B14:B17,">="&0)>2,SUM(B14:B17)/4,"No valid data")
17	No valid data	=IF(COUNTIF(B15:B18,">="&0)>2,SUM(B15:B18)/4,"No valid data")
18	No Valid Data	=IF(COUNTIF(B16:B19,">="&0)>2,SUM(B16:B19)/4,"No valid data")
19	90832	=IF(COUNTIF(B17:B20,">="&0)>2,SUM(B17:B20)/4,"No valid data")
20	98992	=IF(COUNTIF(B18:B21,">="&0)>2,SUM(B18:B21)/4,"No valid data")
21	794567	=IF(COUNTIF(B19:B22,">="&0)>2,SUM(B19:B22)/4,"No valid data")
22	65398	=IF(COUNTIF(B20:B23,">="&0)>2,SUM(B20:B23)/4,"No valid data")
23	345	=IF(COUNTIF(B21:B24,">="&0)>2,SUM(B21:B24)/4,"No valid data")
24	21	=IF(COUNTIF(B22:B25,">="&0)>2,SUM(B22:B25)/4,"No valid data")

Assuming the data values to be in cells B2:B25 put the following formula into cell C4 and copy down: =IF(COUNT(B1:B4)>=3,SUM(B1:B4)/4,"")

Let us know if you have any questions.

本文标签： Excel AVERAGEIF formula logicStack Overflow