Find the text cells masquerading as numbers.

From time to time, an import gone awry will leave you with values that look like numbers but are actually text. They won’t total, they won’t compute in formulas and won’t be picked up by a pivot table.

If you have a whole column of them, usually a Data \ Text to columns will take care of the issue, but sometimes that’s not your best tool.

A quick way to find out which cells are not really numbers (and a quick way to find lots of other types of cell) is to use the Goto…Special function. Select the range containing the culprits, press F5 and you have the Go to menu, press the Special button and you will get all the Special Cells option. Select the appropriate one, in this case, Constants \ Text and voilà, the text cells are selected. You can now manually address the issues.

Try it, you might like it.

Thomas

Quick Balance Sheet Account Matching

Hi all,

If your accounting package doesn’t let you apply g/l entries to other g/l entries, or if you just haven’t been doing it, reconciliation of balance sheet can often be an arduous task, with dozens, hundreds or thousands of line to match before you can find out your outstanding items.

The following formulas will help you make a first pass of reconciliation before you dig in further. The goal is to take care of all the one-for-one reversals. To find them, we check the amount column, and for each line, we check if the sum of amounts matching that line’s amount, either with a negative or positive sign, is equal to zero.

First, the two step method, using table references rather than range references. If the [ ] and [@..] confuse you, check out this tutorial on office.microsoft.com.

• Create a column with the absolute value of the line’s amount, using the ABS() function: =ABS([@Amount])
• Create a column with a formula summing the Amount column if the Absolute values match: =SUMIF([ABS],[@ABS],[Amount])=0

Second the one step method, using sumproduct:

• =SUMPRODUCT((ABS([Amount])=ABS([@Amount]))*[Amount])=0

Both of these methods will return a list of TRUE / FALSE values: TRUE if the amount is matched, FALSE if it isn’t. You can filter out the TRUE value and focus your attention on the FALSE. Your work isn’t done yet, but it got started faster. Check out the example file (quick balance sheet matching) to see it in action.

Try it, you might like it,

Thomas

Quick True / False formula

Want a True / False result on your formula (which could be pretty helpful on a quick autofilter analysis)?
The long way?

=IF(MyFormula=0, TRUE, FALSE)

The quick way?

=MyFormula=0

You can use operators directly without an IF statement, and mix and match just like you would in the first part of an IF statement, e.g.
=AND(SUM(A1:A5)>5,SUM(B1:B5)<12)

Try it, you might like it.

Thomas