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?

 

[Aladin Akyurek]

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

Does this work for you?

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

which needs just enter.

 

[lshub]

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

Does this work for you?

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

which needs just enter.

Unfortunately it does not. I get the same answer as the above formula, the weighted average price with the .01 cent prices included in the calculation. But thank you for the simplified formula that accomplishes what the much longer formula I have does!!

 

[Aladin Akyurek]

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

Unfortunately it does not. I get the same answer as the above formula, the weighted average price with the .01 cent prices included in the calculation. But thank you for the simplified formula that accomplishes what the much longer formula I have does!!

Given what follows, filtered with Z = a

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

G​

[/td][td]

R​

[/td][td]

S​

[/td][/tr]
[tr][td]

4​

[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

5​

[/td][td][/td][td]

0.025806​

[/td][td][/td][/tr]


[tr][td]

6​

[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

7​

[/td][td]X[/td][td]Y[/td][td]Z[/td][/tr]


[tr][td]

8​

[/td][td]

2​

[/td][td]

0.1​

[/td][td]a[/td][/tr]


[tr][td]

9​

[/td][td]

4​

[/td][td]

0.2​

[/td][td]a[/td][/tr]


[tr][td]

10​

[/td][td]

5​

[/td][td]

0.1​

[/td][td]a[/td][/tr]


[tr][td]

12​

[/td][td]

10​

[/td][td][/td][td]a[/td][/tr]


[tr][td]

13​

[/td][td]

10​

[/td][td][/td][td]a[/td][/tr]


[tr][td]

14​

[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

15​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

if the value in R5 is not correct, what result would you be expecting?

 

[lshub]

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

Given what follows, filtered with Z = a

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

G​

[/td][td]

R​

[/td][td]

S​

[/td][/tr]
[tr][td]

4​

[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

5​

[/td][td][/td][td]

0.025806​

[/td][td][/td][/tr]


[tr][td]

6​

[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

7​

[/td][td]X[/td][td]Y[/td][td]Z[/td][/tr]


[tr][td]

8​

[/td][td]

2​

[/td][td]

0.1​

[/td][td]a[/td][/tr]


[tr][td]

9​

[/td][td]

4​

[/td][td]

0.2​

[/td][td]a[/td][/tr]


[tr][td]

10​

[/td][td]

5​

[/td][td]

0.1​

[/td][td]a[/td][/tr]


[tr][td]

12​

[/td][td]

10​

[/td][td][/td][td]a[/td][/tr]


[tr][td]

13​

[/td][td]

10​

[/td][td][/td][td]a[/td][/tr]


[tr][td]

14​

[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

15​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

if the value in R5 is not correct, what result would you be expecting?

I'm sorry, I am not sure I follow the above data set. I entered it into excel and got a different result. But I might have been unclear with what I was trying to accomplish in my original question, so I can break down my data set to show you what I am trying to accomplish.



[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

G​

[/td][td]

R​

[/td][/tr]

[tr][td]

4​

[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

5​

[/td][td][/td][td]

$3.50​

[/td][td][/td][/tr]


[tr][td]

6​

[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

7​

[/td][td]Units[/td][td]Price[/td][/tr]


[tr][td]

515​

[/td][td]

85 Units​

[/td][td]

$3.47​

[/td][/tr]


[tr][td]

2092​

[/td][td]

34 Units​

[/td][td]

$0.01​

[/td][/tr]


[tr][td]

2800​

[/td][td]

44 Units​

[/td][td]

$3.56​

[/td][/tr]

[tr][td]

2884​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

I filtered the data down to these 3 stores, with one that has a .01 cent price. I want the formula to completely ignore row G2092 and column R2092 in the calculation and only consider the other two sales/prices. I am not sure if I can even do that. Essentially, I am trying to create a summary sheet page that can provide the weighted average price (by volume) that can then be filtered by 32 other values (i.e. zip code). The .01 cent values skew the final average, but I can not remove them from the data set and would like to avoid for others who receive the summary to have to filter and re-filter because of the .01 cent values.

I want the R5 value in this scenario to be $3.50, however, the result I get is $2.77, which includes the store with the .01 cent price in the calculation. Using both the formula I posted and the one you provided, the result is $2.77. Is there even a way to change the formula to ignore G2092 an R2092 when it calculates the weighted average?

 

[asad]

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

Sorry for jumping in Sir Aladin, but just a thought. If the above dat is in columns A1:C3 then this formula is working for me. Please correct me if I am wrong.

=SUMPRODUCT(--(C1:C3>0.01),((LEFT(B1:B3,SEARCH(" ",B1:B3))*1)*(C1:C3)))/SUMPRODUCT(--(C1:C3>0.01),((LEFT(B1:B3,SEARCH(" ",B1:B3))*1)))

 

[Aladin Akyurek]

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

I'm sorry, I am not sure I follow the above data set. I entered it into excel and got a different result. But I might have been unclear with what I was trying to accomplish in my original question, so I can break down my data set to show you what I am trying to accomplish.



[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

G​

[/TD]
[TD]

R​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD][/TD]
[TD]

$3.50​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Units[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]

515​

[/TD]
[TD]

85 Units​

[/TD]
[TD]

$3.47​

[/TD]
[/TR]
[TR]
[TD]

2092​

[/TD]
[TD]

34 Units​

[/TD]
[TD]

$0.01​

[/TD]
[/TR]
[TR]
[TD]

2800​

[/TD]
[TD]

44 Units​

[/TD]
[TD]

$3.56​

[/TD]
[/TR]
[TR]
[TD]

2884​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I filtered the data down to these 3 stores, with one that has a .01 cent price. I want the formula to completely ignore row G2092 and column R2092 in the calculation and only consider the other two sales/prices. I am not sure if I can even do that. Essentially, I am trying to create a summary sheet page that can provide the weighted average price (by volume) that can then be filtered by 32 other values (i.e. zip code). The .01 cent values skew the final average, but I can not remove them from the data set and would like to avoid for others who receive the summary to have to filter and re-filter because of the .01 cent values.

I want the R5 value in this scenario to be $3.50, however, the result I get is $2.77, which includes the store with the .01 cent price in the calculation. Using both the formula I posted and the one you provided, the result is $2.77. Is there even a way to change the formula to ignore G2092 an R2092 when it calculates the weighted average?

One should never describe a problem by means of a (non-working) formula as done in the original post. That said, how do you arrive at $3.50? No formula please. I'm asking for the manual procedure which gives $3.50 given the input you posted above.

 

[Aladin Akyurek]

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

Sorry for jumping in Sir Aladin, but just a thought. If the above dat is in columns A1:C3 then this formula is working for me. Please correct me if I am wrong.

=SUMPRODUCT(--(C1:C3>0.01),((LEFT(B1:B3,SEARCH(" ",B1:B3))*1)*(C1:C3)))/SUMPRODUCT(--(C1:C3>0.01),((LEFT(B1:B3,SEARCH(" ",B1:B3))*1)))

Quite possible. One wonders why the OP needs the word Units glued to numbers.

 

[lshub]

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

One should never describe a problem by means of a (non-working) formula as done in the original post. That said, how do you arrive at $3.50? No formula please. I'm asking for the manual procedure which gives $3.50 given the input you posted above.

The units are not necessary, simply added to depict volume. I thought it would be helpful to show the formula that I was trying to build off of, but won't post it next time. The manual math here is:

85 + 44 = 129 (total volume)
85/129 = 65.9% of total volume
.659 * $3.47 = $2.29
44/129 = 34.1% of total volume
.341 * 3.56 = $1.21

$2.29 + $1.21 = $3.50
This gives more weight to the store selling the greater volume in the average price calculation.

The formula that I already posted and the one that you gave me, can conduct this math to arrive at a weighted average with the data filtered, but factor in the middle set of data with the .01 cent price, which gives you $2.77.

 

[Aladin Akyurek]

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

The units are not necessary, simply added to depict volume. I thought it would be helpful to show the formula that I was trying to build off of, but won't post it next time. The manual math here is:

85 + 44 = 129 (total volume)
85/129 = 65.9% of total volume
.659 * $3.47 = $2.29
44/129 = 34.1% of total volume
.341 * 3.56 = $1.21

$2.29 + $1.21 = $3.50
This gives more weight to the store selling the greater volume in the average price calculation.

The formula that I already posted and the one that you gave me, can conduct this math to arrive at a weighted average with the data filtered, but factor in the middle set of data with the .01 cent price, which gives you $2.77.

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))