calculation of avg by excluding of certain numbers

[beck_bakytbek]

Hi Folks, 

i got a question, my tables does look like:

id	number1
1	5
2	7
3	3
4	12
5	5
6	8

The average of all numbers is 6,67. My issue is, i want to calculate the average by excluding the numbers: 5 ( i want it to do dynamically, 

for example: 

the average of all numbers is 6,67. When i exclude the numbers: 5, then my average is: 7,5.

Does anybody have any idea how to solve this issue, any feedbacks are appreciated.

Thanks a lot

Beck

[Channa]

=avg({<number1-={5}>} number1)

it will help you

[beck_bakytbek]

Channa,

first of all thanks a lot for your help and time,

your solution is very helpful, but i am looking for solution, where i can use the excluding in expression (dynamically) without set expression.

do you have another idea?

[Channa]

Table1:
LOAD
id,
number1
FROM [lib://TEST/exclude.xlsx]
(ooxml, embedded labels, table is Sheet1);

Table2:
Load id as ID1,
number1 as Number2 Resident Table1 where number1<>5;

Just take AVG(Number2) take QVF

[beck_bakytbek]

Channa thanks a lot for your help and time