I am trying to find the average of a range of cells that fall between a predefined/criteria range (i.e. 0-2) but treating the end of the range (2 - in this example) as the maximum amount to be used in calculating the average over the range of cells in excel.
Here is an example of the data I am interested finding the average for:
| COST |
|---|
| 2 |
| 3 |
| 5 |
| 7 |
If finding the average using the range of 0-2, it should be 2, as you'd be treating anything with the cost of 2 as 2. So, the total is 2 2 2 2=8 and there are 4 cells so the average would be 2.
Another example would find the average using the range of 0-3, the average would be 2.75, as the first value in the above table falls between the criteria range and the rest match or fall outside the high end of of range so they are assigned the maximum of 3. So, the total is 2 3 3 3=11 and there are 4 cells so the average would be 2.75.
CodePudding user response：
You can try:
=SUM(IF(A2:A5>=C2,C2,A2:A5))/COUNT(A2:A5)
in pre-O365 it must be entered as array formula with Ctrl Shift Enter
