Division only to visible cells

[damav78]

Hi, i have a table where at the end of it i have a division formula in order to take the average.
The problem here is that i cannot use average type because it is an average depending on quantity. So i use a simple formula =SUM(W7:W153)/SUM(R7:R153)
(W for quantity, R for price)
My qusetion is how can i make it work only in visible-filtered cells and not at the whole sheet.
Thank you

 

[Snakehips]

@damav78 Try using AGGREGATE?

Book3
	R	S	T	U	V	W	X	Y
1	Price					Qty		Calc Visibles
2								1.60869565
3
4
5
6
7	2					1
8	4					2
9	2					3
10	3					4
11	2					5
12	2					6
13	2					7
14	2					8
18	4					1
19
20
21
Sheet1

Cell Formulas
Range	Formula
Y2	Y2	=AGGREGATE(9,5,W7:W153)/AGGREGATE(9,5,R7:R153)

HTH

 

[damav78]

@damav78 Try using AGGREGATE?

Book3
	R	S	T	U	V	W	X	Y
1	Price					Qty		Calc Visibles
2								1.60869565
3
4
5
6
7	2					1
8	4					2
9	2					3
10	3					4
11	2					5
12	2					6
13	2					7
14	2					8
18	4					1
19
20
21
Sheet1

Cell Formulas
Range	Formula
Y2	Y2	=AGGREGATE(9,5,W7:W153)/AGGREGATE(9,5,R7:R153)

HTH

I think it works fine. Thank you