SOLVED

From a sheet of mixed assets, calculate average cost of asset based on asset type

Copper Contributor

I am trying to simplify a process that currently requires creating sheets for each asset type.

 

Eg. From the following table I want to find the AVG Purchase Rate

 ABCDE
1AssetAmount PurchasedPurchase RateValueEvent
2ABC25

$3

$75

BUY
3DEF34$5$170BUY
4ABC65$6$390BUY
5DEF74$8$592BUY
6DEF25$10$250SELL

 

As I am trying to place the formulas on a Summary sheet and the data on the Transactions sheet my attempt was

 

Where Summary B3 = ABC

=(IF(Transactions!A:A = Summary!$B3,((Transactions!B:B)*(Transactions!C:C))/(SUM(Transactions!B:B)),""))

but I got a spill error

 

I also tried 

=SUMIFS(Transactions!$D$2:$D$400,Transactions!$A$2:$A$400,Summary!$B3,Transactions!$E$2:$E$400,"BUY")/SUMIFS(Transactions!$B$2:$B$400,Transactions!$A$2:$A$400,Summary!$B3,Transactions!$E$2:$E$400,"BUY")

which seems like it might work, but I'm not 100% sure if there is a better way

 

I also tried to combine the variables using IF And

=IF(AND(Transactions!$A$2:$A$400,Summary!$B3,Transactions!$E$2:$E$400,"BUY"), SUM(Transactions!$D$2:$D$400)/SUM(Transactions!$B$2:$B$400))

which totally failed. Is grouping variables like this even possible?

 

Any thoughts or recommendations on how to proceed?

2 Replies
best response confirmed by Fridley (Copper Contributor)
Solution

If I understood this correct:
=UNIQUE(FILTER(A:A,TRUE,"")) will return a list of unique "Asset" and then you can average if off of that:
=AVERAGEIF(A:C,H:H,C:C)
(presuming the above filter is in H) - you will need to copy down to match the spill from the filter, but should do what you're after.

 

example workbook 

Have not tried incorporating "Unique" before, but shall give a it a go.

 

Currently following using the UNIQUE for my 3 sets of unique data (Asset/User/TransactionType) I have created a sumif

 

=SUMIFS(Transactions!F:F,Transactions!E:E,@A:A,Transactions!A:A,$B$2,Transactions!D:D,$C$2)-SUMIFS(Transactions!F:F,Transactions!E:E,@A:A,Transactions!A:A,$B$2,Transactions!D:D,$C$3)-SUMIFS(Transactions!F:F,Transactions!E:E,@A:A,Transactions!A:A,$B$2,Transactions!D:D,$C$4)+SUMIFS(Transactions!F:F,Transactions!E:E,@A:A,Transactions!A:A,$B$2,Transactions!D:D,$C$5)

 

and a averageif 

=AVERAGEIFS(Transactions!I:I,Transactions!E:E,@A:A,Transactions!A:A,$B$2,Transactions!D:D,$C$2)

 

Seems to have done the trick. 

 

P.S. Trans A ="USER"; Trans D = "TransactionType"; Trans E = "Asset"; Trans F = "Amount"; Trans I = "Rate" ; Trans K = "Value"

1 best response

Accepted Solutions
best response confirmed by Fridley (Copper Contributor)
Solution

If I understood this correct:
=UNIQUE(FILTER(A:A,TRUE,"")) will return a list of unique "Asset" and then you can average if off of that:
=AVERAGEIF(A:C,H:H,C:C)
(presuming the above filter is in H) - you will need to copy down to match the spill from the filter, but should do what you're after.

 

example workbook 

View solution in original post