I need help doing a complex sum in a beast mode, as when I do it I either get an error or a totally wrong number.
- The formula I was given reduces to
A/(A+B-C)
, and sums across the entire data set for whatever date range the user chooses. - Each of the letter values will be either a zero or one for a given record based on flags in that record: it's quite possible that an individual record will have no flags raised, making the denominator zero for that record.
When I sum up the values for the numerator and denominator separately and manually calculate the results I get the expected results (9.4%, in this case).
If I set my beast mode to SUM(A/(A+B-C))
I get a divide by zero error, as it calculates the formula per record before summing and one or more of the records will have the denominator calculate to zero.
If I do SUM(A) / SUM(A+B+C)
or SUM(A) / (SUM(A) + SUM(B) - SUM(C))
I get 51%, which is totally wrong.
I'm obviously thinking about this wrong, so any perspective adjustments that make it work would be appreciated.