 SOLVED

New Contributor

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

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

 A B C D E 1 Asset Amount Purchased Purchase Rate Value Event 2 ABC 25 \$3 \$75 BUY 3 DEF 34 \$5 \$170 BUY 4 ABC 65 \$6 \$390 BUY 5 DEF 74 \$8 \$592 BUY 6 DEF 25 \$10 \$250 SELL

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

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

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 (New Contributor)
Solution

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

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.

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

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"