Round fractional numbers for AVERAGEIF whole number Criteria

[loudnoiseman]

Hi Everyone,

My AVERAGEIF range contains fractional numbers but my criteria is whole numbers and I think I need to somehow adapt with a rounding function I suppose so I can get correct average range figures. I'm not sure if pasting this example will work but this illustrates how the fractional numbers are being ignored.

Thanks!

Fractional numbers	To Average			Criteria	AVERAGEIF ignores fractional numbers	formula
1​	10​			1	10	AVERAGEIF(A2:A22,E2,B2:B22)
1.2​	100​			2	500	AVERAGEIF(A2:A22,E3,B2:B22)
1.4​	200​			3	1000	AVERAGEIF(A2:A22,E4,B2:B22)
1.6​	300​			4	1500	AVERAGEIF(A2:A22,E5,B2:B22)
1.8​	400​			5	2000	AVERAGEIF(A2:A22,E6,B2:B22)
2​	500​
2.2​	600​
2.4​	700​
2.6​	800​
2.8​	900​
3​	1000​
3.2​	1100​
3.4​	1200​
3.6​	1300​
3.8​	1400​
4​	1500​
4.2​	1600​
4.4​	1700​
4.6​	1800​
4.8​	1900​
5​	2000​

 

[Toadstool]

Hi Loudnoiseman,

AVERAGEIF parameters are range,criteria,average_range so your statement in F2 is saying "Look at A2 to A22 and only if it is equal to E2 (the value 1) then average values in B2 to B22".

Only one value in column A is a 1 so it returns the value in that row of B. Same for criteria 2,3,4 and 5.

Please state what you want to be averaged.

 

[Fluff]

How about

=AVERAGEIFS(B:B,A:A,">="&E2,A:A,"<"&E2+1)

 

[loudnoiseman]

Hi Toadstool,

In the first formula, criteria is 1 so yes it's looking for exactly 1 in A:A whereas I'm hoping to modify the formula to round to the nearest whole number in A:A so that 1.2 and 1.4 are also considered 1 and 1.6 and 1.8 are considered 2 thus including associated B:B in the average. I believe the correct answer for criteria 1 is 103.3333333.

I get that I could achieve this with helper columns but my dataset is already almost 400 columns and want to avoid doubling and tripling if I can avoid it.

Hi Fluff,

I'm getting an error with your suggestion but thanks anyway!

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also what error do you get?

 

[loudnoiseman]

Hi Fluff,

Thanks, I just updated details, I'm using 365 on Windows. The error I got was #DIV/0!

But I do apologize as I copied this post data back into Excel and it's incorrectly formatted as text and not numbers thus the error.

Now I re-created as numbers and I see your formula does work after all and it's rounding down so all 1.x = 1 and 2.x = 2 etc which works for my purposes so indeed thank you very much!!

Loudnoiseman

 

[Fluff]

I just updated details

Thanks for that.

Do you want to round the numbers so that 1.6 is averaged as part of the 2, or do you want it averaged as part of the 1?

 

[loudnoiseman]

Yes, actually I prefer to round to the closest whole number so 1.6 would be treated as 2.

Thanks!!

 

[Fluff]

Ok how about

+Fluff 1.xlsm
	A	B	C	D	E	F
1	Fractional numbers	To Average			Criteria	AVERAGEIF ignores fractional numbers
2	1	10			1	103.3333333
3	1.2	100			2	500
4	1.4	200			3	1000
5	1.6	300			4	1500
6	1.8	400			5	1900
7	2	500
8	2.2	600
9	2.4	700
10	2.6	800
11	2.8	900
12	3	1000
13	3.2	1100
14	3.4	1200
15	3.6	1300
16	3.8	1400
17	4	1500
18	4.2	1600
19	4.4	1700
20	4.6	1800
21	4.8	1900
22	5	2000
23
Main

Cell Formulas
Range	Formula
F2:F6	F2	=AVERAGE(FILTER($B$2:$B$22,ROUND($A$2:$A$22,0)=E2))