Cells:
A ¦ B
2 ¦ 10
3 ¦ 12
4 ¦ 16
I'm looking for an array formula which:
1)checks for rows whose value in column B is smaller than 15
2)for the applicable rows it divides value from column "B" by the one in "A"
3)in the end it shows average of all divisions
so 10/2+12/3=9
9/2=4,5
What I came up with:
=SUM(IF(B1:B3<15;B1:B3/A1:A3))/COUNTIF(B1:B3;"<15")))
followed by Ctrl+Shift+Enter
is quite complicated.
Is there any simpler way to perform this operation without having to repeat the "<15" condition? I'm trying to find a way to facilitate calculations for tables with more conditions.
Thanks for any suggestions
Matt
Bookmarks