prove/audit SUMPRODUCT?
 

I am hoping that I am not the first person to have to do this, but I
can't find anything searching Google groups...

I have some financial models which mock a relational database. The
basic construction is data in tables and summary reports using
SUMPRODUCT referencing dynamic ranges in the tables.

I am now being asked to prove the formulas by generating another file
which calculates all the reports the "old-fashioned way".

Basically no one can audit my files because it is a "black-box"
solution.

Any ideas on how to prove or audit SUMPRODUCT without building a
completely separate, non-dynamic model to test if the result is the
same?

TIA!

What do you mean by "dynamic ranges"? How dynamic? Maybe you could give
us one example of a Sumproduct formula that you would like to
calculate in a different way.

- DH

Here is a formula example from an HR database which calculates the
average Full-Time Equivalents. The row is the dept and the column is
the period.

=SUMPRODUCT((PosDeptNum=$A10)*(G$7<=PosEnd)*(PosSt art<G$8)*(((G$8<PosEnd)*G$8+(PosEnd<=G$8)*PosEnd)-((G$7<PosStart)*PosStart+(PosStart<=G$7)*G$7)-(PosStart<=G$7)*(G$8<PosEnd))/(G$8-G$7-1))

In order to "show my work" I would have to have a row for each
individual and then use Subtotal for the departments, or have a
different tab with the calculations for each individual, and then use a
SUMIF on the departmental tab.

There are new employees being added constantly, and employees
departments can change so the creation of the middle step would be a
manual process, or involve writing code, which I have also been asked
not to do, for auditing purposes (not everyone knows VBA so again they
can't see my work).

thanks!