I'm working with some really frustrating reporting software that doesn't let me calculate the average by dividing two sums.
To get around this, I need to find a way to get the same result by calculating the average of each value that makes up the sum.
Here's an example:
A B
1 1
2
1
2
2
1 1
1
1
2
1
1 1
15 3
In the above, the sum of column A is 15, and the sum of column B is 3. The result I need is 5 (15/3). But I can't use this method to get this result.
So I'm trying to figure out a calculation for each row that will produce the same result if I either sum or average the total of all these calculations.
For example (obviously the below isn't right but this is the theory):
A B C
1 1 1
2 0
1 0
2 0
2 0
1 1 1
1 0
1 0
2 0
1 0
1 1 1
15 3 0.27
In the above, I've divided B by A for each row and then done the average of these calculations, which is 0.27. I need to take the same approach, with a different calculation, to somehow get 5.
Hope this makes sense (fingers crossed that it is even possible),
Thanks!