reconciliation of numbers

[Dave F]

I have a tricky problem. I download accounting data from a central
database. The various accounting lines roll up to an aggregate. For
some reason, sometimes all the accounting lines that roll up to the
aggregate are not downloaded.

I'm trying to come up with a formula that will test for this: the sum
of the accounting lines should equal the number at the aggregate
level.

Assume then that the aggregate level number is in F31. The following
formula will see if the two sets of numbers equals zero, assuming the
accounting lines are in the range F4:F30:

=SUM(OFFSET($F$31,-(ROWS($F$4:$F$30)),,):OFFSET($F$31,-1,))-$F$31

Here's my question. Sometimes the range of accounting lines is
different (could be longer, or could be shorter).

Is there a way to deal with this dynamic aspect with a formula? Is
this type of problem best handled via a macro?

Thanks,

Dave

 

[Myrna Larson]

You may be making this more complicated than it is. Assuming that when there
are fewer than 27 accounting lines, the missing lines (doesn't matter where
they are) are blank, can't you simply write =SUM($F$4:$F$30,-$F$31)? Or is
there something I'm missing?

Or, if the number of lines is totally variable, and the only constant is that
your aggregate level number is the last one in the list,

=SUM($F$4:$F$100)- LOOKUP(1E300,$F$4:$F$100) * 2

The formula sums from F4 through F100 (assumed it won't go past that), then
subtracts the last value in the column twice. That's equivalent to summing all
values but the last, then subtracting that last value. The result should be 0.

Adjust the maximum row to the largest possible number of rows, and excluding
data below this range, if any.

In the LOOKUP part, it's important that the 1st argument be greater than any
possible number in column F list.

 

[Dave F]

You may be making this more complicated than it is. Assuming that when there
are fewer than 27 accounting lines, the missing lines (doesn't matter where
they are) are blank, can't you simply write =SUM($F$4:$F$30,-$F$31)? Or is
there something I'm missing?

Or, if the number of lines is totally variable, and the only constant is that
your aggregate level number is the last one in the list,

=SUM($F$4:$F$100)- LOOKUP(1E300,$F$4:$F$100) * 2

The formula sums from F4 through F100 (assumed it won't go past that), then
subtracts the last value in the column twice. That's equivalent to summing all
values but the last, then subtracting that last value. The result should be 0.

Adjust the maximum row to the largest possible number of rows, and excluding
data below this range, if any.

In the LOOKUP part, it's important that the 1st argument be greater than any
possible number in column F list.








- Show quoted text -

Well the problem is that the number of lines may be variable. Your
second suggestion is interesting. I will play around with it, thanks.

Dave