Averages and counts on volumes

[sajeel]

I download a trading order book into excel (Column A to Column K).
I then use if functions and avergae function to generate some statistics on Column M to Column U. The If functions simply go down the columns and read the values and ignore the Volumes of the trades. For instance FESX-AEX (Sell) was traded 8 times instead of 5 as shown in column N. The formulas ignore the Column B which states the volumes traded. I need the cells to take into consideration the volumes traded in order to generate the figures!

[DonkeyOte]

For the count you need to revert to a SUMIF, ie:

N5: =SUMIF(C:C,N$2,B:B)

Re: the Averages - presumably you want to weight the AvgFillPrice based on volume of buy/sell ? In which case the AVERAGEIFS won't be viable.

How do you intend / wish to handle #DIV/0! error ?

Let us know.

[DonkeyOte]

I'm going offline but assuming you want to weight appropriately and also return 0 rather than DIV/0 where required then:

Please Login or Register  to view this content.

copied across to U6 and applied to row 7 also.

change ranges to suit... avoid using entire column references in the SUMPRODUCT.

[sajeel]

The average doesnt seem to work. Column N returns 2 times the average value if the volume in Column B is 2.

[DonkeyOte]

Post a sample with the formulae you're using in place.

[sajeel]

The cells highlighted in yellow are the values to be compared.for fesx-aex the true average is highlighted. The formula gives an incorrect value.

[zbor]

Instead of MAX(1;COUNTIFS($A$1:$A$100;$M5;$C$1:$C$100;N$2)) you must divide with SUMPRODUCT(--(C1:C100=N2);B1:B100)

since counting will count how many times FESX-AEX appear but it must be multiplied with Volume (B column).

So, instead of 1183,1/5=236,62 you must put 1183,1/8=147,8875

[DonkeyOte]

sajeel, completely my fault... the divisor should be a SUMIFS rather than COUNTIFS given you need to SUM the Volume for weighting, so:

Please Login or Register  to view this content.

[zbor]

sajeel, completely my fault... the divisor should be a SUMIFS rather than COUNTIFS given you need to SUM the Volume for weighting, so:

I must quote this.. Can't be seen very often

[sajeel]

Thanks a lot! Much appreciated.