Need Excel 2007 Formula to Check For Accounting Errors in Long Column

[alcharbonneau]

Hi

The attached is an excerpt from a worksheet that contains up to 6 items in a long column: revenues, expenses, operational profits, additional expenses, additional revenues, and net profits for 2000+ companies. The individual companies do not report all 6 items, that is, for example, some companies do not report additional expenses and/or additional revenues. I have been using the formula revenues-expenses=operational profit; net profit=operational profit+additional revenue-additional expenses. When one of the companies fails to report additional expenses and/or revenues, I get a FALSE statement even though their net profit may be correct. I have corrected these situations manually because I was dealing with less than 250 companies. Now that I am analyzing the industry as a whole, I wonder if it is possible to automate the formula and introduce some conditions so that net profit gets tested TRUE/FALSE even if the company does not report additional revenues and/or expenses.

I have tried for some time now to solve this problem without any success.

Thank you for taking the time to read and consider this request.

Al

[Ace_XL]

Firstly, convert Column "D" to numbers by doing the following
-In a blank cell type 1
-Copy this cell
-Select your range of data (Column D)
-Paste special --multiply


Then for operational profit use

Please Login or Register  to view this content.

For net profit use

Please Login or Register  to view this content.

See attached

[alcharbonneau]

Hi

Thank you for such an excellent solution. I would like to understand it more - can you recommend a newbie level book or do you have the time to explain what the formula is doing.

For me it is like a new language.

Thanks again.

Al

[Ace_XL]

The formula checks for the first instance of the record no. in column A. If the record no. has already occurred in preceding rows it simply returns blank.

Thereafter, the sumproduct forumla simply sums Column F (amounts) based on matching record numbers and the account code specified in the sumproduct formula and checks for op profit = rev - exp and net profit = op profit + add rev - add exp.

Hence is does a separate calculation for each of revenue, expense, operating profit, additional revenue and additional expense for each unique record no.. In case of missing items, the sum is simply zero for that particular instance.

Trust this helps

[alcharbonneau]

Yes it does.

Thank you.

Al