simple formula not working

Hi All.

I want to add up a column. From what I read I can do it two ways. In cell
D680 which is one cell after the end of the data in column D: I put
=sum(D:D) which is suppose add up the column. I also tried =sum(D5:D679) ,
but it says $0.00 Does a formula for currency need to be written another
way? It does show the $$ in the total in cell D680. I'm confused, this
should be simple?

Thanks for any help..

...Chip..

Hi!

>In cell D680 which is one cell after the end of the data in column D: I
>put =sum(D:D)

Don't do it that way. If you do you'll end up with a circular reference.

Use:

>I also tried =sum(D5:D679)

But your problem is probably that the numbers in that range are really TEXT.

Try this.......

Select any empty cell that has not been preformatted. The default format is
GENERAL.
Goto the menu Edit>Copy
Now, select the range of numbers D5:D679
Goto the menu Edit>Paste Special>Add>OK

That *should" convert TEXT numbers to NUMERIC numbers and your formula
should now work.

Biff

"Charles Shapiro" <[email protected]> wrote in message
news:h5Zrg.16713$_c1.7203@fed1read05...
> Hi All.
>
> I want to add up a column. From what I read I can do it two ways. In
> cell D680 which is one cell after the end of the data in column D: I
> put =sum(D:D) which is suppose add up the column. I also tried
> =sum(D5:D679) , but it says $0.00 Does a formula for currency need to be
> written another way? It does show the $$ in the total in cell D680. I'm
> confused, this should be simple?
>
> Thanks for any help..
>
> ..Chip..
>

Use the formula =sum(D5:D679) assuming that this formula is in a cell below
row 679 if it's in Column D. Not knowing anymore than what you wrote, I
would say your numbers are not numbers. Do this:
In some blank cell type 1. Check that the format of this cell is General.
Copy that cell.
Select all the occupied cells of Column D (but not the formula cell).
Do Edit - Paste Special - check the "Multiply" box.
Click OK.
Your formula should work now. HTH Otto
"Charles Shapiro" <[email protected]> wrote in message
news:h5Zrg.16713$_c1.7203@fed1read05...
> Hi All.
>
> I want to add up a column. From what I read I can do it two ways. In
> cell D680 which is one cell after the end of the data in column D: I
> put =sum(D:D) which is suppose add up the column. I also tried
> =sum(D5:D679) , but it says $0.00 Does a formula for currency need to be
> written another way? It does show the $$ in the total in cell D680. I'm
> confused, this should be simple?
>
> Thanks for any help..
>
> ..Chip..
>

Thanks Biff. I did as you suggested and still the formula reports 0.00.
The cells were formatted currency. I changed the cells to numbers and when
I type the
=SUM(D5:D679) it puts a blue box around all those cells. As soon as I hit
ENTER, the box goes away and it says 0.00.. I guess I was wrong on it being
a simple formula?

The other reply said almost the same thing, except I was told to MULTIPLY
instead of ADD and it made them all 0.

What am I doing wrong? Was it wrong to just highlight the cells D5 to D679
and format them as NUMBERS and then shouldn't the formula work? It still
says ZERO.

Thanks.

...Chip..




"Biff" <[email protected]> wrote in message
news:%[email protected]...
> Hi!
>
>>In cell D680 which is one cell after the end of the data in column D: I
>>put =sum(D:D)
>
> Don't do it that way. If you do you'll end up with a circular reference.
>
> Use:
>
>>I also tried =sum(D5:D679)
>
> But your problem is probably that the numbers in that range are really
> TEXT.
>
> Try this.......
>
> Select any empty cell that has not been preformatted. The default format
> is GENERAL.
> Goto the menu Edit>Copy
> Now, select the range of numbers D5:D679
> Goto the menu Edit>Paste Special>Add>OK
>
> That *should" convert TEXT numbers to NUMERIC numbers and your formula
> should now work.
>
> Biff
>
> "Charles Shapiro" <[email protected]> wrote in message
> news:h5Zrg.16713$_c1.7203@fed1read05...
>> Hi All.
>>
>> I want to add up a column. From what I read I can do it two ways. In
>> cell D680 which is one cell after the end of the data in column D: I
>> put =sum(D:D) which is suppose add up the column. I also tried
>> =sum(D5:D679) , but it says $0.00 Does a formula for currency need to be
>> written another way? It does show the $$ in the total in cell D680. I'm
>> confused, this should be simple?
>>
>> Thanks for any help..
>>
>> ..Chip..
>>
>
>

Hi Charles

Otto said to enter 1 in a blank cell and Paste Special>Multiply, not
enter 0 and multiply.
Since his suggestion "worked" and Biff's didn't, I can only assume that
you made an error when trying Biff's solution.
Both will work, ADDING 0 or MULTPLYING by 1 will have the same effect of
coercing your text values into numeric, then your SUM(D5:D679) should
give you the correct result.

--
Regards

Roger Govier


"Charles Shapiro" <[email protected]> wrote in message
news:At0sg.16729$_c1.16048@fed1read05...
> Thanks Biff. I did as you suggested and still the formula reports
> 0.00. The cells were formatted currency. I changed the cells to
> numbers and when I type the
> =SUM(D5:D679) it puts a blue box around all those cells. As soon as I
> hit ENTER, the box goes away and it says 0.00.. I guess I was wrong
> on it being a simple formula?
>
> The other reply said almost the same thing, except I was told to
> MULTIPLY instead of ADD and it made them all 0.
>
> What am I doing wrong? Was it wrong to just highlight the cells D5 to
> D679 and format them as NUMBERS and then shouldn't the formula work?
> It still says ZERO.
>
> Thanks.
>
> ..Chip..
>
>
>
>
> "Biff" <[email protected]> wrote in message
> news:%[email protected]...
>> Hi!
>>
>>>In cell D680 which is one cell after the end of the data in column
>>>D: I put =sum(D:D)
>>
>> Don't do it that way. If you do you'll end up with a circular
>> reference.
>>
>> Use:
>>
>>>I also tried =sum(D5:D679)
>>
>> But your problem is probably that the numbers in that range are
>> really TEXT.
>>
>> Try this.......
>>
>> Select any empty cell that has not been preformatted. The default
>> format is GENERAL.
>> Goto the menu Edit>Copy
>> Now, select the range of numbers D5:D679
>> Goto the menu Edit>Paste Special>Add>OK
>>
>> That *should" convert TEXT numbers to NUMERIC numbers and your
>> formula should now work.
>>
>> Biff
>>
>> "Charles Shapiro" <[email protected]> wrote in message
>> news:h5Zrg.16713$_c1.7203@fed1read05...
>>> Hi All.
>>>
>>> I want to add up a column. From what I read I can do it two ways.
>>> In cell D680 which is one cell after the end of the data in column
>>> D: I put =sum(D:D) which is suppose add up the column. I also
>>> tried =sum(D5:D679) , but it says $0.00 Does a formula for currency
>>> need to be written another way? It does show the $$ in the total in
>>> cell D680. I'm confused, this should be simple?
>>>
>>> Thanks for any help..
>>>
>>> ..Chip..
>>>
>>
>>
>
>

Like everything pertaining to computers, you must pay attention to *all* the
details.

Those 2 suggestions for 'fixing' the cells that you mentioned, one was to
multiply using a cell containing a *1*, while the other was to add an
*empty* cell ... right ?

Try this test.
Say you enter this formula along side Column D.
In E5, enter:
=ISNUMBER(D5)

What do you get returned in cell E5?
TRUE
or
FALSE

Copy the formula down Column E and see what returns you get for the rest of
the cells.

If they're all FALSE, you *know* that they're *not* numbers.

There's a possibility that the empty cell you picked to try to correct the
situation the first time might not have been formatted to General.

Try again, but this time, pick an unused, empty cell, and YOU format it to
currency, and add the dollar signs and whatever decimals you wish.
Now, enter a 1 in this cell.
Does it look OK, like this $1.00
Just for fun, try the ISNUMBER formula on this cell and see if it comes back
TRUE.

If everything looks good (correct), now go on and fix Column D using this
cell.
Since the $1.00 already entered, use the multiply option.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Charles Shapiro" <[email protected]> wrote in message
news:At0sg.16729$_c1.16048@fed1read05...
> Thanks Biff. I did as you suggested and still the formula reports 0.00.
> The cells were formatted currency. I changed the cells to numbers and
when
> I type the
> =SUM(D5:D679) it puts a blue box around all those cells. As soon as I hit
> ENTER, the box goes away and it says 0.00.. I guess I was wrong on it
being
> a simple formula?
>
> The other reply said almost the same thing, except I was told to MULTIPLY
> instead of ADD and it made them all 0.
>
> What am I doing wrong? Was it wrong to just highlight the cells D5 to
D679
> and format them as NUMBERS and then shouldn't the formula work? It still
> says ZERO.
>
> Thanks.
>
> ..Chip..
>
>
>
>
> "Biff" <[email protected]> wrote in message
> news:%[email protected]...
> > Hi!
> >
> >>In cell D680 which is one cell after the end of the data in column D:
I
> >>put =sum(D:D)
> >
> > Don't do it that way. If you do you'll end up with a circular reference.
> >
> > Use:
> >
> >>I also tried =sum(D5:D679)
> >
> > But your problem is probably that the numbers in that range are really
> > TEXT.
> >
> > Try this.......
> >
> > Select any empty cell that has not been preformatted. The default format
> > is GENERAL.
> > Goto the menu Edit>Copy
> > Now, select the range of numbers D5:D679
> > Goto the menu Edit>Paste Special>Add>OK
> >
> > That *should" convert TEXT numbers to NUMERIC numbers and your formula
> > should now work.
> >
> > Biff
> >
> > "Charles Shapiro" <[email protected]> wrote in message
> > news:h5Zrg.16713$_c1.7203@fed1read05...
> >> Hi All.
> >>
> >> I want to add up a column. From what I read I can do it two ways. In
> >> cell D680 which is one cell after the end of the data in column D: I
> >> put =sum(D:D) which is suppose add up the column. I also tried
> >> =sum(D5:D679) , but it says $0.00 Does a formula for currency need to
be
> >> written another way? It does show the $$ in the total in cell D680.
I'm
> >> confused, this should be simple?
> >>
> >> Thanks for any help..
> >>
> >> ..Chip..
> >>
> >
> >
>
>

This is one of my least favorite things to try to "guide" someone through!

Biff

"Ragdyer" <[email protected]> wrote in message
news:O5nre%[email protected]...
> Like everything pertaining to computers, you must pay attention to *all*
> the
> details.
>
> Those 2 suggestions for 'fixing' the cells that you mentioned, one was to
> multiply using a cell containing a *1*, while the other was to add an
> *empty* cell ... right ?
>
> Try this test.
> Say you enter this formula along side Column D.
> In E5, enter:
> =ISNUMBER(D5)
>
> What do you get returned in cell E5?
> TRUE
> or
> FALSE
>
> Copy the formula down Column E and see what returns you get for the rest
> of
> the cells.
>
> If they're all FALSE, you *know* that they're *not* numbers.
>
> There's a possibility that the empty cell you picked to try to correct the
> situation the first time might not have been formatted to General.
>
> Try again, but this time, pick an unused, empty cell, and YOU format it to
> currency, and add the dollar signs and whatever decimals you wish.
> Now, enter a 1 in this cell.
> Does it look OK, like this $1.00
> Just for fun, try the ISNUMBER formula on this cell and see if it comes
> back
> TRUE.
>
> If everything looks good (correct), now go on and fix Column D using this
> cell.
> Since the $1.00 already entered, use the multiply option.
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Charles Shapiro" <[email protected]> wrote in message
> news:At0sg.16729$_c1.16048@fed1read05...
>> Thanks Biff. I did as you suggested and still the formula reports 0.00.
>> The cells were formatted currency. I changed the cells to numbers and
> when
>> I type the
>> =SUM(D5:D679) it puts a blue box around all those cells. As soon as I
>> hit
>> ENTER, the box goes away and it says 0.00.. I guess I was wrong on it
> being
>> a simple formula?
>>
>> The other reply said almost the same thing, except I was told to MULTIPLY
>> instead of ADD and it made them all 0.
>>
>> What am I doing wrong? Was it wrong to just highlight the cells D5 to
> D679
>> and format them as NUMBERS and then shouldn't the formula work? It still
>> says ZERO.
>>
>> Thanks.
>>
>> ..Chip..
>>
>>
>>
>>
>> "Biff" <[email protected]> wrote in message
>> news:%[email protected]...
>> > Hi!
>> >
>> >>In cell D680 which is one cell after the end of the data in column D:
> I
>> >>put =sum(D:D)
>> >
>> > Don't do it that way. If you do you'll end up with a circular
>> > reference.
>> >
>> > Use:
>> >
>> >>I also tried =sum(D5:D679)
>> >
>> > But your problem is probably that the numbers in that range are really
>> > TEXT.
>> >
>> > Try this.......
>> >
>> > Select any empty cell that has not been preformatted. The default
>> > format
>> > is GENERAL.
>> > Goto the menu Edit>Copy
>> > Now, select the range of numbers D5:D679
>> > Goto the menu Edit>Paste Special>Add>OK
>> >
>> > That *should" convert TEXT numbers to NUMERIC numbers and your formula
>> > should now work.
>> >
>> > Biff
>> >
>> > "Charles Shapiro" <[email protected]> wrote in message
>> > news:h5Zrg.16713$_c1.7203@fed1read05...
>> >> Hi All.
>> >>
>> >> I want to add up a column. From what I read I can do it two ways. In
>> >> cell D680 which is one cell after the end of the data in column D:
>> >> I
>> >> put =sum(D:D) which is suppose add up the column. I also tried
>> >> =sum(D5:D679) , but it says $0.00 Does a formula for currency need to
> be
>> >> written another way? It does show the $$ in the total in cell D680.
> I'm
>> >> confused, this should be simple?
>> >>
>> >> Thanks for any help..
>> >>
>> >> ..Chip..
>> >>
>> >
>> >
>>
>>
>

Thanks to all that posted. I do NOT know what I did, as nothing seems to
work as designed. I had another list of the same (different month) and I
went to it, made sure the COL was formatted NUMBERS, move to 1 cell below
any numbers and entered the formula. It worked. I then went back to the
current month, entered the same formula, was told I had a circular reference
in D38 It was $0.00. When I deleted that cell's entry the formula starting
working....

Hmmm...

Sorry to have been such a pain for something I though (and is probably) so
simple!

...Chip..

Tell me about it ! ! !<bg>

But it was 11:30 PM and the A/C was on the fritz and in So. Cal. the temp
was still 90F at that time, and I couldn't sleep, so ... what the heck!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Biff" <[email protected]> wrote in message
news:[email protected]...
> This is one of my least favorite things to try to "guide" someone through!
>
> Biff
>
> "Ragdyer" <[email protected]> wrote in message
> news:O5nre%[email protected]...
> > Like everything pertaining to computers, you must pay attention to *all*
> > the
> > details.
> >
> > Those 2 suggestions for 'fixing' the cells that you mentioned, one was
to
> > multiply using a cell containing a *1*, while the other was to add an
> > *empty* cell ... right ?
> >
> > Try this test.
> > Say you enter this formula along side Column D.
> > In E5, enter:
> > =ISNUMBER(D5)
> >
> > What do you get returned in cell E5?
> > TRUE
> > or
> > FALSE
> >
> > Copy the formula down Column E and see what returns you get for the rest
> > of
> > the cells.
> >
> > If they're all FALSE, you *know* that they're *not* numbers.
> >
> > There's a possibility that the empty cell you picked to try to correct
the
> > situation the first time might not have been formatted to General.
> >
> > Try again, but this time, pick an unused, empty cell, and YOU format it
to
> > currency, and add the dollar signs and whatever decimals you wish.
> > Now, enter a 1 in this cell.
> > Does it look OK, like this $1.00
> > Just for fun, try the ISNUMBER formula on this cell and see if it comes
> > back
> > TRUE.
> >
> > If everything looks good (correct), now go on and fix Column D using
this
> > cell.
> > Since the $1.00 already entered, use the multiply option.
> > --
> > HTH,
> >
> > RD
> >
>
> --------------------------------------------------------------------------
-
> > Please keep all correspondence within the NewsGroup, so all may benefit
!
>
> --------------------------------------------------------------------------
-
> > "Charles Shapiro" <[email protected]> wrote in message
> > news:At0sg.16729$_c1.16048@fed1read05...
> >> Thanks Biff. I did as you suggested and still the formula reports
0.00.
> >> The cells were formatted currency. I changed the cells to numbers and
> > when
> >> I type the
> >> =SUM(D5:D679) it puts a blue box around all those cells. As soon as I
> >> hit
> >> ENTER, the box goes away and it says 0.00.. I guess I was wrong on it
> > being
> >> a simple formula?
> >>
> >> The other reply said almost the same thing, except I was told to
MULTIPLY
> >> instead of ADD and it made them all 0.
> >>
> >> What am I doing wrong? Was it wrong to just highlight the cells D5 to
> > D679
> >> and format them as NUMBERS and then shouldn't the formula work? It
still
> >> says ZERO.
> >>
> >> Thanks.
> >>
> >> ..Chip..
> >>
> >>
> >>
> >>
> >> "Biff" <[email protected]> wrote in message
> >> news:%[email protected]...
> >> > Hi!
> >> >
> >> >>In cell D680 which is one cell after the end of the data in column
D:
> > I
> >> >>put =sum(D:D)
> >> >
> >> > Don't do it that way. If you do you'll end up with a circular
> >> > reference.
> >> >
> >> > Use:
> >> >
> >> >>I also tried =sum(D5:D679)
> >> >
> >> > But your problem is probably that the numbers in that range are
really
> >> > TEXT.
> >> >
> >> > Try this.......
> >> >
> >> > Select any empty cell that has not been preformatted. The default
> >> > format
> >> > is GENERAL.
> >> > Goto the menu Edit>Copy
> >> > Now, select the range of numbers D5:D679
> >> > Goto the menu Edit>Paste Special>Add>OK
> >> >
> >> > That *should" convert TEXT numbers to NUMERIC numbers and your
formula
> >> > should now work.
> >> >
> >> > Biff
> >> >
> >> > "Charles Shapiro" <[email protected]> wrote in message
> >> > news:h5Zrg.16713$_c1.7203@fed1read05...
> >> >> Hi All.
> >> >>
> >> >> I want to add up a column. From what I read I can do it two ways.
In
> >> >> cell D680 which is one cell after the end of the data in column D:
> >> >> I
> >> >> put =sum(D:D) which is suppose add up the column. I also tried
> >> >> =sum(D5:D679) , but it says $0.00 Does a formula for currency need
to
> > be
> >> >> written another way? It does show the $$ in the total in cell D680.
> > I'm
> >> >> confused, this should be simple?
> >> >>
> >> >> Thanks for any help..
> >> >>
> >> >> ..Chip..
> >> >>
> >> >
> >> >
> >>
> >>
> >
>
>