Every good beancounter knows you should always cross cast to check tables add up the same in both directions.
|^^ sum||^^ sum||^^ sum||?? sum|
The problem comes when you do that bottom right corner. Oh! sum from above or sum from the left? hmm.
Or add another sum below or to the side. Oh and if summing the horizontal and the vertical then you should really compare them and flag an error if they don’t agree. Thats starting to be a big formula.
Instead of SUM what about a function that can add both, compare them, return one if they match and return a text string if not? glad you asked, here is exactly that in glorious XLM – I have thousands of these in a live model on auto calc, that is still responsive. (needs to go in column D, crosscheck in row 1, otherwise that ‘D8’ is going to be looking in the wrong place, define name as function. etc)
CrossCheck =ARGUMENT("Arg1",1) =ARGUMENT("Arg2",1) =ARGUMENT("ErrorMessage",2) =RESULT(1+2) =Arg1-Arg2 =IF(ABS(D8)<0.01) =RETURN(Arg1) =ELSE() =RETURN(ErrorMessage & " : " &D8) =END.IF() =RETURN()
Of course I should have passed in that 0.01 materiality level, or set up a name for it, but I’ll leave that as an exercise for the reader.
Use it thusly:
Any obvious blunders? suggestions?