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

### Like this:

Like Loading...

*Related*

This entry was posted on Wednesday, 15th October, 2008 at 11:29 am and is filed under development, error, Excel, Spreadsheet, technical. You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.

Wednesday, 15th October, 2008 at 12:03 pm |

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

Wednesday, 15th October, 2008 at 12:36 pm |

Why be clear when you can be obsure?

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

Wednesday, 15th October, 2008 at 3:00 pm |

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.

Wednesday, 15th October, 2008 at 3:14 pm |

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.

Wednesday, 15th October, 2008 at 5:59 pm |

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).

Wednesday, 15th October, 2008 at 11:04 pm |

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

Thanks for the link Harlan

Thursday, 16th October, 2008 at 9:24 am |

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 :-)