CrossCheck XLM

Every good beancounter knows you should always cross cast to check tables add up the same in both directions.

1 1 1 << sum
1 1 1 << sum
1 1 1 << sum
1 1 1 << sum
^^ 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:

=CrossCheck(Sum(A1:A4),Sum(A5:C5),”arrgghh!”)

Anyuse?

Any obvious blunders? suggestions?

cheers

Simon

Advertisements

7 Responses to “CrossCheck XLM”

  1. Ross Says:

    Can you just do this with if, sum x – sum y = 0, sum y, else error?

  2. Bob Phillips Says:

    Why be clear when you can be obsure?

    Just subtract the sum the horizontal totals from the sum of the vertical totals.

  3. Simon Says:

    Ross
    yes but thats starting to be a big formula, and sum y gets evaluated twice, no problem with a sum but could slow things down with a vlookup. I’ve started wrapping these ifs in XLM because its faster and cleaner on the sheet. the xlm logici is just about what you describe, just wrapped. And this XLM one gives the amount you are out. and its use is clear from its name.

    Bob it needs the total if they match, the error message and amount of error if they don’t.

    The equivaltent wsf is:
    IF(abs(Sum(A1:A4)-Sum(A5:C5))<0.01,Sum(A1:A4),”arrgghh!”&” : “& Sum(A1:A4)-Sum(A5:C5)) roughly.

  4. Bob Phillips Says:

    Simon,

    Surely the point is maintenance, or KISS.

    Aside from the fact that most people are not XLM conversant, so it is obscure as I suggested, what happens when you add your wonderful XLM macro to a mission critical workbook and then one day, MS finally decides XLM is no longer supported, and provide a ribbon-free release that doesn’t have XLM (ie a release that everyone snaps up ).

    Personally, I would use CF to show that the totals do not match and leave it to whoever owns the spreadsheet to track it down, which they have to do anyway.

  5. Harlan Grove Says:

    Have you seen

    http://www.theregister.co.uk/2008/10/15/lehman_buyout_excel_confusion/

    Not strictly related to this, but within the general category of avoiding screw ups.

    In re cross-footing, I used to use a formula like the following

    =IF(VAR(SUM(B10:F10),SUM(G3:G9),SUM(B3:F9))<1e-6,SUM(B3:F9),
    “ERROR – sums of subtotals don’t match”)

    to make sure the sums of the columns and rows of B3:F9 totaled to the sum of B3:F9. Experience taught me it’s not sufficient just to check SUM(B10:F10)=SUM(G3:G9).

  6. Simon Says:

    Bob, does that mean you won’t be donating to the ‘save XLM campaign’? (/irony (for Rob))

    Thanks for the link Harlan

  7. Bob Phillips Says:

    Simon – just because I think MS might do something doesn’t mean that I agree with it. Simplicity aside, sometimes XLM is the right tool for the job, so if there were such a campaign I would donate :-)

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: