Array formula - Weighted Average Price of filtered data above .01 cent

[lshub]

I am having trouble modifying a formula I built out to calculate what the weighted average price of a filtered set of data is. After a lot of trial and error, I was able to successfully create an array formula that could do the above. However, I now want to add if parameters to the formula that will ignore any blanks or prices at or below .01 cent. I've searched for an answer and tinkered with the formula for hours and just can't seem to get it exactly right.

Here is what I am working off of right now:
Column R is pricing and Column G is store volume.

{=SUM(SUBTOTAL(9,OFFSET(R8:R2900,ROW(R8:R2900)-MIN(ROW(R8:R2900)),0,1))*SUBTOTAL(9,OFFSET(G8:G2900,ROW(G8:G2900)-MIN(ROW(G8:G2900)),0,1)))/SUBTOTAL(9,G8:G2900)}

Anyone know what I would need to add/change to make that possible?

 

[lshub]

Re: Array forumla - Weighted Average Price of filtered data above .01 cent

That is...

=SUMPRODUCT(SUBTOTAL(9,OFFSET(G8,ROW(G8:G2900)-ROW(G8),0,1)),
     --ISNUMBER(R8:R2900),--(R8:R2900>0.1),R8:R2900)/
  SUMPRODUCT(SUBTOTAL(9,OFFSET(G8,ROW(G8:G2900)-ROW(G8),0,1)),
     --ISNUMBER(R8:R2900),--(R8:R2900>0.1))

That worked perfectly, thank you!

 

[Aladin Akyurek]

Re: Array forumla - Weighted Average Price of filtered data above .01 cent

That worked perfectly, thank you!

You are welcome.