formatting won't apply to my cell with a formula

[Guest]

I downloaded the checkbook register from Microsoft.com and am trying to add a
few cells at the end to quickly calculate my outstanding debits and credits
so that I can balance my statements automatically. I figured out how to write
the formula properly (eventually), but the cells will not take on the
currency format no matter what I do. I have tried pasting the format from the
other cells within the register, tried clearing the format and reapplying the
currency format, tried accounting, etc.

 

[Bernie Deitrick]

Adrienne,

Try removing and Conditional Formatting that may have been applied.

HTH,
Bernie
MS Excel MVP

 

[Guest]

I searched and it seems there isn't any conditional formatting applied to the
cells. I tried clearing all formatting under the edit menu, and still when I
try to format the cell with currency formatting, it doesn't take it. Even in
the formatting window, the sample doesn't show it with the decimal points of
the currency symbol as it does in the other cells in the other part of the
register that were formatted that way in the template.

Any other suggestions?

 

[Niek Otten]

Isn't your cell actually text, although it may look like a number?
Try =ISNUMBER(A1)
If it is text,:
Format an empty cell as General. Enter the number 1. Edit>Copy. Select your
data. Edit>Paste Special, check Multiply.

 

[David McRitchie]

Hi Adrienne,
Conditional Formatting has nothing to do with number formatting,
don't think Bernie read the entire question -- I think the example might
come with some Conditional Formatting for color banding, but is
devoid of the important number formatting.

If you use Format, cells, custom
what do you see for the format of the active cell.

I don't think the checkbook example has formatting, formulas,
and if that is the case it certainly is not of much use as an example.

You might find the following of interest after you figure out what the
problem is with the formats.
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
It is an example of a checkbook.

You might check your data, first of all if the numbers are left justified
that is a pretty sure indication that they are not numbers.
=ISTEXT(C4) or the opposite =ISNUMBER(C4)

After you change a format between text and number in either direction,
you must reenter it see it this works. Its not a matter of what you say
the format is, it's a matter of what Excel was using when the data was
originally entered before changing a format.
F2 then Enter
http://www.mvps.org/dmcritchie/excel/join.htm#trimall and
read about how to find out what you actually have.

To see formulas and formats assigned to another cell see
GetFormula and GetFormat macros in
http://www.mvps.org/dmcritchie/excel/formula.htm

 

[Guest]

I think you're both right that it is being formatted as text, because it is
left-justified. Although this seems weird, since it is a formula for adding
and subtracting - shouldn't the computer know that's a number?

Let me clarify exactly what I've done and see if you can give me any more
specific pointers:
I downloaded the check register from microsoft's web page. It has the
standard register columns, and I use the one labeled c to mark things that
have cleared at the bank with an x. Then there is a column of debits and one
of credits. So at the bottom, I created a formula to add up the debits not
yet cleared and another to add up the credits not yet cleared so that I can
balance my bank statement (basically a recreation of the form that's on the
back of your bank statement). Here's a copy of the cells I added at the
bottom and the formulas (also note how the numbers are formatted in the last
3 rows, the ones with the formulas):

Statement Ending Balance: $1,645.92
Outstanding Debits: 3982.53
Outstanding Credits: 2476.9
Account Register Balance? 140.29

Formula for outstanding debits:
=IMSUB(SUM(F6:F499),SUMIF(E6:E499,"x",F6:F499))

Formula for outstanding
credits:=IMSUB(SUM(G6:G499),SUMIF(E6:E499,"x",G6:G499))

And the formula for account register balance: =IMSUB(H501+G503,F502)


If I do need to add the =ISNUMBER command, I'm not sure how to do that when
these formulas are already there.

Thanks for working through this with me!
adrienne

 

[Guest]

Oh, and to answer your question. Format, cells, custom shows this:

$#,##0.00 );($#,##0.00)

 

[Guest]

Sorry - I read this again more thoroughly after I realized I didn't have to
use the ISNUMBER -the paste special, multiple works! And I can copy the
formatting from the other cells using the paint function and they remain as
currency. Thanks!

adrienne

 

[Gord Dibben]

Adrienne

Is this the Checkbook Register Template you downloaded?

http://office.microsoft.com/en-us/templates/TC010186511033.aspx?CategoryID=CT011377171033

If so, just select B89:H89 and drag/copy down as far as you wish.

There is no CF on any of the cells.

Just the Currency formatting.


Gord Dibben Excel MVP

 

[David McRitchie]

Hi Adrienne,
I hadn't even noticed Niek's reply, but it would be better to select an
empty cell to copy and then use Special Cells Add so that you
do not create zeros where you had empty cells. Especially if you
are using a Debit column and a Credit column.

But much easier is to have installed the TrimAll macro and it will
remove the spaces and/or Char(160) characters. Test first if dates are
involved, but I have it installed so I can access from a menu and it really
helps when working with data downloaded from the internet.

Of course if you are downloading your data from the Bank, you might
consider using Quicken Deluxe (Deluxe gives you internet access),
or since this is a Microsoft group -- MS Money, and updating will be
a lot easier, no need to do a lot of futzing around in Excel. . For one
bank account it might not make much difference, but if you ever invest
in stocks you will need a package like Quicken to keep records, and
history of everything.
...