Forum Discussion
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.https://tavernerresearch-my.sharepoint.com/:x:/g/personal/simonb_taverner_com_au/ES-vINBQrH9OlozfkDZ7bX0BOXIUb8Nohf58JfJhnWOCUQ?e=IBiNmz
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.
https://tavernerresearch-my.sharepoint.com/:x:/g/personal/simonb_taverner_com_au/ES-vINBQrH9OlozfkDZ7bX0BOXIUb8Nohf58JfJhnWOCUQ?e=IBiNmz
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"
