AVERAGEIF of SMALL with multiple criteria

[gpnm25]

Hi all!

Long time lurker...first time poster.

I'm not an excel expert, but certainly not a noobie.

So this is very, very frustrating.

I have a very large dataset -- around 25K rows (probably more). I have a bunch of fields including STATE, AGE, GENDER, and RATING AREA. In addition, I have a field with insurance premiums.

What I want to do is take the average of the 5 lowest insurance premiums for every state, age, gender, and rating area. I have another table where I'm trying to plug this in.

The formula that I've tried (and failed at using) is:

=AVERAGEIF(small(Table2[Premiums],{1,2,3,4,5}),Table2[State],[@State]...

I just get a message from excel saying that there's an error in my formula...

Table 2 is the table with all of the information, and I'm plugging this formula into a table that has the proper qualifiers (Age, Gender, Rating Area, State).

Help???

 

[Oeldere]

Why not use a pivot table for that kind of work?

 

[gpnm25]

I've tried with a pivot table, but I'm having trouble getting the actual values for the premiums to show rather than a calculation (ideally, I'd be able to do it as a calculated field within the pivot table, but it's not letting me use small(premiums,{1,2,3,4,5}) in the pivot table).

 

[gpnm25]

As an FYI, in case anyone else runs into the same trouble.

I ended up using a pivot table to display the 5 cheapest premiums for each combination of qualifiers (using the "Top 10" filter option). I reconfigured the pivot table to look more like a regular dataset (took some effort), then copied and pasted into a separate excel sheet. From there, I was able to pull the information using a simple averageif statement (but without the array that I was trying to use before -- so it was just an average of all the premiums, which ended up now being the 5 cheapest).

Hope this helps someone else down the line!

 

[Aladin Akyurek]

1. Control+shif+enter, not just enter:

=AVERAGE(SMALL(PremiumRange,{1,2,3,4,5}))

2. Just enter:

=AVERAGEIF(PremiumRange,"<="&SMALL(PremiumRange,5))

 

[gpnm25]

Ahh, so AVERAGEIF can't be pasted as an array formula?

 

[Aladin Akyurek]

Ahh, so AVERAGEIF can't be pasted as an array formula?

No need for that. AVERAGEIF is a range-processing function, not one that process arrays.