May 31 2022 09:34 PM
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
=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?
May 31 2022 10:00 PM
SolutionIf 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.
May 31 2022 10:46 PM - edited May 31 2022 11:37 PM
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"
May 31 2022 10:00 PM
SolutionIf 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.