Calculate average based on different criteria in excel vba

Question

I am new to excel vba. I have a huge set of data for which I have to calculate averages corresponding to each id. Upon search, I came to know that it can be easily done by using excel AverageIf. But I have to calculate average of numbers which are below Threshold for an id.

Below is the screenshot of sample data. For example, when I calculate the average of Price for id 1, I will only consider Price values which are below 70 for average calculation as 70 is Threshold and any value above 70 cannot be included in calculation and same goes for all other ids. I would appreciate any ideas in this regard.

Answer 1

AVERAGEIFS should do the job:

=AVERAGEIFS(B2:B15,A2:A15,1,B2:B15,"<="&C2)

Answer 2

I like the countifs but here is another way using a helper column to determine if to include in average calculation. Assumes data set up as above without repeating Threshold value against each id.

1) Convert your data to a table with Ctrl+T (with a cell in the range selected).

2) In top right cell under header Average put

=IF(B2<INDEX($C$2:$C$13,MATCH(A2,$A$2:$A$13,0)),1,"")

It will autofill down all the rows in that column. Tailor the ranges to your actual ranges. These ranges match the image below.

3) Select a cell in the table and press Alt + N + V to generate a pivottable and place it by the side of the current table with a 2 column gap.

4) Add id to the rows and price to the Values field. The add Average to the page field and filter on 1. Then right click on price in the Values area and select Summarize value field by Average.

5) You can add more rows to the table and the formula will autocomplete so you only need to refresh the pivot to get new averages.

Note:

You could also have used a lookup table to retrieve the thresholds.